[dbcp] poolPreparedStatements parameter

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[dbcp] poolPreparedStatements parameter

Stefano Nichele-2
Hi All,
I would like to ask why poolPreparedStatements is false by default. It
seems that setting it to true could improve performances (I haven't
tried it, but it seems useful)
Is there any drawback or known issues ?

thanks a lot
ste

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [dbcp] poolPreparedStatements parameter

Anurag Kapur
Just guessing,
Probably because PreparedStatements are not used in many applications and
thus by having the pooling option turned off by default it saves the
overhead involved in maintaining the pools.

Can a expert validate by guess and if it is not correct provide a sane
explanation? :)

Cheers
Anurag

------------------------------------------------------------------
Anurag Kapur
Associate - Technology,
Sapient Corporation India.

http://www.linkedin.com/in/anuragkapur
------------------------------------------------------------------


On Wed, Feb 25, 2009 at 9:49 PM, Stefano Nichele
<[hidden email]>wrote:

> Hi All,
> I would like to ask why poolPreparedStatements is false by default. It
> seems that setting it to true could improve performances (I haven't tried
> it, but it seems useful)
> Is there any drawback or known issues ?
>
> thanks a lot
> ste
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>
Reply | Threaded
Open this post in threaded view
|

RE: [dbcp] poolPreparedStatements parameter

wesclark
Another guess is that modern database do a pretty good job of caching
queries at the server, so why not let the database server do the caching
for you?

-----Original Message-----
From: Anurag Kapur [mailto:[hidden email]]
Sent: Wednesday, February 25, 2009 3:43 PM
To: Commons Users List
Subject: Re: [dbcp] poolPreparedStatements parameter

Just guessing,
Probably because PreparedStatements are not used in many applications
and thus by having the pooling option turned off by default it saves the
overhead involved in maintaining the pools.

Can a expert validate by guess and if it is not correct provide a sane
explanation? :)

Cheers
Anurag

------------------------------------------------------------------
Anurag Kapur
Associate - Technology,
Sapient Corporation India.

http://www.linkedin.com/in/anuragkapur
------------------------------------------------------------------


On Wed, Feb 25, 2009 at 9:49 PM, Stefano Nichele
<[hidden email]>wrote:

> Hi All,
> I would like to ask why poolPreparedStatements is false by default. It

> seems that setting it to true could improve performances (I haven't
> tried it, but it seems useful) Is there any drawback or known issues ?
>
> thanks a lot
> ste
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [dbcp] poolPreparedStatements parameter

Gamnacke
In reply to this post by Stefano Nichele-2
Well, I am trying it out now per request from our DBA:s. If you use pooling of prepared statements you will essentially only have to compile the SQL once. However, the pool is per connection and not per JVM. So if you have short-lived connections you won't benefit as much from it.

I am not aware of any performance costs that are due to pooling the prepared statements over time. I assume it must be better to pool the statements than to constantly recreate them.

There is one thing that strikes me though, and I've asked this in a previous post (DBCP: Why is the validation query not a prepared statement). The validation query is not executed as a prepared statement for some reason.
Reply | Threaded
Open this post in threaded view
|

RE: [dbcp] poolPreparedStatements parameter

Gamnacke
In reply to this post by wesclark
I can tell you that a modern database (Oracle 10) does not seem to do a good job of caching the query. According to snapshots taken on our DB we get a one-to-one correspondence between query execution and query parsing if we turn off pooling. With pooling turned on, the query needs only be parsed once.
Reply | Threaded
Open this post in threaded view
|

Re: [dbcp] poolPreparedStatements parameter

Stefano Nichele-2
Thanks for you answer.  This is what I think (talking also about your
other email). What I don't understand is why it is false by default.
Maybe memory usage ? I don't know....I would like to turn on it on
production but I'm a bit doubtful. Is there any trab ?
Bugs ? Just for completeness I'm using postgres.

Curious the fact that the validation query is not a prepared
statement....but it should be performed rarely.

ste

Gamnacke wrote:
> I can tell you that a modern database (Oracle 10) does not seem to do a good
> job of caching the query. According to snapshots taken on our DB we get a
> one-to-one correspondence between query execution and query parsing if we
> turn off pooling. With pooling turned on, the query needs only be parsed
> once.
>
>  



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [dbcp] poolPreparedStatements parameter

Gamnacke
We are running it in production now and there have not been any issues so far. We have yet to evaluate the result and tune the number of connections (since the pools are per connection). I try to get back to this list when I know more.