Обсуждение: prepared statement performance

Does anyone have some handy metrics of the performance advantage of
prepared vs unprepared statements?  I'm considering the case of an
application which has to make multiple queries/inserts (30-100) per
application transaction and which is currenlty generating the sql by
building the strings on the fly then passing them to the JDBC.  Can I
expect significant (ie worth the recoding time) performance increases by
converting the statements into prepared statements?

Thanks,
Sheer

Perpared statements in postgres jdbc are converted on the java side and
then shipped to the backed, so there will no performance increase.

Sheer El-Showk wrote:
>
> Does anyone have some handy metrics of the performance advantage of
> prepared vs unprepared statements?  I'm considering the case of an
> application which has to make multiple queries/inserts (30-100) per
> application transaction and which is currenlty generating the sql by
> building the strings on the fly then passing them to the JDBC.  Can I
> expect significant (ie worth the recoding time) performance increases by
> converting the statements into prepared statements?
>

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

Sheer,

If your code only runs against PostgreSQL, then the answer is that it
wouldn't be worth your time.  The FE/BE protocol that the postgres JDBC
driver uses doesn't support binding variables, so the JDBC driver ends
up doing exactly what you are currently doing in your code, and builds
up a single string from all the values and sends the result to the
server.  If your code also runs against other databases (for example
Oracle) then you could expect to see performance improvements as other
databases may (Oracle does) cache statements and rebind/reexecute.

thanks,
--Barry

Sheer El-Showk wrote:

> Does anyone have some handy metrics of the performance advantage of
> prepared vs unprepared statements?  I'm considering the case of an
> application which has to make multiple queries/inserts (30-100) per
> application transaction and which is currenlty generating the sql by
> building the strings on the fly then passing them to the JDBC.  Can I
> expect significant (ie worth the recoding time) performance increases by
> converting the statements into prepared statements?
>
> Thanks,
> Sheer
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>