Prepared statement vs. parameterized

From: "Bucky Jordan" <bjordan(at)lumeta(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Prepared statement vs. parameterized
Date: 2006-10-27 23:59:43
Message-ID: 78ED28FACE63744386D68D8A9D1CF5D4209E11@MAIL.corp.lumeta.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

I've only used the PG JDBC driver for pretty standard things in the
past, and on pretty small db's. However, the current project I'm working
on will be using rather large data sets and likely make use of some more
advanced features. So, I have a few questions related to JDBC
parameterized statements and their relation to prepared statements on
the PG backend.

It appears that parameterized statements are always prepared- is this
correct? If so, why (JDBC spec, performance..)?

As a general rule, we always use parameterized statements (courtesy of
Hibernate). So, if the above is true, then all of our queries are also
prepared. After reading previous discussions on -jdbc and -performance,
it appears that this is very often not so good for performance since the
PG backend basically has to plan a query without values.

Now, on a small database, this wouldn't be much of an issue, especially
as the planner gets better at picking reasonable plans without values
(seems pretty tricky to me..). However, as your data sizes grow (> 500
million records or so) I would think it's critical to have the values
when planning. (I'm starting a project with very large data sizes, so I
will soon be testing some of this..)

So my question is, given the above, would it possibly makes sense to be
able to use parameterized statements without preparing them? Maybe this
could be a user configurable option? (if not, the other request is
really more for the back-end to improve the performance of prepared
statements... which seems quite a bit more complicated).

Thanks,

Bucky

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Julien Patrouix 2006-10-28 11:17:31 Re: jdbc driver performance TODO
Previous Message Bucky Jordan 2006-10-27 23:52:13 Re: jdbc driver performance TODO