Skip site navigation (1) Skip section navigation (2)

Re: Limit vs setMaxRows issue

From: Sebastiaan van Erk <sebster(at)sebster(dot)com>
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Limit vs setMaxRows issue
Date: 2006-06-22 08:35:32
Message-ID: 449A5654.5050208@sebster.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: Joost KraaijeveldDate: 2006-06-22 10:59:13
Subject: How to notify JBoss J2EE application in a trigger or rule???
Previous:From: Michael GuyverDate: 2006-06-22 08:16:32
Subject: Re: Binary tx format for an array?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group