Re: Limit vs setMaxRows issue

Oliver Jowett <oliver(at)opencloud(dot)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.