Re: Limit vs setMaxRows issue

> The reason I would like to see this feature (instead of adding the LIMIT 
> manually) is for cross database compatibility (which is the essence of 
> JDBC). Basically, setMaxRows is portable, LIMIT is not. Since I am part 
> of a team developing a cross-database application in which performance 
> is often important, this feature is quite important to us. Currently 
> postgres is slow for us on simple index queries on large data sets (1.8 
> seconds for the first 100 primary keys only of a table of 43000 rows); 
> and unfortunately, these kinds of queries are very common in our 
> application.

JDBC is a little too low-level to give true database independence; you
can write portable queries, but you're severely restricted when it comes
to functionality supported by most databases but not in a standardized
way, such as limits, locking, performance hinting, sequences/serials,
etc.

For simple, non-performance critical apps you can mostly get away with
it (as we did for a while with some of our products).  But for anything
more sophisticated, your application really needs a way to deal with
database-specific SQL.

On newer projects we use Hibernate HQL, which has been a major boon in
terms of database portability and performance.

-- Mark Lewis