Re: Poor performance using ResultSet.TYPE_SCROLL_INSENSITIVE

[Mark Matthews]

How many rows are we talking about here? In general, it's -never- a good idea to select more rows than you need, and use fetch hints to scroll the result set. Pretty much every database now has options like MySQL's LIMIT clause that can (and often will) involve the optimizer to make it so your query examines as few rows as possible.

The MySQL protocol itself doesn't have the notion of fetch batch sizes, and the driver is optimized for OLTP type queries. Leaving large result sets open while a user paginates is very, very sub-optimal because of the locks and other resources that are needlessly taken.

You may want to consider re-thinking your overall strategy, because it's more than likely consuming more resources than you think, even on databases other than MySQL.

-Mark

Mark Matthews
Consulting Member Technical Staff - MySQL Enterprise Tools
Oracle
http://www.mysql.com/products/enterprise/monitor.html