Skip site navigation (1) Skip section navigation (2)

Re: Does "preparing" a PreparedStatement really help?

From: mljv(at)planwerk6(dot)de
To: pgsql-jdbc(at)postgresql(dot)org
Cc: Paul Tomblin <ptomblin(at)gmail(dot)com>
Subject: Re: Does "preparing" a PreparedStatement really help?
Date: 2008-02-24 12:50:15
Message-ID: 200802241350.15570.mljv@planwerk6.de (view raw or flat)
Thread:
Lists: pgsql-jdbc
Am Samstag 23 Februar 2008 17:25:58 schrieb Paul Tomblin:
> Is there a performance advantage for preparing a PreparedStatement and
> keeping it around and using it thousands of times rather than making a new
> Statement every time?  How big? 

i just experienced this topic the last two weeks so ican share my experience.

Summary: it makes a huge difference!

We have a java web application with hibernate as our ORM tool and c3p0 as 
connection pool and a dedicated postgresql server with dual core amd and 8 GB 
of ram. most of our queries are NOT very complicated to parse. Usually we use 
hibernate generated queries which are most of the time very simple queries 
like "select * from table where id = ?". But hibernate expands the query 
string itself so it become rather huge like "Select table_0.attribute_0, 
table0_attribute1 from table as table0 where table0.attribute0 = ?" and so 
on. So i don't know if parsing attribute lists instead of * needs more 
parsing time or not. We have some more complicated queries but not more than 
3-4 joins and most of the time no difficult subqueries or other stuff. Our 
application is serving about 30 Million PageViews per month and we have some 
peak times where we need maximum performance. 

i tell you all this stuff to let you compare with your environment.

By mistake we stopped using prepared Statements last week by setting 
c3p0.maxStatements = 0. It was the only change in the application which could 
affect the performance. What happend?

- CPU load was doubled from 25% to over 50%
- Incoming Network traffic doubled (because of sending full statements to the 
DB backend instead of statement names)
- load average increased dramatically

We switched back to "prepared statements" and the monitoring graphs went back 
to normal. Maybe it is just all about saving network traffic the cpu has to 
handle, i don't know.

I really never saw something like this. if you read about performance, books 
and documentations always say: "it depends on your environment" and of course 
they are right. but i dont like it, just saying "its faster" or "its faster 
depending on your environment". 

i often have no clue what it means. of course you always have to check your 
bottlenecks yourself but it would be nice if words like "faster" always show 
up with an example benchmark, so you can compare with your environment.

when i first saw "preparing statements" i thought it might have no impact to 
me as parsing stage takes only a few cpu cycles. but i was wrong. so i am 
glad to share my experience with you.

But i guess it does not make a huge difference if you are not struggeling with 
lots of concurrent users. Often the parsing time is only a few millisecond, 
one example:

   select * from member where member_name = 'Jim';
   Time: 65.386 ms (average of 50 executions)

   prepare s1 (text) AS select * from member where member_name = $1;
   execute s1 ('Jim');
   Time: 61.576 ms (average of 50 executions)

( i executed these a few time and the values are averages)

so you save 4 ms on this query. if you don't have concurrent users and the 
whole application response is 400ms, you won't see any difference as 1% 
performance gain is not "visible". But if you have 50 requests per second at 
peak time, you save 200ms/second on your database server. so your CPU load 
might drop by 20%.

but prepared statements has one disadvantage. If you use pg before 8.3 and 
don't drop your pooled connection from time to time, the prepared plan will 
be used forever regardless of new data by "ANALYZE". But this is no longer 
true with 8.3. As i use 8.1 at the moment, i set a maximumLiveTime for each 
pooled connection to be sure a query is replanned from time to time.

if you have any further questions about my szenario, don't hestitate to ask 
me.

kind regards
Janning

In response to

pgsql-jdbc by date

Next:From: Agustin CSDate: 2008-02-25 18:45:41
Subject: Re: Problem executing remote SELECT's (through internet) with JDBC
Previous:From: Agustin CSDate: 2008-02-23 21:58:36
Subject: Re: Problem executing remote SELECT's (through internet) with JDBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group