Re: Limit vs setMaxRows issue

From: Mark Lewis <mark(dot)lewis(at)mir3(dot)com>
To: Sebastiaan van Erk <sebster(at)sebster(dot)com>
Cc: Kris Jurka <books(at)ejurka(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Limit vs setMaxRows issue
Date: 2006-06-22 16:15:47
Message-ID: 1150992947.19111.200.camel@archimedes
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-jdbc

> 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

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Joost Kraaijeveld 2006-06-22 16:28:43 Re: [JDBC] How to notify JBoss J2EE application in a
Previous Message Sergii Sinelnychenko 2006-06-22 14:59:03 ResultSetMetaData + CachedResultSet bug