Re: Limit vs setMaxRows issue

Hi,

We actually do use a part of hibernate (the dialects) with some custom
additions, especially for creating tables, indexes, etc. However, since
the user of the application creates the data model (and changes it
runtime), and since the user is actually working in a tables and
columns paradigm, we cannot really use the ORM or HQL parts of hibernate.

Anyway, apart from the joys (i.e. difficulties) in writing portable SQL
(oh what fun ;-)), I still think it would be a nice improvement to the
JDBC driver is setMaxRows did actually hard limit the number of returned
rows, considering it hardly make sense in this case for postgres to
prepare to return more rows than specified.

I'm willing to help or even code it if someone points me in the right
direction. The JDBC part should be easy; I'm not sure about the postgres
side, but I imagine it's not too difficult either. The hard part is
probably getting the protocol extended and getting people to agree that
it's a good idea to change the protocol, etc... ;-) (although it's a
pretty non-intrusive, backwards compatible change).

Regards,
Sebastiaan

Mark Lewis wrote:
>> 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
>
>
>