Thread: Limit vs setMaxRows issue
Hi, When using the ps.setMaxRows() call on PreparedStatement, the jdbc driver sets the row limit via the "raw" postgres protocol. However, in the words of Tom Lane, "the row limit in the protocol only says how many rows to deliver in the first batch. The presumption is that you'll eventually grab the rest, and so the query is planned on that basis." What this means that when we do the following query: select action_id from actions order by action_id with a ps.setMaxRows(100), it takes about 1.8 seconds for the query to complete. However, if we do the following query: select action_id from actions order by action_id limit 100 without any ps.setMaxRows() the query only takes 0.156 seconds. This is more than a factor of 10 faster. I'm not 100% sure of what JDBC says about setMaxRows (it's kind of ambiguous in the java doc, as usual), but as far as I can tell, if you call setMaxRows on the prepared statement there is no way in to ever retrieve more than that number of rows. If this is indeed the case, it seems to me that currently there is a mismatch between the JDBC api and the postgresql api, and JDBC should somehow tell postgres that this is a hard limit and it should not plan for a second batch. Therefore, my question is: is this a bug? It is not feasable for me to add LIMIT clauses to all the SQL queries in my code, so if this IS a bug, I hope it can be fixed. If it is NOT a bug, is there an alternative workaround that does not involve changing all of my sql statements? Thanks in advance, Sebastiaan
Sebastiaan, I believe the setMaxRows will use a cursor, because you have an order by on the cursor it will have to be fully materialized Try it without the order by Dave On 21-Jun-06, at 5:11 AM, Sebastiaan van Erk wrote: > Hi, > > When using the ps.setMaxRows() call on PreparedStatement, the jdbc > driver sets the row limit via the "raw" postgres protocol. However, > in the words of Tom Lane, "the row limit in the protocol only says > how many rows to deliver in the first batch. The presumption is > that you'll eventually grab the rest, and so the query is planned > on that basis." > > What this means that when we do the following query: > > select action_id from actions order by action_id > > with a ps.setMaxRows(100), it takes about 1.8 seconds for the query > to complete. However, if we do the following query: > > select action_id from actions order by action_id limit 100 > > without any ps.setMaxRows() the query only takes 0.156 seconds. > This is more than a factor of 10 faster. > > I'm not 100% sure of what JDBC says about setMaxRows (it's kind of > ambiguous in the java doc, as usual), but as far as I can tell, if > you call setMaxRows on the prepared statement there is no way in to > ever retrieve more than that number of rows. If this is indeed the > case, it seems to me that currently there is a mismatch between the > JDBC api and the postgresql api, and JDBC should somehow tell > postgres that this is a hard limit and it should not plan for a > second batch. > > Therefore, my question is: is this a bug? It is not feasable for me > to add LIMIT clauses to all the SQL queries in my code, so if this > IS a bug, I hope it can be fixed. If it is NOT a bug, is there an > alternative workaround that does not involve changing all of my sql > statements? > > Thanks in advance, > Sebastiaan > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
Hi, I'm sorry, but I don't really know what you mean with setMaxRows using a cursor. Note that the *same query* (including the order by) is fast when it contains the LIMIT 100 sql appended to it. So this query *also* does an order by. The only difference between the queries is that one uses the setMaxRows() call to limit the number of rows in the result set, and the other uses SQL. Greetings, Sebastiaan Dave Cramer wrote: > Sebastiaan, > > I believe the setMaxRows will use a cursor, because you have an order > by on the cursor it will have to be fully materialized > > Try it without the order by > > Dave > On 21-Jun-06, at 5:11 AM, Sebastiaan van Erk wrote: > >> Hi, >> >> When using the ps.setMaxRows() call on PreparedStatement, the jdbc >> driver sets the row limit via the "raw" postgres protocol. However, >> in the words of Tom Lane, "the row limit in the protocol only says >> how many rows to deliver in the first batch. The presumption is that >> you'll eventually grab the rest, and so the query is planned on that >> basis." >> >> What this means that when we do the following query: >> >> select action_id from actions order by action_id >> >> with a ps.setMaxRows(100), it takes about 1.8 seconds for the query >> to complete. However, if we do the following query: >> >> select action_id from actions order by action_id limit 100 >> >> without any ps.setMaxRows() the query only takes 0.156 seconds. This >> is more than a factor of 10 faster. >> >> I'm not 100% sure of what JDBC says about setMaxRows (it's kind of >> ambiguous in the java doc, as usual), but as far as I can tell, if >> you call setMaxRows on the prepared statement there is no way in to >> ever retrieve more than that number of rows. If this is indeed the >> case, it seems to me that currently there is a mismatch between the >> JDBC api and the postgresql api, and JDBC should somehow tell >> postgres that this is a hard limit and it should not plan for a >> second batch. >> >> Therefore, my question is: is this a bug? It is not feasable for me >> to add LIMIT clauses to all the SQL queries in my code, so if this IS >> a bug, I hope it can be fixed. If it is NOT a bug, is there an >> alternative workaround that does not involve changing all of my sql >> statements? >> >> Thanks in advance, >> Sebastiaan >> >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: Don't 'kill -9' the postmaster >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
On Wed, 21 Jun 2006, Sebastiaan van Erk wrote: > I'm not 100% sure of what JDBC says about setMaxRows (it's kind of ambiguous > in the java doc, as usual), but as far as I can tell, if you call setMaxRows > on the prepared statement there is no way in to ever retrieve more than that > number of rows. If this is indeed the case, it seems to me that currently > there is a mismatch between the JDBC api and the postgresql api, and JDBC > should somehow tell postgres that this is a hard limit and it should not plan > for a second batch. > > Therefore, my question is: is this a bug? It is not feasable for me to add > LIMIT clauses to all the SQL queries in my code, so if this IS a bug, I hope > it can be fixed. If it is NOT a bug, is there an alternative workaround that > does not involve changing all of my sql statements? > I'm not sure how you would like the driver to tell the server that it doesn't want more than setMaxRows rows. The defined API for this is using LIMIT in your sql query. The driver cannot do this for you (at least without parsing your query) because the query may already have a LIMIT or it may be something like an INSERT into a VIEW that has a DO INSTEAD SELECT rule on it. If you're suggesting that we extended the frontend/backend protocol to include this extra information than that's definitely a feature request, not a bug report. Kris Jurka
On Wed, June 21, 2006 12:00 pm, Kris Jurka wrote: > > I'm not sure how you would like the driver to tell the server that it > doesn't want more than setMaxRows rows. The defined API for this is using > LIMIT in your sql query. The driver cannot do this for you (at least > without parsing your query) because the query may already have a LIMIT or > it may be something like an INSERT into a VIEW that has a DO INSTEAD > SELECT rule on it. If you're suggesting that we extended the > frontend/backend protocol to include this extra information than that's > definitely a feature request, not a bug report. The backend protocol already supports maximum row limit if you use the extended protocol. I guess it's a legacy thing in the driver, but there really is no reason to use the simple query protocol at all on recent postgresqls. -M
"A.M." <agentm@themactionfaction.com> writes:
> On Wed, June 21, 2006 12:00 pm, Kris Jurka wrote:
>> If you're suggesting that we extended the
>> frontend/backend protocol to include this extra information than that's
>> definitely a feature request, not a bug report.
> The backend protocol already supports maximum row limit if you use the
> extended protocol.
No, it would take a protocol change to add such a thing out-of-line
(that is, not as a LIMIT clause in the query text). The reason is that
the planning is done at PARSE time, or at the latest BIND time. The
row limit field in the EXECUTE message comes far too late to affect the
query plan. EXECUTE's row limit was not meant as anything except a way
to fetch a query result in segments, avoiding the grab-it-all-at-once,
run-out-of-memory syndrome. It is definitely *not* meant to imply that
the client doesn't intend to fetch the whole query result eventually.
I don't have a lot of sympathy for the OP's position that he shouldn't
have to use a LIMIT clause for this ...
regards, tom lane
Sebastiaan van Erk wrote: > Therefore, my question is: is this a bug? It is not feasable for me to > add LIMIT clauses to all the SQL queries in my code, so if this IS a > bug, I hope it can be fixed. If it is NOT a bug, is there an alternative > workaround that does not involve changing all of my sql statements? It's not a bug. setMaxRows() is essentially a hint, there's certainly no requirement that the driver will go off and add LIMIT clauses to your query, the minimal implementation won't change query execution at all and will just limit rows coming back out of the ResultSet.. It might be nice to add LIMIT but that would require the driver to parse query strings which gets very complicated and isn't going to catch all the cases anyway. You'll be getting at least some improvement with the existing driver because the whole resultset isn't being transferred and processed, even if the plan is still assuming you will grab all the data. If your queries need a LIMIT clause to get decent performance then your safest bet is to add a LIMIT clause yourself. You can keep the setMaxRows() as well if you like.. -O
Hi, Thanks for the helpful replies (thanks also to Oliver Jowett). As it seems to me to be the case that a setMaxRows call actually limits the data you can access to that specific number of rows and it is impossible to ever get more rows, it seems to me to be a waste of time and a loss of performance if the backend does not know this and prepares the result as if everything will (eventually) be returned. I am not suggesting that the driver parse queries and add a LIMIT clause itself. This would make the driver exceedingly complex, it would duplicate logic in the driver that is already in postgres itself and cause an extra maintenance nightmare. Furthermore it would probably introduce many new bugs, cause lots of work, and all for functionality (i.e., limit the resultset to n rows) that *already exists* in postgres itself. So I guess it is indeed a feature request then; that the backend protocol supports limiting the resultset without having to alter the query, and that this limit is indeed a hard limit [i will never ask for more rows] (instead of a soft limit [i might ask for the other rows]). Considering how all the *functionality* at least is already implemented, this should not be too much work, I imagine. The reason I would like to see this feature (instead of adding the LIMIT manually) is for cross database compatibility (which is the essence of JDBC). Basically, setMaxRows is portable, LIMIT is not. Since I am part of a team developing a cross-database application in which performance is often important, this feature is quite important to us. Currently postgres is slow for us on simple index queries on large data sets (1.8 seconds for the first 100 primary keys only of a table of 43000 rows); and unfortunately, these kinds of queries are very common in our application. Regards, Sebastiaan Kris Jurka wrote: > > > On Wed, 21 Jun 2006, Sebastiaan van Erk wrote: > >> I'm not 100% sure of what JDBC says about setMaxRows (it's kind of >> ambiguous in the java doc, as usual), but as far as I can tell, if >> you call setMaxRows on the prepared statement there is no way in to >> ever retrieve more than that number of rows. If this is indeed the >> case, it seems to me that currently there is a mismatch between the >> JDBC api and the postgresql api, and JDBC should somehow tell >> postgres that this is a hard limit and it should not plan for a >> second batch. >> >> Therefore, my question is: is this a bug? It is not feasable for me >> to add LIMIT clauses to all the SQL queries in my code, so if this IS >> a bug, I hope it can be fixed. If it is NOT a bug, is there an >> alternative workaround that does not involve changing all of my sql >> statements? >> > > I'm not sure how you would like the driver to tell the server that it > doesn't want more than setMaxRows rows. The defined API for this is > using LIMIT in your sql query. The driver cannot do this for you (at > least without parsing your query) because the query may already have a > LIMIT or it may be something like an INSERT into a VIEW that has a DO > INSTEAD SELECT rule on it. If you're suggesting that we extended the > frontend/backend protocol to include this extra information than > that's definitely a feature request, not a bug report. > > Kris Jurka
> The reason I would like to see this feature (instead of adding the LIMIT > manually) is for cross database compatibility (which is the essence of > JDBC). Basically, setMaxRows is portable, LIMIT is not. Since I am part > of a team developing a cross-database application in which performance > is often important, this feature is quite important to us. Currently > postgres is slow for us on simple index queries on large data sets (1.8 > seconds for the first 100 primary keys only of a table of 43000 rows); > and unfortunately, these kinds of queries are very common in our > application. JDBC is a little too low-level to give true database independence; you can write portable queries, but you're severely restricted when it comes to functionality supported by most databases but not in a standardized way, such as limits, locking, performance hinting, sequences/serials, etc. For simple, non-performance critical apps you can mostly get away with it (as we did for a while with some of our products). But for anything more sophisticated, your application really needs a way to deal with database-specific SQL. On newer projects we use Hibernate HQL, which has been a major boon in terms of database portability and performance. -- Mark Lewis
What about the setFetchSize method? My impression is that using setFetchSize along with setMaxRows would do the trick on the back end as it resulted in the cursor mechanism that didn't retrieve the complete result set. Is that not the case in PG 8.1 (at least with the few caveats listed at http://jdbc.postgresql.org/documentation/81/query.html)? David > It's not a bug. setMaxRows() is essentially a hint, there's certainly > no requirement that the driver will go off and add LIMIT clauses to > your query, the minimal implementation won't change query execution at > all and will just limit rows coming back out of the ResultSet.. It > might be nice to add LIMIT but that would require the driver to parse > query strings which gets very complicated and isn't going to catch all > the cases anyway. You'll be getting at least some improvement with the > existing driver because the whole resultset isn't being transferred > and processed, even if the plan is still assuming you will grab all > the data. > > If your queries need a LIMIT clause to get decent performance then > your safest bet is to add a LIMIT clause yourself. You can keep the > setMaxRows() as well if you like..
Hi,
We actually do use a part of hibernate (the dialects) with some custom additions, especially for creating tables, indexes, etc. However, since the user of the application creates the data model (and changes it runtime), and since the user is actually working in a tables and columns paradigm, we cannot really use the ORM or HQL parts of hibernate.
Anyway, apart from the joys (i.e. difficulties) in writing portable SQL (oh what fun ;-)), I still think it would be a nice improvement to the JDBC driver is setMaxRows did actually hard limit the number of returned rows, considering it hardly make sense in this case for postgres to prepare to return more rows than specified.
I'm willing to help or even code it if someone points me in the right direction. The JDBC part should be easy; I'm not sure about the postgres side, but I imagine it's not too difficult either. The hard part is probably getting the protocol extended and getting people to agree that it's a good idea to change the protocol, etc... ;-) (although it's a pretty non-intrusive, backwards compatible change).
Regards,
Sebastiaan
Mark Lewis wrote:
We actually do use a part of hibernate (the dialects) with some custom additions, especially for creating tables, indexes, etc. However, since the user of the application creates the data model (and changes it runtime), and since the user is actually working in a tables and columns paradigm, we cannot really use the ORM or HQL parts of hibernate.
Anyway, apart from the joys (i.e. difficulties) in writing portable SQL (oh what fun ;-)), I still think it would be a nice improvement to the JDBC driver is setMaxRows did actually hard limit the number of returned rows, considering it hardly make sense in this case for postgres to prepare to return more rows than specified.
I'm willing to help or even code it if someone points me in the right direction. The JDBC part should be easy; I'm not sure about the postgres side, but I imagine it's not too difficult either. The hard part is probably getting the protocol extended and getting people to agree that it's a good idea to change the protocol, etc... ;-) (although it's a pretty non-intrusive, backwards compatible change).
Regards,
Sebastiaan
Mark Lewis wrote:
The reason I would like to see this feature (instead of adding the LIMIT manually) is for cross database compatibility (which is the essence of JDBC). Basically, setMaxRows is portable, LIMIT is not. Since I am part of a team developing a cross-database application in which performance is often important, this feature is quite important to us. Currently postgres is slow for us on simple index queries on large data sets (1.8 seconds for the first 100 primary keys only of a table of 43000 rows); and unfortunately, these kinds of queries are very common in our application.JDBC is a little too low-level to give true database independence; you can write portable queries, but you're severely restricted when it comes to functionality supported by most databases but not in a standardized way, such as limits, locking, performance hinting, sequences/serials, etc. For simple, non-performance critical apps you can mostly get away with it (as we did for a while with some of our products). But for anything more sophisticated, your application really needs a way to deal with database-specific SQL. On newer projects we use Hibernate HQL, which has been a major boon in terms of database portability and performance. -- Mark Lewis
Tom Lane <tgl@sss.pgh.pa.us> writes: > No, it would take a protocol change to add such a thing out-of-line > (that is, not as a LIMIT clause in the query text). The reason is that > the planning is done at PARSE time, or at the latest BIND time. The > row limit field in the EXECUTE message comes far too late to affect the > query plan. If planning is done at time of creation of the PreparedStatement object (reminder: the example given above has no parameters), then the setMaxRows() call will come too late whatever is the protocol change. I mean: no protocol change can go back in time and "optimize" by not doing useless work already done. Thanks in advance for pointing out my mistake(s) here.
Mark Lewis <mark.lewis@mir3.com> writes: > JDBC is a little too low-level to give true database independence; you > can write portable queries, but you're severely restricted when it comes > to functionality supported by most databases but not in a standardized > way, such as limits, locking, performance hinting, sequences/serials, > etc. > > For simple, non-performance critical apps you can mostly get away with > it (as we did for a while with some of our products). But for anything > more sophisticated, your application really needs a way to deal with > database-specific SQL. Thanks a lot for sharing this very valuable experience from the field. However .setMaxRows() IS currently part of this portable and severely restricted JDBC performance feature set, so I see no reason for not trying to implement it as best as possible. The more performance you can get from JDBC, the less not portable code people will write. And JDBC is evolving too.
Oliver Jowett <oliver@opencloud.com> writes:
> It's not a bug. setMaxRows() is essentially a hint, there's certainly
> no requirement that the driver will go off and add LIMIT clauses to
> your query,
My reading of the JDBC javadoc and my excessive "pickiness" do not
agree with the word "hint"
/**
* Sets the limit for the maximum number of rows that any
* <code>ResultSet</code> object can contain to the given number.
* If the limit is exceeded, the excess
* rows are silently dropped.
OK nothing ever tells you that the server has to behave optimally and
never compute anything useless. However in this case you know _for
sure_ that additional rows will be useless.
Marc Herbert wrote: > Oliver Jowett <oliver@opencloud.com> writes: > > >>It's not a bug. setMaxRows() is essentially a hint, there's certainly >>no requirement that the driver will go off and add LIMIT clauses to >>your query, > > > My reading of the JDBC javadoc and my excessive "pickiness" do not > agree with the word "hint" > > /** > * Sets the limit for the maximum number of rows that any > * <code>ResultSet</code> object can contain to the given number. > * If the limit is exceeded, the excess > * rows are silently dropped. > > > OK nothing ever tells you that the server has to behave optimally and > never compute anything useless. However in this case you know _for > sure_ that additional rows will be useless. I would assume that if the intent of the spec was "put LIMIT on the query", they would say that. As it stands all it does from an API point of view is change the behaviour of the ResultSet. Whether that does anything for efficiency is an implementation decision. The driver certainly behaves correctly according to the API, which was my point. There are other optimizations you can make that don't affect query execution at all -- for example, you know you only need to store the first N rows returned, not all of them. -O
Marc Herbert wrote: > Tom Lane <tgl@sss.pgh.pa.us> writes: > > >>No, it would take a protocol change to add such a thing out-of-line >>(that is, not as a LIMIT clause in the query text). The reason is that >>the planning is done at PARSE time, or at the latest BIND time. The >>row limit field in the EXECUTE message comes far too late to affect the >>query plan. > > > > If planning is done at time of creation of the PreparedStatement > object (reminder: the example given above has no parameters), then the > setMaxRows() call will come too late whatever is the protocol change. > I mean: no protocol change can go back in time and "optimize" by not > doing useless work already done. > > Thanks in advance for pointing out my mistake(s) here. We do not special-case the no-parameters case, so it's handled just like all the other cases: the query is parsed and planned immediately before execution. We also avoid an extra round-trip by doing it at that point. If you're interested in the details of this, the driver source code is really your best reference.. -O
Oliver Jowett <oliver@opencloud.com> writes: > Marc Herbert wrote: >> If planning is done at time of creation of the PreparedStatement >> object (reminder: the example given above has no parameters), then the >> setMaxRows() call will come too late whatever is the protocol change. >> I mean: no protocol change can go back in time and "optimize" by not >> doing useless work already done. >> Thanks in advance for pointing out my mistake(s) here. > > We do not special-case the no-parameters case, so it's handled just > like all the other cases: the query is parsed and planned > immediately before execution. OK, I should not have put this "reminder" above. I thought I would simplify the discussion but it did not. According to: http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html as well as to any other non-DB specific, similar documentation, the server is able to plan the query at parse time BEFORE receiving any actual parameter. Connection.preparedStatement()'s Javadoc calls this "pre-compilation" (and it's optional). > the query is parsed and planned immediately before execution. Hum, interesting. Looks like "lazy prepared" statement, no pre-compilation? If you delay parsing & planning then of course you would not need to go back in time to add late optimizations... However, what about the following executions of the same and now already prepared and planned statement? Now you would have to go back in time to perform any .setMaxRows() optimization, right? I assume here that it's the query planning phase that would benefit from any .setMaxRows() optimization, correct me if I'm wrong. > We also avoid an extra round-trip by doing it at > that point. Then you also avoid any latency benefit that _could_ arise thanks to pre-compilation. At least for the first execution. > If you're interested in the details of this, the driver source code is > really your best reference.. Sure, but for such high-level architectural questions you can easily understand that I prefer to read your enlightning explanations.
>> My reading of the JDBC javadoc and my excessive "pickiness" do not
>> agree with the word "hint"
>> /**
>> * Sets the limit for the maximum number of rows that any
>> * <code>ResultSet</code> object can contain to the given number.
>> * If the limit is exceeded, the excess
>> * rows are silently dropped.
>> OK nothing ever tells you that the server has to behave optimally and
>> never compute anything useless. However in this case you know _for
>> sure_ that additional rows will be useless.
>
> I would assume that if the intent of the spec was "put LIMIT on the
> query", they would say that.
I think they would never say that, because they are quite careful not
to ever try to enforce any kind of implementation/performance detail.
They just care about the interface and the external results, any
timing consideration always excluded.
So the way they seem to do it, is just to open a 8 lanes wide road to
performance in front of you ("if you compute that, it WILL be useless
and dropped at some point. Now compute what you want."). My poor
english skills would not have called this a "hint". I guess this is
just a minor misunderstanding.
Of course I'm assuming here that computing those excess rows has no
side-effect. Hopefully this is always the case?!
> As it stands all it does from an API point of view is change the
> behaviour of the ResultSet. Whether that does anything for efficiency
> is an implementation decision. The driver certainly behaves correctly
> according to the API, which was my point.
Agreed for sure.
Marc Herbert wrote: > Oliver Jowett <oliver@opencloud.com> writes: >> the query is parsed and planned immediately before execution. > > Hum, interesting. Looks like "lazy prepared" statement, no > pre-compilation? If you delay parsing & planning then of course you > would not need to go back in time to add late > optimizations... I don't know what you mean by "lazy prepared" statements. We give the statement to the server for parsing and planning at the point when we know both the query and the parameter types -- which, because of the JDBC API design, means just before execution. We retain the parse/plan results on the server side when it looks like the statement will be reused (using a simple "how many times has this statement already been reused?" metric), otherwise we reparse/replan on each execution. > However, what about the following executions of the same and now > already prepared and planned statement? Now you would have to go back > in time to perform any .setMaxRows() optimization, right? It's no different to the case where you change the parameter types really. -O
Oliver Jowett <oliver@opencloud.com> writes:
> Marc Herbert wrote:
>> Oliver Jowett <oliver@opencloud.com> writes:
>
>>> the query is parsed and planned immediately before execution.
>> Hum, interesting. Looks like "lazy prepared" statement, no
>> pre-compilation? If you delay parsing & planning then of course you
>> would not need to go back in time to add late
>> optimizations...
>
> I don't know what you mean by "lazy prepared" statements. We give the
> statement to the server for parsing and planning at the point when we
> know both the query and the parameter types -- which, because of the
> JDBC API design, means just before execution.
OK thanks, now I think I got it: it seems like the JDBC API does not
assume you need parameter types to plan the query.
Connection.preparedStatement()
* If the driver supports precompilation,
* the method <code>prepareStatement</code> will send
* the statement to the database for precompilation.
No word about parameter types. If you do not need the datatypes, then
you can send/plan the query way earlier (not "lazy"), at
PreparedStatement object construction time.
I should have noticed this issue from here
<http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html>
Looks like we have an API mismatch here.
Is this parameter types issue specific to PostgreSQL, or is just the
JDBC API badly designed on this point?
Another approach could be for the server to infer the types from the
query string?
If not 100% reliably, then in an optimistic way?
> We retain the parse/plan results on the server side when it looks
> like the statement will be reused (using a simple "how many times
> has this statement already been reused?" metric), otherwise we
> reparse/replan on each execution.
Could this be compatible with any setMaxRows() optimization? I guess
yes, provided the maxRows parameter is considered in preparedstatement
matching, just like datatypes seem to be... Maybe this is already the
case for non-JDBC PREPARE + LIMIT?
Thanks again for all these answers.
Marc Herbert wrote: > OK thanks, now I think I got it: it seems like the JDBC API does not > assume you need parameter types to plan the query. The JDBC API doesn't say anything at all about query planning, AFAIK. > Connection.preparedStatement() > > * If the driver supports precompilation, > * the method <code>prepareStatement</code> will send > * the statement to the database for precompilation. We don't support precompilation in the sense it's used here, then. [...] I am a bit confused about what this discussion is actually about .. do you have a point to make here? What is it that you want to do that the current driver doesn't do well? A fair amount of work has gone into getting query execution working smoothly and efficiently within the constraints of the API already.. Vague high-level handwaving, especially without a clear target, doesn't get us anywhere. For example, it's largely irrelevant to an application whether the query gets parsed by the server at statement creation or statement execution .. at worst, it means you see parse errors at a different point. >>We retain the parse/plan results on the server side when it looks >>like the statement will be reused (using a simple "how many times >>has this statement already been reused?" metric), otherwise we >>reparse/replan on each execution. > > Could this be compatible with any setMaxRows() optimization? I guess > yes, provided the maxRows parameter is considered in preparedstatement > matching, just like datatypes seem to be... Maybe this is already the > case for non-JDBC PREPARE + LIMIT? This is all very hypothetical without actual protocol support. I'd suggest you start from a proposed protocol design and work back from there. The limiting factor is likely to be what you can easily support on the server side, not the driver implementation. -O
Oliver Jowett <oliver@opencloud.com> writes:
> Marc Herbert wrote:
>
>> OK thanks, now I think I got it: it seems like the JDBC API does not
>> assume you need parameter types to plan the query.
>
> The JDBC API doesn't say anything at all about query planning, AFAIK.
>
>> Connection.preparedStatement()
>> * If the driver supports precompilation,
>> * the method <code>prepareStatement</code> will send
>> * the statement to the database for precompilation.
>
> We don't support precompilation in the sense it's used here, then.
Again here, I think the word "compilation" is purposely fuzzy to be
portable across DBMS. The message is:
if the driver supports <pre-compilation|pre-parsing|pre-planning|pre-whatever>
then
the PreparedStatement will be _sent to the database_ at
object creation time then not sent again but referred to.
By the way that's precisely the reason why the SQL request string of
PreparedStatement is documented as not mutable, to allow this
optimization.
PreparedStatement are quite obviously meant to match the SQL "PREPARE"
statement or the ODBC SQLPrepare() function
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcprepared_execution.asp>
The name re-use is not just coincidence, don't you think?
> [...]
>
> I am a bit confused about what this discussion is actually about .. do
> you have a point to make here? What is it that you want to do that the
> current driver doesn't do well? A fair amount of work has gone into
> getting query execution working smoothly and efficiently within the
> constraints of the API already.. Vague high-level handwaving,
> especially without a clear target, doesn't get us anywhere.
OK, this discussion has a bit slipped to a general description of the
implementation of PreparedStatement, but the main questions stayed
explicit, simple and not so far away:
1. is it possible to get the same level of performance achieved by
LIMIT using .setMaxRows() (question still in Subject:)
2. when is the SQL query actually sent to the database and
"prepared"? This question came because it seems any .setMaxRows()
coming AFTER this point will come too late to help the server,
whatever the protocol changes.
Then I digressed to other PreparedStatement performance questions. You
did not had to answer; thanks a lot for doing it.
> For example, it's largely irrelevant to an application whether the
> query gets parsed by the server at statement creation or statement
> execution .. at worst, it means you see parse errors at a different
> point.
It's functionally irrelevant, but this is a discussion about
performance. From what I got, calling .setMaxRows() before vs after
query parsing greatly changes the range of possible
optimizations. This discussion is just about the ordering of events
(= "hand-waving"?). I'm not even discussing about any protocol changes
at this point.
Even if you delay the parsing on the first execution in order to wait
for any helpful but late .setMaxRows(), then later .setMaxRows() on
the next re-executions will come after parsing anyway. Unless you
kinda re-parse every time...
I found very interesting to discuss about possible strategies to
handle this issue, but you do not have to be interested. If no one
else is interested then I'll stop noising the list.
> The limiting factor is likely to be what you can easily support
> on the server side, not the driver implementation.
I can understand that. Looks like we already saw that with the
datatypes issue.
Hi, Marc, Marc Herbert wrote: > Even if you delay the parsing on the first execution in order to wait > for any helpful but late .setMaxRows(), then later .setMaxRows() on > the next re-executions will come after parsing anyway. Unless you > kinda re-parse every time... Only when setMaxRows() actually changes. And theoretically, only re-planning is required, not re-parsing, but currently the server does not allow clients to control those steps independently. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Oliver Jowett <oliver@opencloud.com> writes: > I am a bit confused about what this discussion is actually about .. do > you have a point to make here? What is it that you want to do that the > current driver doesn't do well? A fair amount of work has gone into > getting query execution working smoothly and efficiently within the > constraints of the API already.. Vague high-level handwaving, > especially without a clear target, doesn't get us anywhere. For > example, it's largely irrelevant to an application whether the query > gets parsed by the server at statement creation or statement execution > .. at worst, it means you see parse errors at a different point. - By the way: <http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html#getMetaData()> Retrieves a ResultSetMetaData object that contains information about the columns of the ResultSet object that will be returned when this PreparedStatement object is executed. Because a PreparedStatement object is precompiled, it is possible to know about the ResultSet object that it will return without having to execute it. Consequently, it is possible to invoke the method getMetaData on a PreparedStatement object rather than waiting to execute it and then invoking the ResultSet.getMetaData method on the ResultSet object that is returned. Similar thing for #getParameterMetadata() - However, in the 3rd edition of the JDBC book: 23.1.3 Using parameter metadata wisely The purpose of a prepared statement is to allow the data source to "prepare" an SQL statement, that is, to compile an SQL statement before it is executed. [..] However, not all data sources prepare a statement the same way, and some do not prepare them at all. If a data source does not precompile SQL statements, the ParameterMetaData methods will not be able to return results until execution [this is a lie because it misses "lazy" implementations, see below]. According to my (limited) understanding of the code, pgjdbc sends a special "describe" request to the server for #getMetadata() in case the statement has not been executed yet, and systematically for #getParameterMetadata(). Neither one seems to cache results, at least not on the driver-side. As you said above, all this looks very well-crafted to be functionally irrelevant to the application. It definitely looks relevant and good to know concerning performance.