aspose file tools*
The moose likes JDBC and the fly likes how long execution plan for  PreparedStatment live on Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how long execution plan for  PreparedStatment live on Database" Watch "how long execution plan for  PreparedStatment live on Database" New topic
Author

how long execution plan for PreparedStatment live on Database

yogesh sood
Ranch Hand

Joined: Aug 31, 2000
Posts: 108
How long execution plan for a Prepared statement remains valid at database. let say i create a prepared statement for an update statment and my code excute that statement 500 times so i understand that during first call the excution plan or some other setup done for executing prep statement will be created at database side and will be valid for subsequent calls. My doubt is how long that execution plan live on Db

is it bound to PreparedStatement or Connection Object, in other words once done with my update if i close prep statement and commit transaction what haapens to that plan which was created on DB for my prep statement, if i create a new prepared statement object with same SQL string in the same connection what will happen now.

Thanks


If its green its biology if its stinkks its chemistry if it has numbers it is Maths and if it doesn't work its TECHNOLOGY
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30352
    
150

Yogesh,
There are two execution plans. There is the one that lives in Java in the database driver. This one is linked to the connection object. In practice people use a connection pool and the prepared statement cache is linked to the connection that lives on in the pool. Therefore it has a longer life.

There is also the execution plan that lives on the database. It can potentially live longer than the connection.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
yogesh sood
Ranch Hand

Joined: Aug 31, 2000
Posts: 108
Thanks Jeann,

It was helpful. I was thinking some what similar. So even if i create new object of PreparedStatement with same connection and Same SQL it will use that already compiled query plan of the connection.

It would be really great if you can point me to some article/resources on internet.


Thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how long execution plan for PreparedStatment live on Database