• 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
  • Ron McLeod
  • Junilu Lacar
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Piet Souris
  • Carey Brown
  • Stephan van Hulst
Bartenders:
  • Frits Walraven
  • fred rosenberger
  • salvin francis

Why we can't share PreparedStatement cache across connections

 
Ranch Hand
Posts: 1873
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,

I was wondering what could be the reason that PreparedStatement cache is for each Connection object individually? Why we can't share cached PreparedStatement across multiple connections?

If we can't share PreparedStatement cache across connections then this is the problem I see,
Consider we have Connection pool and for a Request1 the connection caches the PreparedStatement1 . Now, if there is Request2 and for the same PreparedStatement1 it ends up getting another Connection from the pool then the cache that exist in the other connection for this statement is not useful.

Here if we had the shared of PreparedStatement across connections then we could have used the same cached PreparedStatement for both the requests.

Please give your views/understanding on this.

Regards,
Maulin
[ September 20, 2005: Message edited by: Maulin Vasavada ]
 
Ranch Hand
Posts: 79
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Maulin,

I understand that for 1 PreparedStatement, all the DB resources like
execution plan and execution path and such things are dedicated to only that connection and is not available to other connection.
 
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yep, if the database and driver both support PreparedStatements, it gets cached on the database and not by the Driver.
 
Maulin Vasavada
Ranch Hand
Posts: 1873
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Okay..

I will have to catch some DBA to understand "why so".

Thank you guys for the responses.

Regards
Maulin
 
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Maulin,

I don't think you need a DBA. All there is to know is that caching of Statements and PreparedStatements is handled differently by different DB vendors and JDBC drivers. In short there is no clear-cut answer to this.

Largely though I think you should just be happy with whatever caching exists because that is what the RDBMS and driver handle for you. The creators of that software spend most of their time configuring their applications to provide optimal levels of performance in terms of cacheing (statements and results) vs. memory usage and that sort of thing.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic