Re: Performance Issue on lookup on a table

[Armin Waibel <ar...@apache.org>]

Hi Eric and Roger,

checked in the initial version of native limit/offset support in CVS 
(OJB_1_0_RELEASE branch). In class PaginationTest you can find example/tests
http://svn.apache.org/viewvc/db/ojb/branches/OJB_1_0_RELEASE/src/test/org/apache/ojb/broker/PaginationTest.java?view=markup

I run these tests again hsql, mysql, maxDB(get some failures, because of 
driver bug) and oracleXE.
Native limit/offset support is implemented only for the most popular 
databases - see ...broker.platforms.Platform classes source code. If you 
need support for other database feel free to send patches ;-)

Please, if you can spend time give it a try.

regards,
Armin


Janssen, Roger wrote:
> Hi,
> 
>> The limit support can't substitute the real "native based" pagination. 
>> My thought was to combine current OJB-pagination with new limit support
> and 
>> fetch-size - e.g set limit to 300, Query.setStartAtIndex(150),
>> Query.setEndAtIndex(300) to get results 150...300 and fetch-size=30 set
> to 
>> web-page result number size. This isn't perfect, but I think this will 
>> be much more effective on large result sets as current behavior
> (without limit).
> 
> Suggested solution only results in improved performance iterating over
> the first pages. Nearing the end of the (large) resultset, performance
> issues will rear their ugly head again. But.... in real the world, this
> might work, since i do not believe people will iterate over manymanymany
> pages to get to the end of a large resultset. When searching, they would
> narrow their search criteria, for picklists, they would enter filter
> criteria like startswith to narrow down the resultset, so if fetching
> the first pages of a large resultset will have good performance, it
> think your solution might actually solve this problem in the real world
> 
> 
>> You are right, native pagination support will be much more complex to 
>> implement than limit support (your examples point out the problem).
>> But isn't it in most cases a simple sql-string split to integrate 
>> the paging query string?
> 
> Yeah... It boils down to sql string manipulation, but before using OR
> mappers, I had to do this in my applications myself and I remember it
> being a pain to get it to work with all the supported databases.
> 
> 
> Roger Janssen
> iBanx
> *************************************************************************
> The information contained in this communication is confidential and is intended solely for the use of the individual or entity to  whom it is addressed.You should not copy, disclose or distribute this communication without the authority of iBanx bv. iBanx bv is neither liable for the proper and complete transmission of the information has been maintained nor that the communication is free of viruses, interceptions or interference. 
> 
> If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org