Thread: Re: [GENERAL] Prepared statement performance...
I've been dying to have usable PreparedStatements in my web application, but
AFAIK this is still not possible given your description below. Assuming I
can't dedicate a connection to each user (connections are pooled), even
Server Side PreparedStatements are useless (since they fall out of context
when the statement is destroyed, and I can't hold it without holding a
client side PreparedStatement), right?
Imagine an online catalog with millions of records, and we want to support
searching and browsing. Parsing the initial search query will be quite
expensive if there are a great many columns on the table, but each
subsequent NEXT_PAGE click really just needs to re run the same query with
new params passed to the limit and offset clauses. Virtually any website
with a database backend will have some variation on this scenario, and
caching the execution plans for these common queries could significantly
improve performance in all these cases.
I agree that server side prepared statements are useful even when we don't
need to bind variables, but unless we have single user systems, neither
definition gets us very far. Maybe if we could cache prepared statements in
application code and dynamically bind them back to connections on each
request we could make use of this functionality, but I can't see how
recreating the prepared statement will ever pay off with connection pools in
place.
Mike
Dimtry,
Server side prepare does not map to jdbc concept of
PreparedStatement and it is important to understand how they are not the
same.
Server side prepare means that you can parse and plan the statement
once and reexecute it multiple times so:
select foo from bar;
becomes
prepare <name> as select foo from bar;
execute <name>;
deallocate <name>;
This applies to all sql statements. So server side prepared
statements can equally be used for regular JDBC Statement objects as well as
JDBC PreparedStatements.
JDBC PreparedStatements provide an interface to bind values into a
sql statement.
Server side prepare provides the ability to reduce the overhead of
parse/plan across muliple executions of a sql statement that may or may not
have bind values.
They are different even though they both have the word 'prepare' in
their names.
thanks,
--Barry
You may be able to configure your app server database pools to
cache PreparedStatements. Some version of JBoss and WebLogic support
this, at any rate. The idea is that just like connections aren't really
closed when you call close (just returned to the pool), PSs aren't really
closed when you call close (just kept in a cache for the connection).
This would let you take advantage of server side PSs in an app server
environment.
The danger is that if each connection has a high PS cache size,
you can run into problems like "too many open cursors" on Oracle (when 50
connections each try to cache 50 PSs or whatever). I'm not sure whether
PostgreSQL would complain or not. Does it support multiple open
PreparedStatements per Connection? And if so, are there any backend
limits to the total number of open server side PSs?
Aaron
On Mon, 30 Sep 2002, Simpson, Mike W wrote:
> I've been dying to have usable PreparedStatements in my web application, but
> AFAIK this is still not possible given your description below. Assuming I
> can't dedicate a connection to each user (connections are pooled), even
> Server Side PreparedStatements are useless (since they fall out of context
> when the statement is destroyed, and I can't hold it without holding a
> client side PreparedStatement), right?
>
> Imagine an online catalog with millions of records, and we want to support
> searching and browsing. Parsing the initial search query will be quite
> expensive if there are a great many columns on the table, but each
> subsequent NEXT_PAGE click really just needs to re run the same query with
> new params passed to the limit and offset clauses. Virtually any website
> with a database backend will have some variation on this scenario, and
> caching the execution plans for these common queries could significantly
> improve performance in all these cases.
>
> I agree that server side prepared statements are useful even when we don't
> need to bind variables, but unless we have single user systems, neither
> definition gets us very far. Maybe if we could cache prepared statements in
> application code and dynamically bind them back to connections on each
> request we could make use of this functionality, but I can't see how
> recreating the prepared statement will ever pay off with connection pools in
> place.
>
> Mike
>
>
> Dimtry,
>
> Server side prepare does not map to jdbc concept of
> PreparedStatement and it is important to understand how they are not the
> same.
>
> Server side prepare means that you can parse and plan the statement
> once and reexecute it multiple times so:
> select foo from bar;
> becomes
> prepare <name> as select foo from bar;
> execute <name>;
> deallocate <name>;
>
> This applies to all sql statements. So server side prepared
> statements can equally be used for regular JDBC Statement objects as well as
> JDBC PreparedStatements.
>
> JDBC PreparedStatements provide an interface to bind values into a
> sql statement.
>
> Server side prepare provides the ability to reduce the overhead of
> parse/plan across muliple executions of a sql statement that may or may not
> have bind values.
>
> They are different even though they both have the word 'prepare' in
> their names.
>
> thanks,
> --Barry
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Aaron Mulder wrote: > You may be able to configure your app server database pools to > cache PreparedStatements. Some version of JBoss and WebLogic support > this, at any rate. The idea is that just like connections aren't really > closed when you call close (just returned to the pool), PSs aren't really > closed when you call close (just kept in a cache for the connection). > This would let you take advantage of server side PSs in an app server > environment. > The danger is that if each connection has a high PS cache size, > you can run into problems like "too many open cursors" on Oracle (when 50 > connections each try to cache 50 PSs or whatever). Oracle's max open cursors is per connection. So as long as it is set higher than the size of the statement cache you should be ok. I also beleive that in recent versions of the oracle jdbc driver, the driver does this statement caching automatically. It shouldn't be too difficult to add statement caching to the postgres jdbc driver if we thought it would be a good idea. > I'm not sure whether > PostgreSQL would complain or not. Does it support multiple open > PreparedStatements per Connection? And if so, are there any backend > limits to the total number of open server side PSs? > Yes it does support mulitple server side prepared statements. There isn't any limit on the backend (other than available memory) on the number. > Aaron > --Barry
I'm getting a compilation error when trying to build the latest pgsql
sources.
Here's ant's error log:
examples:
[javac] Compiling 8 source files to
/Volumes/data/Users/drew/sources/TAOWO/tools/pgsql/src/interfaces/jdbc/
build
[javac]
/Volumes/data/Users/drew/sources/TAOWO/tools/pgsql/src/interfaces/jdbc/
org/postgresql/jdbc1/AbstractJdbc1Connection.java:269:
encode(java.lang.String,java.lang.String,java.lang.String) in
org.postgresql.util.MD5Digest cannot be applied to
(java.lang.String,java.lang.String,byte[])
[javac]
byte[] digest = MD5Digest.encode(PG_USER, password, md5Salt);
[javac]
^
[javac] Note: Some input files use or override a deprecated API.
[javac] Note: Recompile with -deprecation for details.
[javac] 1 error
Sorry, I couldn't figure out the problem from looking at the two files,
org/postgresql/jdbc1/AbstractJdbc1Connection.java and
org/postgresql/util/MD5Digest.java.
FYI - I'm building on Mac OS X 10.2, with the following configuration.
./configure
--prefix=/Volumes/data/Users/drew/sources/TAOWO/buildresults/postgres
--bindir=/Volumes/data/Users/drew/sources/TAOWO/buildresults/bin/
--enable-recode --with-java --without-readline --enable-syslog
--enable-unicode-conversion --enable-multibyte --enable-cassert
--enable-debug.
Thanks for any help,
Drew
On Friday, October 11, 2002, at 09:42 AM, Barry Lind wrote:
>
>
> Aaron Mulder wrote:
>> You may be able to configure your app server database pools to
>> cache PreparedStatements. Some version of JBoss and WebLogic support
>> this, at any rate. The idea is that just like connections aren't
>> really
>> closed when you call close (just returned to the pool), PSs aren't
>> really
>> closed when you call close (just kept in a cache for the connection).
>> This would let you take advantage of server side PSs in an app >> server
>> environment.
>> The danger is that if each connection has a high PS cache size, you
>> can run into problems like "too many open cursors" on Oracle (when 50
>> connections each try to cache 50 PSs or whatever).
>
> Oracle's max open cursors is per connection. So as long as it is set
> higher than the size of the statement cache you should be ok. I also
> beleive that in recent versions of the oracle jdbc driver, the driver
> does this statement caching automatically. It shouldn't be too
> difficult to add statement caching to the postgres jdbc driver if we
> thought it would be a good idea.
>
>> I'm not sure whether PostgreSQL would complain or not. Does it
>> support multiple open PreparedStatements per Connection? And if so,
>> are there any backend limits to the total number of open server side
>> PSs?
>
> Yes it does support mulitple server side prepared statements. There
> isn't any limit on the backend (other than available memory) on the
> number.
>
>
>> Aaron
>
> --Barry
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Drew, Any luck getting it to compile? Here's a couple of things: 1) The precompiled jar will work just fine, even on a mac 2) If you are re-compiling, ie this isn't the first time, try make clean in the jdbc directory 3) Try getting the latest sources off of cvs Dave On Fri, 2002-10-11 at 13:02, Drew Wilson wrote: > I'm getting a compilation error when trying to build the latest pgsql > sources. > > Here's ant's error log: > examples: > [javac] Compiling 8 source files to > /Volumes/data/Users/drew/sources/TAOWO/tools/pgsql/src/interfaces/jdbc/ > build > [javac] > /Volumes/data/Users/drew/sources/TAOWO/tools/pgsql/src/interfaces/jdbc/ > org/postgresql/jdbc1/AbstractJdbc1Connection.java:269: > encode(java.lang.String,java.lang.String,java.lang.String) in > org.postgresql.util.MD5Digest cannot be applied to > (java.lang.String,java.lang.String,byte[]) > [javac] > byte[] digest = MD5Digest.encode(PG_USER, password, md5Salt); > [javac] > ^ > [javac] Note: Some input files use or override a deprecated API. > [javac] Note: Recompile with -deprecation for details. > [javac] 1 error > > Sorry, I couldn't figure out the problem from looking at the two files, > org/postgresql/jdbc1/AbstractJdbc1Connection.java and > org/postgresql/util/MD5Digest.java. > > FYI - I'm building on Mac OS X 10.2, with the following configuration. > ./configure > --prefix=/Volumes/data/Users/drew/sources/TAOWO/buildresults/postgres > --bindir=/Volumes/data/Users/drew/sources/TAOWO/buildresults/bin/ > --enable-recode --with-java --without-readline --enable-syslog > --enable-unicode-conversion --enable-multibyte --enable-cassert > --enable-debug. > > Thanks for any help, > > Drew > > On Friday, October 11, 2002, at 09:42 AM, Barry Lind wrote: > > > > > > > Aaron Mulder wrote: > >> You may be able to configure your app server database pools to > >> cache PreparedStatements. Some version of JBoss and WebLogic support > >> this, at any rate. The idea is that just like connections aren't > >> really > >> closed when you call close (just returned to the pool), PSs aren't > >> really > >> closed when you call close (just kept in a cache for the connection). > >> This would let you take advantage of server side PSs in an app >> server > >> environment. > >> The danger is that if each connection has a high PS cache size, you > >> can run into problems like "too many open cursors" on Oracle (when 50 > >> connections each try to cache 50 PSs or whatever). > > > > Oracle's max open cursors is per connection. So as long as it is set > > higher than the size of the statement cache you should be ok. I also > > beleive that in recent versions of the oracle jdbc driver, the driver > > does this statement caching automatically. It shouldn't be too > > difficult to add statement caching to the postgres jdbc driver if we > > thought it would be a good idea. > > > >> I'm not sure whether PostgreSQL would complain or not. Does it > >> support multiple open PreparedStatements per Connection? And if so, > >> are there any backend limits to the total number of open server side > >> PSs? > > > > Yes it does support mulitple server side prepared statements. There > > isn't any limit on the backend (other than available memory) on the > > number. > > > > > >> Aaron > > > > --Barry > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > >
Drew: I just did a clean build on a fresh CVS, MacOSX v 10.2.1. My configuration is ./configure --bindir=/usr/local/bin --mandir=/usr/local/share/man/ --enable-recode --with-java --enable-syslog --enable-unicode-conversion --enable-multibyte --with-perl --with-tcl --without-tk If you are still having problems feel free to contact me directly and maybe we can debug it. Do you have any java extensions installed, for example? -mc -- Mike Coleman <mc@fivebats.com> Five Bats Research, Portland Oregon