Limiting SQL Query results

[Ivo Petkov <iv...@gmail.com>]

Hi all,

I have the following question:

As Derby does not support the SQL Limit
(http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e
) and

if I use java.sql.Statement.setMaxRows(X), does Derby stops scanning the
tables when X rows are found, or it the statement just programatically
limits the returned rows to X.
We have performance issues for a complex select query and we want to limit
the scanning of the database for example - only to the first 10 found rows.

With other words - Does Derby have such an optimization to stop scanning the
database when the first X results are found?

Regards,
Ivo

Re: Limiting SQL Query results

[Stanley Bradbury <St...@gmail.com>]

Ivo Petkov wrote:
> Hi all,
>
> I have the following question:
>
> As Derby does not support the SQL Limit ( 
> http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e 
> <http://mail-archives.apache.org/mod_mbox/db-derby-dev/200411.mbox/%3c41A95632.3010301@debrunners.com%3e>) 
> and
>
> if I use java.sql.Statement.setMaxRows(X), does Derby stops scanning 
> the tables when X rows are found, or it the statement just 
> programatically limits the returned rows to X.
> We have performance issues for a complex select query and we want to 
> limit the scanning of the database for example - only to the first 10 
> found rows.
>
> With other words - Does Derby have such an optimization to stop 
> scanning the database when the first X results are found?
>
> Regards,
> Ivo
>
>
Hi -
This article addresses the issue of browsing large tables with Derby:

http://www.onjava.com/pub/a/onjava/2007/01/31/tuning-derby.html

Based on what I see in the Derby API it seems the answer depends on the 
driver you are using:


      Class EmbedStatement


      setMaxRows

public void *setMaxRows*(int max)
                throws java.sql.SQLException

The maxRows limit is set to limit the number of rows that any ResultSet 
can contain. If the limit is exceeded, the excess rows are silently dropped.


HTH