MySQL Forums
Forum List  »  Connector/JDBC and Java

Re: Poor performance using ResultSet.TYPE_SCROLL_INSENSITIVE
Posted by:
Date: September 23, 2011 10:07AM

Hi Mark, thank you for your quick response.

I'm having problems with only 2000 rows, but the problem is not in the execution of the query (from SQL Editor query executes in 0.094 sec, is on row fetching where there is time consumption). On other databases using this method i've successfully queried more than 4.000.000 rows in practically 0 (Used in production systems with thousands users without performance problems).

I know is better to get only the rows i need. Here my problem is not in MySQL or postgres, those resolution for pagination is the best i've seen (adding limit start, pagesize (or pagesize, start ... :-) at the end of the clause do what i want the best way). My problem is on all other databases that have no common methods to paginate results:

- Oracle using rownums and subqueries
- SQL Server using ROW_NUMBER() OVER and subqueries
- DB2 also using ROW_NUMBER() OVER and subqueries

The problem is that is difficult to handle sql queries in a generic form adding all this stuff...

I think i will have to write specific code for MySQL...

Anyway, it would be nice if MySQL driver didn't despise fetchsize:

void setFetchSize(int rows) throws SQLException

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed. The number of rows specified affects only result sets created using this statement. If the value specified is zero, then the hint is ignored. The default value is zero.

Thanks for your help,

Options: ReplyQuote

Written By
Re: Poor performance using ResultSet.TYPE_SCROLL_INSENSITIVE
September 23, 2011 10:07AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.