Support for SQL Limit?

[Brian Abbott <br...@dommoni.com>]

As far as I can tell, Derby does not currently support the SQL Limit command. Am I missing something? If it doesnt, are there any plans to implement it?

Cheers,

Brian Abbott

Re: Support for SQL Limit?

[Brian Abbott <br...@dommoni.com>]

Unfortunately I'm not intemately familiar with the internal workings of
Derby but, I would think if you had the appropriate meta-data in the
internal table structures, this would be a very efficient place to execute
this. But, I suppose order by/ResultSet counting would work as well.

Thank you for the info Jeremy!

Brian Abbott

----- Original Message ----- 
From: "Jeremy Boynes" <jb...@apache.org>
To: "Derby Development" <de...@db.apache.org>
Sent: Saturday, November 27, 2004 12:31 PM
Subject: Re: Support for SQL Limit?


> Brian Abbott wrote:
> > As far as I can tell, Derby does not currently support the SQL Limit
> > command. Am I missing something? If it doesnt, are there any plans to
> > implement it?
> >
>
> LIMIT is really a MySQL feature not an SQL command. Different databases
> implement this in different ways as it was not standardized until SQL03
> (with window functions that I don't think anyone implements yet and
> which are really there for OLAP processing).
>
> One problem with it is that SQL does not define the order in which rows
> are returned; even order by will not define the order for rows with the
> same sort key. This means that if you execute the same query twice you
> may get the rows returned in different orders - this is common on any
> database that uses a parallel query algorithm. As a result the data
> returned by LIMIT/OFFSET may well be non-deterministic.
>
> Subject to these constraints, you can mimic this on the client side by
> controlling how you iterate over the ResultSet - e.g. skip offset rows,
> process limit rows and then close it. This actually gives you a solution
> that is portable between database vendors.
>
> The downside is that the offset rows need to be evaluated and returned
> to the client only to be discarded. However, unless offset is very large
> (which would imply that perhaps you should look for a better query
> strategy anyway) this is probably not where the majority of your
> processing effort is going. Further, if you are using Derby embedded
> then you are simply duplicating what the server would need to do to
> implement offset anyway so the difference is going to be minimal.
>
> However, if you feel that LIMIT/OFFSET is critical or useful
> functionality you might want to open a Jira item to add it as an
> enhancement.
>
> --
> Jeremy

Re: Support for SQL Limit?

[Jeremy Boynes <jb...@apache.org>]

Brian Abbott wrote:
> As far as I can tell, Derby does not currently support the SQL Limit 
> command. Am I missing something? If it doesnt, are there any plans to 
> implement it?
>  

LIMIT is really a MySQL feature not an SQL command. Different databases 
implement this in different ways as it was not standardized until SQL03 
(with window functions that I don't think anyone implements yet and 
which are really there for OLAP processing).

One problem with it is that SQL does not define the order in which rows 
are returned; even order by will not define the order for rows with the 
same sort key. This means that if you execute the same query twice you 
may get the rows returned in different orders - this is common on any 
database that uses a parallel query algorithm. As a result the data 
returned by LIMIT/OFFSET may well be non-deterministic.

Subject to these constraints, you can mimic this on the client side by 
controlling how you iterate over the ResultSet - e.g. skip offset rows, 
process limit rows and then close it. This actually gives you a solution 
that is portable between database vendors.

The downside is that the offset rows need to be evaluated and returned 
to the client only to be discarded. However, unless offset is very large 
(which would imply that perhaps you should look for a better query 
strategy anyway) this is probably not where the majority of your 
processing effort is going. Further, if you are using Derby embedded 
then you are simply duplicating what the server would need to do to 
implement offset anyway so the difference is going to be minimal.

However, if you feel that LIMIT/OFFSET is critical or useful 
functionality you might want to open a Jira item to add it as an 
enhancement.

--
Jeremy

Re: Support for SQL Limit?

[Jan Hlavatý <hl...@code.cz>]

Dain Sundstrom wrote:
> Most people are looking for limit and offset, so they can implement
> result paging.  Without support for limit and offset most people use
> setMaxRows in the statement for limit and something like relative(int)
> in the result set for the offset.

> The problem I see, is the planner
> does not know how much the user intends to skip until after the query is
> processed, but maybe this can't be optimized anyway.

Yes, that kind of paging needs to count all the records from the beginning
to the end of page, which is on average 50% of all the records in the database.
That does not work well with higher isolation levels, especially without
read-only transactions and multiversion architecture...

What _can_ be optimized however is a different paging method, in which you
don't use record numbers (which make sense only in context of whole resultset
you're trying to page and will shift as records are added/deleted),
but use ordering index key value to position your page in the result set.
You select first N records that have ordering key >= page starting value,
in the order of the ordering index, to get the page.
This type of paging only provides navigation to next/previous/first/last page, but
that is a good thing when the underlying result set is changing a lot anyway, and is
even good for the concurrency (by locking just the page, not whole resultset
or all skipped records from beginning).
Query planner should recognize I'm trying to retrieve a limited number of records
in specific index order starting with specific value, and return the correct records
by planning record retrieval based on the ordering index, not on how the records
happen to be stored (which would be some random records that fulfill the >= predicate
as SapDB does, which is totally useless).

Jan

Re: Support for SQL Limit?

[Dain Sundstrom <ds...@gluecode.com>]

Most people are looking for limit and offset, so they can implement 
result paging.  Without support for limit and offset most people use 
setMaxRows in the statement for limit and something like relative(int) 
in the result set for the offset.   The problem I see, is the planner 
does not know how much the user intends to skip until after the query 
is processed, but maybe this can't be optimized anyway.

-dain


On Nov 28, 2004, at 8:03 AM, Jan Hlavatý wrote:

> Daniel John Debrunner wrote:
>> Derby supports limiting the number of rows returned by a query through
>> the standard JDBC java.sql.Statement.setMaxRows() method.
>
> And does it affect the execution plan? Knowing the maximum number of 
> records
> would be extremely useful for query optimizer, which could select a 
> much faster
> plan using indexes which otherwise has higher "per record" cost over 
> mass methods
> like full table scan...
>
> This was (is?) one of the major shortcomings in SapDB - it always
> realized query for first few records starting with specific value in 
> an (existing) index order
> by full table scan and sort, thinking the index scan would cost much 
> more.
>
> Jan
>

Re: Support for SQL Limit?

[Jan Hlavatý <hl...@code.cz>]

Daniel John Debrunner wrote:
> Derby supports limiting the number of rows returned by a query through
> the standard JDBC java.sql.Statement.setMaxRows() method.

And does it affect the execution plan? Knowing the maximum number of records
would be extremely useful for query optimizer, which could select a much faster
plan using indexes which otherwise has higher "per record" cost over mass methods
like full table scan...

This was (is?) one of the major shortcomings in SapDB - it always
realized query for first few records starting with specific value in an (existing) index order
by full table scan and sort, thinking the index scan would cost much more.

Jan

Re: Support for SQL Limit?

[Daniel John Debrunner <dj...@debrunners.com>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Brian Abbott wrote:
> As far as I can tell, Derby does not currently support the SQL Limit
> command. Am I missing something? If it doesnt, are there any plans to
> implement it?
>

Derby supports limiting the number of rows returned by a query through
the standard JDBC java.sql.Statement.setMaxRows() method.

Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBqVYOIv0S4qsbfuQRAiMsAKCgY/IdgMmBhMHVtrG40a9zVVnd4QCgnsBC
3ZLKFhckBzEC8dvZ8ZE97AA=
=E96Z
-----END PGP SIGNATURE-----