I'm using a number of queries that basically perform a sum for a few columns. The table the queries operate on is fairly large (couple of milion records). A query uses a number of parameters that control whether some rows are taken along in the final result or not.
So far nothing fancy. The odd thing is that if I execute any of these queries by building a string for it and executing it through a regular JDBC statement, then the execution time is about 800ms. However, if I execute the same query using a prepared statement the execution time sky rockets to a whopping 20 seconds!
I'm using PostgreSql 8.1. JDBC is used through Tomcat 5.5.15 and its standard connection pool DBCP. The VM is Sun's 5.0 update 6 JVM. Both Tomcat and PostgreSQL are running on a debian linux box.
Does someone have any clue as to what causes this extreme slowness when using prepared statements?
Just in case, the code I use is:
[ January 24, 2006: Message edited by: Saskia de Jong ]
A few questions to narrow down the problem: 1) Can you post the code you are comparing it to that uses Statement? 2) Are you timing the whole method or just the executeQuery()? If the former, can you time just the executeQuery? 3) What types are the parameters? int, varchar...
It sounds like the database is choosing a different execution plan for the dynamic sql than for the prepared statement. Not familiar with the guts of postgresql, but it sounds like you need to dig into whatever tuning documentation you can find to figure out when it does full table scans versus using indexes, etc.
Generally prepared statements are a better approach; databases tend to scale to heavy volumes more effectively with them. The downside is that sometimes a database can use literal values in a query to come up with a better execution plan. This is particularly likely when one or more of the parameters are from columns that have highly skewed value distributions (like status codes where some codes are much more likely to be used than other codes).
Reid - SCJP2 (April 2002)
Saskia de Jong
Joined: Jan 24, 2006
As if I had nothing else to do today , I spend the entire day testing through a dozens of configurations. I wrote a series of unit tests for the query and tried several things.
First I added 1 test where I used setInteger instead of setObject. On the 8.1 DB this gave interesting results.
For "prepared with setInteger", "prepared with setObject" and "non-prepared" resp, this gave on average the execution times:
1416, 11396, 1257
Here, using setInteger is significantly faster than setObject, but still a bit slower compared with the non-prepared version.
After this I tested with another version of postgres (7.4) and with more complicated queries, but they all gave the same kind of results. setObject unrealistically slow, setInteger fast, but non-prepared the fastest.
Then an interesting test-case came up. I changed the driver from postgresql-8.1-404.jdbc3.jar to pg74jdbc3.jar and all of a sudden each version of the execution method gave the exact same timings. For the same config as the numbers I gave above I was now seeing resp:
1188, 1158, 1124
I re-run my tests with different queries, different DBs (e.g. 7.4 driver on 7.4 DB, 7.4 driver on 8.1 DB) and the execution time of prepared vs non-prepared and setObject vs setInteger is always about equal. For the record, I repeated each test atleast 6 times, and the DBs had no other load on them.
So, in this case there's definitely something wrong with the postgresql-8.1-404.jdbc3.jar driver!
update: I also tried with the 8.0 driver (both 313 and 314), but this gave the same results as the 8.1 driver again. So there's probably nothing inherently wrong with the current 8.1 driver. Maybe the old 7.4 driver simply does not support true prepared statements and always sends the same SQL text to the DB? [ January 25, 2006: Message edited by: Saskia de Jong ]
Repeated PreparedStatement execution should be quicker than repeated Statement execution. If your test show a slower speed, it should mean that your database engine doesn't support PreparedStatement in fact, all PreparedStatement execution were converted into Statement execution.
Saskia de Jong
Joined: Jan 24, 2006
Originally posted by Wei Dai: Repeated PreparedStatement execution should be quicker than repeated Statement execution.
It should, but it's slower...
Like I mentioned, I repeated each test at least a couple of time. Apart from the fact that every query is faster after the initial execute (prepared or not-prepared), it wasn't seeing any differences for repeated execution.
I even enabled the prepared statement cache in my connection pool (DBCP), but this also did not help.
If your test show a slower speed, it should mean that your database engine doesn't support PreparedStatement in fact, all PreparedStatement execution were converted into Statement execution.[/QB]
The PG JDBC 8.1 driver definitely does support prepared statements (wire protocol 3). PG 8.1 itself also supports prepared statements.