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

PreparedStatement Pre-compiled Once?

 
Kang Wang
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys,

If I prepare the same sql statement more than once, is it precompiled by the DBMS once only or more than once?

For example,

for(int i = 0; i < 10; i++){
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM T1 WHERE C1 = ?");
//execute query
}

Does the database precompile the select statement in each loop? or does it check if it already exists and igonre the other 9 loops?

Thanks very much
Kang
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Kang Wang:

is it precompiled by the DBMS once only or more than once?


It is compiled only once provided database has support for it.


Does the database precompile the select statement in each loop? or does it check if it already exists and igonre the other 9 loops?

Database checks for complied statement in shared pool if a statement is there then it wont compile else database will compile it once and keep it in shared pool onwards call will be ignored for comilation





in this case query would be compiled once but will be executed 10 times.
Use PreparedStatement.addBatch() to execute all sql in one go


Shailesh
 
Kang Wang
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks soooooooooo much.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic