From: | Anish Kejariwal <anishkej(at)yahoo(dot)com> |
---|---|
To: | "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | avoid prepared statements on complex queries? |
Date: | 2011-11-16 01:18:01 |
Message-ID: | 1321406281.74175.YahooMailNeo@web33903.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
I'm running into an issue where a complex query is performing significantly slower if I use a prepared statement.
I'm using:
-postgres 9.0.3
-postgresql-9.0-801.jdbc4.jar JDBC driver
Some notes about the query:
-I'm calling a postgres function, via: select * from foo(?,?,?,?,?,?,?)
-I'm getting back 4 million results
-the underlying query is quite complex joining several tables, and the core table is partitioned over a couple hundred tables.
Here's the java code:
conn.setAutoCommit(false);
pstmt = conn.prepareStatement("select * from foo(?,?,?,?,?,?,?)");
pstmt.setFetchSize(1000);
//and, then I do the usual thing to set parameters in the prepared statement
stmt.setInt(1, SOME_NUMBER);
pstmt.setArray(2, genepoolConn.createArrayOf("integer", ARRAY_OF_INTEGERS));
//and so on
What I have found:
-if ARRAY_OF_INTEGERS has a length of 3, the query performs fast
-if ARRAY_OF_INTEGERS has a length of 150, then query takes 1200 seconds
-if ARRAY_OF_INTEGERS has a length of 150, and I don't use a prepared statement, the query takes the expected 30 seconds
So, obviously when using a prepared statement, postgres is coming up with the wrong execution plan when the the parameter list is unexpectedly large. Pretty understandable.
My question: is there a work around to this? Can I force it not set the execution plan until I bind the variables?
The only reasons I'm using a prepared statement:
-parameterized queries are far easier to work with than building my query via string concatenation
-minimize chances of SQL Injection
If only there was a way to have parameterized queries without using prepared statements....
Thanks!
From | Date | Subject | |
---|---|---|---|
Next Message | Maciek Sakrejda | 2011-11-16 01:31:41 | Re: avoid prepared statements on complex queries? |
Previous Message | Teun Hoogendoorn | 2011-11-15 08:59:20 | BUG #6293: JDBC driver performance |