StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POsubsequent call to sql stored procedure very slow
primarykey
Id
18843741
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
7
CommunityOwnedDate
CreationDate
2013-09-17T07:25:16.033
FavoriteCount
0
LastActivityDate
2013-09-17T14:06:01.327
LastEditDate
2013-09-17T14:06:01.327
LastEditorUserId
1635563
OwnerUserId
1635563
ParentId
0
PostTypeId
1
Score
1
ViewCount
409
LastEditorDisplayName
text
Body
When I call my stored procedure the first time, it executes in a few seconds. When i call it again, the exact same way, after say a minute or 2, it takes around 3-5 minutes! The stored procedure just does a run-of-the-mill Update with some where clauses, nothing extraordinary. I have seen other questions here, where stored procedures have run slow the first time and faster the subsequent times, so my situation really puzzles me! I ran the application in hibernate debug mode, and this is where it seems to take a long time, the second time around : You can see there's a 3 minute gap between the first and the second debug statement <pre><code>........ //binding of other named parameters 17 Sep 2013 17:06:38,287 | DEBUG | org.hibernate.engine.query.NativeSQLQueryPlan [139] | bindNamedParameters() ABC111 -> productName [1] 17 Sep 2013 17:09:39,051 | DEBUG | org.hibernate.jdbc.AbstractBatcher [418] | about to close PreparedStatement (open PreparedStatements: 1, globally: 1) </code></pre> Here's how i call my stored procedure: <pre><code>StringBuilder query = new StringBuilder(); query.append("{call ").append(Constants.StoreProcedures.UPDATE_ORDER_STATUS) .append("(:productName, :prodNumber, ") //other parameters .append(")} "); final String queryStr = query.toString(); Object obj = super.getJpaTemplate().execute(new JpaCallback() { public Object doInJpa(EntityManager em) throws PersistenceException { Query query = em.createNativeQuery(queryStr); query.setParameter("productName", prodBean.getProductName()); query.setParameter("prodNumber", prodBean.getProdNumber()); //other parameters return query.executeUpdate(); } }); </code></pre> EDIT: On sniffing around further, i noticed that between the two calls to the stored proc in question, lets calls it SP1, another stored Proc (SP2) is called in a loop. And i face this problem only if the number of loops is high - over 400. And if i run SP1 after say 5 minutes or so, it seems to run fine - doesn't take so long. (Haven't figured out the actual threshold time gap though, after which the problem doesn't occur.)
Tags
<sql><oracle><hibernate><stored-procedures><plsql>
Title
subsequent call to sql stored procedure very slow
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USinsanity
UserOwnerUserId
1. USinsanity
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.