Re: Prepared Statements - Mailing list pgsql-jdbc

First of all what postgres version are you using?

It is important, because in 7.2 (and before) PreparedStatements *are*
indeed a "big lie". They are actually *slower* then the simple
Statement, because they parse your sql for questionmarks when you create
it, and replace the question marks with the actual parameters when you
run it - thus the overhead compared to a plain Statement, that just
sends your sql to the backend as is. There is no performance benefit in
using them before 7.3 - every statement is re-parsed, and re-planned by
the server every time you execute it anyway.

In 7.3, I believe, there is a way to cache the query plans in the
backend, and PreparedStatements can use that feature, but, that is *off*
by default AFAIK - you have to turn it on explictly (and I am not sure
how) otherwise the behaviour is the same as in 7.2.

Theoretically, the benefit of PS caching the query plans can be really
*huge*.
I am running an app, that is reading about 80 million entries from a
text file, and for each entry it looks up the corresponding row in the
database to check if it needs to be updated...
It was slow like hell, and, after loading the backend into the debugger,
I found out that about 40% (!) of the total time was spent on parsing
and planning the query I was using to lookup the row in the table (it
was literally taking longer to parse and plan it, then to actually
execute and return the result). I am running on 7.2, so,
PreparedStatements are not an option for me - I had to write a stored
procedure in "C", that caches tne query plan in the backend, runs it,
concatenates the results into a pipe separated text string (you can't
have functions return rows in 7.2 either), and to modify my java app to
call that udr, and parse the returned text back into columns instead of
sending the plain sql query and getting the ResultSet...

That alone gave me about 40% performance improvement...

Dima

Julien Le Goff wrote:

>Hello everyone,
>
>I have a question regarding the efficiency of Prepared Statements. I'm
>working on a project, and my task now is to decide whether it's worth
>it to use PS. This problem came up when, beginning to implement jdbc
>classes, we noticed that we would need a lot of PS - something like 40
>per class. Each PS would be a class variable, and it sounds weird to
>have 40 class variables... We could have a more elegant system using
>normal statements, but would it be much less efficient?
>
>I started doing some very simple tests: inserting 1000 elements to a
>table, doing 1.000.000 simple queries, then 1.000.000 queries with a
>join... But suprisingly, Prepared Statements didn't give better results
>than normal statements. Before warning the world that prepared
>statements are a big lie, I wanted to have your opinion. Has anyone
>done a reliable test showing the difference between PS and normal
>statements? Does anyone know "how" better PS are supposed to be?
>
>Then, concerning my test, what the hell could be wrong in what I did?
>The query is the following:
>
>String theJoinQueryPrepared =
>"SELECT tr.text FROM truc tr, test te " +
>"WHERE tr.id = te.id AND te.id = ?";
>
>for a Prepared Statement, and
>
>String theJoinQuery = "SELECT tr.text FROM truc tr, test te  " +
> WHERE tr.id = te.id AND te.id = ";
>
>for a Statement.
>
>Then I just do:
>
>    for(int j = 0; j < 1000; j++)
>    {
>     for(int i = 0; i < 1000; i++)
>     {
>        thePS.setInt(1, i);
>        ResultSet theResultSet = thePS.executeQuery();
>
>     }
>    }
>
>and
>
>    for(int j = 0; j < 1000; j++)
>    {
>        for(int i = 0; i < 1000; i++)
>        {
>            ResultSet theResultSet =
>                theStatement.executeQuery(
>                        theJoinQueryPrepared + i);
>        }
>    }
>
>I realize that this test is ridiculously simple, but shouldn't the first
>loop be more efficient? On my server both are equally fast...
>
>Ok, I hope this message wasn't too long / too stupid. Thanks in advance,
>
>Julien
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>