empire-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Amin Abbaspour <abbaspour_a...@yahoo.com>
Subject re: Limit Query Results inside DBCommand
Date Mon, 30 Nov 2009 08:03:20 GMT
Dear Rainer,

JDBC setMaxRows is not equivalent to SQL LIMIT from the database's point of view. setMaxRows
sets the max number of rows ResultSet can contain and AFAIK ResultSet ignores the results
more that that limit, while SQL LIMIT informs database engine about the count of the rows
client wants hence engine does not perform more work as soon as it gets to requested number
of rows.

As Francis mentioned, pagination is important too. This is particularly useful when we have
paged view (e.g. LOV pattern).

Besides limit and pagination which is occasionally required based on application logic, limiting
has tangible performance results in some databases engines such as DB2, hence its always said
that one would better append a 'FETCH FIRST n ROWS ONLY' if he is sure his query has a clause
that will return only N row(s).

I personally don't see much problem in implementing this. If you want, I can do it and sent
diff files.

BTW I also want to add some LOCKING feature to SQLServer SELECT but would like to know your
point of view to see how to implement that we both preserver polymorphism/OOP and database
specific features. 

Regards,
Amin

--- On Sun, 11/29/09, Rainer Döbele <doebele@esteam.de> wrote:

> From: Rainer Döbele <doebele@esteam.de>
> Subject: re: Limit Query Results inside DBCommand
> To: empire-db-dev@incubator.apache.org
> Date: Sunday, November 29, 2009, 11:47 PM
> Francis De Brabandere wrote:
> > Re: Limit Query Results inside DBCommand
> > 
> > you might also want to implement paging, added a
> comment tot the issue
> 
> Not sure whether we should to do this.
> If we solve it using the setMaxRows() on the statement,
> which is what I prefer, then there does not seem to be a way
> to specify an offset.
> 
> At the moment I cannot see a solution for the paging that
> will work for all databases.
> If this is a special feature of MySql then we can provide
> it as a particular extension for MySql (just like the
> connectByPrior that we support for Oracle).
> 
> Rainer
> 
> > 
> > On Sun, Nov 29, 2009 at 1:25 PM, Rainer Döbele <doebele@esteam.de>
> > wrote:
> > > Hi Amin,
> > >
> > > unfortunately I must confess that we currently
> don't have the ability
> > to limit the number of rows returned.
> > >
> > > This shows how different people work with
> databases.
> > > In my many years of database programming I have
> never really felt the
> > need for it since I always found a constraint to work
> with.
> > > I only sometimes use it for interactive queries
> to the database.
> > > But you are right, this is a thing we definitely
> have to add.
> > >
> > > I have created a JIRA issue for that and I will
> implement that ASAP.
> > > Unfortunately this feature is handled differently
> by each database.
> > > e.g.
> > > SQL Server:
> > > SELECT TOP 10 id, name, ...
> > > FROM contacts
> > >
> > > MySQL:
> > > SELECT id, name, ...
> > > FROM contacts
> > > LIMIT 10
> > >
> > > ORACLE:
> > > SELECT id, name, ...
> > > FROM contacts
> > > WHERE ROWNUM <= 10
> > >
> > > Another option is to use setMaxRows() before
> executing the statement.
> > > This looks like a better idea to me since the
> JDBC driver can handle
> > it.
> > > Does anyone know if this is really equivalent?
> > >
> > > Rainer
> > >
> > >
> > > Amin Abbaspour wrote:
> > >> re: Limit Query Results inside DBCommand
> > >>
> > >> Hello to everyone,
> > >>
> > >> This is my first email in empire-db's users
> list :)
> > >>
> > >> One small question; How can I limit the
> number of rows returned in
> > >> select statements (i.e. SELECT .... LIMIT x,
> or SELECT TOP x ...)?
> > >>
> > >> Right now I do this manually by appending a
> "LIMIT x" to my query's
> > >> string but this is such a frequent
> requirement that I am pretty sure
> > >> this is implemented but I failed to find it.
> > >>
> > >> Regards,
> > >> Amin Abbaspour
> > >>
> > >>
> > >>
> > >>
> > >
> > 
> > 
> > 
> > --
> > http://www.somatik.be
> > Microsoft gives you windows, Linux gives you the whole
> house.
> 


      

Mime
View raw message