select with maxRows attribute

[GoogleCodeExporter]

Evert sql dialect has a different way of getting only the top n rows of a 
result set. 

SELECT TOP 10 column FROM table // SQL Server
SELECT column FROM table LIMIT 10 // MySQL
SELECT column FROM table WHERE ROWNUM <= 10 // Oracle
SELECT column FROM table FETCH FIRST 10 ROWS ONLY // DB2

etc.

JDBC supports a method setMaxRows(int rows) which limits the result set. It 
would be nice to have an attribute in the select tag that does the same thing. 

Original issue reported on code.google.com by raupach....@googlemail.com on 14 Jul 2010 at 4:21

[GoogleCodeExporter]

Enclosed is a patch.

Original comment by raupach....@googlemail.com on 16 Sep 2010 at 5:36

Attachments:

• maxRowsPatch.diff

[GoogleCodeExporter]

This feature would be really useful.

Original comment by joao...@gmail.com on 17 Sep 2010 at 10:58

[GoogleCodeExporter]

This should be already covered funcionally by RowBounds.

Maybe for a performance improvement MyBatis could have a new config property to 
enable using setMaxRows when RowBounds was passed as a parameter. In this case 
setMaxRows should be set to RowBounds#getOffset()+RowBounds#getLimit().

thoughts?

Original comment by eduardo.macarron on 30 Jul 2011 at 4:34

• Added labels: Type-Feature
• Removed labels: Type-Defect

[GoogleCodeExporter]

Guess you are right. RowBounds is fine.

Original comment by raupach....@googlemail.com on 31 Jul 2011 at 5:53

[GoogleCodeExporter]

I am not sure about this change.

I cannot find any information about de difference between setting maxRows and 
stopping reading from RecordSet. Seems that network traffic and database 
resource consumption will be almost the same. Besides, setMaxRows may work 
wrong on poor drivers.

Please add more info to argue the inclusion or setMaxRows.

Original comment by eduardo.macarron on 12 Sep 2011 at 6:33

[GoogleCodeExporter]

Original comment by eduardo.macarron on 8 Nov 2011 at 7:39

• Changed state: WontFix

[GoogleCodeExporter]

I'm not sure if it's the same thing. We have tables with more than 100 million 
records. I know it may sound stupid to paginate these registers, but it's not 
my call. I am afraid that the database performance can degrade without LIMIT. 
We are using MariaDB (mysql fork) and the driver calls set @@SQL_SELECT_LIMIT = 
value for setMaxRows(). According with documentation, this is the same as the 
LIMIT:

"""
SQL_SELECT_LIMIT acts as an automatic LIMIT row_count to any SELECT query.

SET @@SQL_SELECT_LIMIT=1000
SELECT * from big_table;
The above is the same as:

SELECT * from big_table LIMIT 1000;
"""

https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/query-op
timizations/how-to-limittimeout-queries/

Original comment by nat...@gmail.com on 21 Sep 2014 at 2:27