Re: Limit vs setMaxRows issue

Sebastiaan van Erk wrote:

> Therefore, my question is: is this a bug? It is not feasable for me to
> add LIMIT clauses to all the SQL queries in my code, so if this IS a
> bug, I hope it can be fixed. If it is NOT a bug, is there an alternative
> workaround that does not involve changing all of my sql statements?

It's not a bug. setMaxRows() is essentially a hint, there's certainly no
requirement that the driver will go off and add LIMIT clauses to your
query, the minimal implementation won't change query execution at all
and will just limit rows coming back out of the ResultSet.. It might be
nice to add LIMIT but that would require the driver to parse query
strings which gets very complicated and isn't going to catch all the
cases anyway. You'll be getting at least some improvement with the
existing driver because the whole resultset isn't being transferred and
processed, even if the plan is still assuming you will grab all the data.

If your queries need a LIMIT clause to get decent performance then your
safest bet is to add a LIMIT clause yourself. You can keep the
setMaxRows() as well if you like..

-O