• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Performance with preparedStatement.

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ]
 
Thank you my well lotioned goddess! Here, have my favorite tiny ad!
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic