Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How long should a callable statement take to be prepared?

 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys, i've noticed that a certain stored procedure runs very fast when invoked (about 70msecs),while preparing the corrisponding callable statement takes a longer time to be prepared (about 300 msec). Sounds strange, so i'd like to have some hints about:is that prepare time normal or is very slow with respect to your own experience? And if it's slower, where should i have a look to enhance performance?
 
Dave Tolls
Ranch Hand
Posts: 2102
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I suspect there's some traffic with the db to check the existence and parameters of the procedure being called?
What database are you using?
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your answer,

target dbms is IBM Db2 for i5.
 
Dave Tolls
Ranch Hand
Posts: 2102
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Never used DB2, so I'm not sure what it might be doing (or what a Stored Procedure looks like in it).
Someone else might have, though, and be able to give a more definitive statement.
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks anyway for your help and time.
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It shouldn't matter what DB you are using. Using callable statement to call a store procedure is the same.

If you worry about performance, run the execution plan for the queries/ store procedure to check where it is slow.
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tsang,

as far as I remember during prepare phase a statement is translated by the driver in a dbms specific / native code. Isn't that true ?

Looking at query / execution plan may be a good idea indeed.

Moreover, I opened a PMR with IBM support for further investigation...

Thank you for your reply.
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Claude Moore wrote:
as far as I remember during prepare phase a statement is translated by the driver in a dbms specific / native code. Isn't that true ?


I doubt that's true.
 
Dave Tolls
Ranch Hand
Posts: 2102
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
K. Tsang wrote:
Claude Moore wrote:
as far as I remember during prepare phase a statement is translated by the driver in a dbms specific / native code. Isn't that true ?


I doubt that's true.


Depends on the driver, but a PreparedStatement can be sent to the db for precompilation. Whether there are any drivers that do that I have no idea.
CallableStatements can be sent as well, as I mention above, but again that's implementation dependent, which is why I was saying they might need someone who has used this version of DB2 to get a more definitive answer. If it's taking 300ms then, frankly, it's got to be talking to the db during preparation.

Actually, there's a thought. Claude, could you break the call? That is stick a random character on the end of the procedure name in your call string just so it would fail? If it fails in the prepare then it is talking to the db. If it fails in the execute, then those 300ms are quite possibly not to do with the db. More out of curiosity than anything else...
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave,

if I run the prepareCall statement with a SQL string with a syntax error, I get an error from DBMS. As far as I remember I'm pretty sure that db2 for i precompiles statements and callable statements in "native" language; moreover one can specify a sql package where these prepared statements are cached to reduce prepare time.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic