wood burning stoves 2.0*
The moose likes JDBC and the fly likes Performance with preparedStatement. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Performance with preparedStatement." Watch "Performance with preparedStatement." New topic
Author

Performance with preparedStatement.

Tarun Dewan
Greenhorn

Joined: Sep 05, 2002
Posts: 21
Hi All,

I have one doubt about usage of preparedStatement. when preparedStatement is used then SQL Statement is cached in the memory.
My doubt is whether it cached at COnnection level or at database level. i.e. if i execute an SQL statement with preparedStatement object using one connection (at this time SQL statement is cached in memory) and then close the connection object.
If i open a new connection object and execute the same SQL statement then earlier cached statement will be used or if this statement is cached again.
I personally believes that caching is done at connection level.
Will appeciate any detail information for the same.
Thanks & regards,
Tarun Dewan.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Hi Tarun,
Please check out our naming policy and change your display name to include at least two names. Thanks.
Originally posted by tarun:
My doubt is whether it cached at COnnection level or at database level. [...] I personally believes that caching is done at connection level.
This is, at least in principle, implementation-dependent. The database I'm most familiar with (Oracle) implements statement caching at the database level; completely independently from the Java driver, even.
- Peter
Tarun Dewan
Greenhorn

Joined: Sep 05, 2002
Posts: 21
Hi Peter,
As per your message caching is implementation dependent.
This means whether i use statement object or preparedStatement object, this will not make any difference.
Pls. clarify.
Thanks & regards,
Tarun Dewan. :roll:
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
It makes all the difference! Even in the case of Oracle, where the cache is in fact in the database, you will generally need to use PreparedStatement for it to work properly. The Oracle statement cache only works if your statement text is exactly the same. Now considerThis won't use the query cache because the two statements are different. Compare this withThe second query will re-use the execution plan of the first one because the statement text SELECT * FROM FOO WHERE BAR=? is exactly the same.
- Peter
[ May 20, 2003: Message edited by: Peter den Haan ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performance with preparedStatement.