Re: Prepared Statements

Prepared statements are more than just for performance. You also use it for
when you have parametrized data so you don't have to construct query strings for
every value you may want to use the query with.

Some performance gain should come from using server prepared statements from the
time you save from parsing and optimizing the query. Of course, for a query
that takes a long time to execute this is not very significant.

In any case, try using server prepared statements by setting your statement to
use that option (you must be using a backend at least of version 7.3):

setUseServerPrepare(true);

Let us know if you had any improvement in the time measurements.

Regards,
Fernando

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
>

--
Fernando Nasser
Red Hat - Toronto E-Mail: fnasser(at)redhat(dot)com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9