Hi, everybody.
I am running into huge performance problems, due to JDBC not being able to
cache query plans :-(
My java program runs the (set of about 5) identical statements with different
parameters for about 30 million times...
What I am talking about below has to do with JDBC, but is not really specific
to it - it seems to me, that the backend itself
could be improved to better handle this kind of scenario, when the same
statement is executed many times in the same session.
It takes about 3 weeks(!) to run, and it looks like about *half* of that time
is spent by the query planner,
creating and recreating the query plans every time I ran the damn statement....
I am looking into implementing some kind of a solution, that would let me work
around that problem...
So far, I only see two possibilities:
- a general solution, that would involve extending postgres SQL gramma to
include a 'prepare' statement
- or am ugly work around, that would involve moving all my sql statements into
stored procedures, and have
those cache the query plans inside...
The second solution is not only ugly (because it requires the application code
to be changed and to have a specialized
stored procedure for every query), but also requires some additional hacks (to
overcome the hard limit on the number of
function arguments and the inability for functions to return tuples) - the only
way I imagine this can be made to work is
to glue all the arguments together into a text string, and have the stored
procedure parse it back, execute the query, then
glue the resulting tuple(s) into another text string, return it, and have the
application (or, perhaps, JDBC layer) parse it back
into columns...
I was wonderring if anybody has any better ideas how this can be made to work
(I am looking for a solution that would
minimize changes to the existing JDBC applications that use PreparedStatements)?
If the maintainers of the involved code are interested, I would be willing to
implement and contribute the solution we come up with ...
(I figure, nobody would really be interested in getting that second solution I
mentioned into the mainstream :-), but, if we are
able to come up with something more general and less ugly, perhaps, I am not
the only one who would be able to contribute from
it)...
For example, I believe, it should not be too complicated to implement that
first possibility I described above...
The way I see it would involve adding two statements to postgres SQL syntax:
prepare <name> as <sql statement>
and
execute <name> with (<parameter list>)
For example:
prepare mystatement as select * from mytable where id = $1 and name like $2;
and then
execute mystatement with (1, 'Dima');
execute mystatement with (2, 'John');
etc....
The JDBC driver would then send the 'prepare' command to the backend in
Connection.prepareStatement (), and
use the 'execute' in PreparedStatement.execute ();
One potential problem with implementation I see here is that the query planner
wants to know the argument types ahead of time...
I guess, I could get around that by making all the arguments 'text', and having
them casted into the right types when the
statement is actually executed.
There is, probably a need to also have some kind of a 'close' command to throw
away the prepared query plans... or we could just
make them last forever until, say, the end of transaction (or, perhaps, the end
of the session?)...
If there is anyone interested in discussing various possibilities, and getting
this implemented one way or another,
I would like to hear from you!
Thanks!
Dima.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
|
