Re: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()

Hi Matthias or other DB2 user,

some time ago i checked in native limit/offset support for the most 
popular databases. But I don't have DB2 installed.

It would be great if a DB2 user could test the native limit/offset 
support for DB2.

TODO:
- Get the latest version from SVN OJB_1_0_RELEASE branch
- cd /profiles/db2.profiles and setup the database
- cd /build.properties select 'profile=db2'
- setup test tables, console: ant -Duse-ddlutils=true prepare-testdb
- Prepare your IDE to run test '...broker.PaginationTest' from OJB test 
suite (recommended to set the working directory to /target/test/ojb)
- Run the test - should run without errors. If the driver doesn't proper 
support ResultSet.absolute(...) and ResultSet.relative(...) the 
testPagingPosition_XY tests may fail (e.g. this happens with maxDB/sapDB).
- Now refer to PlatformDb2Impl
http://svn.apache.org/viewvc/db/ojb/branches/OJB_1_0_RELEASE/src/java/org/apache/ojb/broker/platforms/PlatformDb2Impl.java?view=markup
and comment in one of the two versions for limit+offset in method 
#addPagingSql(...) and set #supportsOffset() to return true.
- Run the PaginationTest again
- Post your experience, patches .... ;-)

regards,
Armin

Matthias Roth wrote:
> sorry I try again, it seam that some filter remove the statment:
> but the attribute tag at the end of your class descriptor
> "<attribute 
>    attribute-name="sql-select-suffix" 
>    attribute-value="LIMIT 50000,10" />"
> 
> regards
> Matthias
>> -- Original-Nachricht --
>> Reply-To: "OJB Users List" <[email protected]>
>> Date: Tue, 20 Mar 2007 17:07:09 +0100
>> From: "Matthias Roth" <[email protected]>
>> Subject: RE: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
>> To: "OJB Users List" <[email protected]>
>>
>>
>> ups the example was corrupted:
>> <class-descriptor
>>  class="User"
>>  table="User">
>>  ...
>> <attribute attribute-name="sql-select-suffix" attribute-value="LIMIT 50000,10"
>> />
>> </class-descriptor>
>>> -- Original-Nachricht --
>>> Reply-To: "OJB Users List" <[email protected]>
>>> Date: Tue, 20 Mar 2007 16:51:24 +0100
>>> From: "Matthias Roth" <[email protected]>
>>> Subject: RE: massive performance problem with q.setStartAtIndex() and
> q.setEndAtIndex()
>>> To: "OJB Users List" <[email protected]>
>>>
>>>
>>> Hi Björn
>>> your sql statment is not an ANSI-SQL statment, the suffix "LIMIT 50000,10"
>>> is
>>> a platform specific command. We had to resolve the same Problem in a Project
>>> for ibm db2. We had to add for some object "FETCH FIRST 350 ROWS ONLY
> OPTIMIZE
>>> FOR 350 ROWS FOR READ ONLY".
>>>
>>> We solved the problem by implementing a SQLGenerator and SQLStatment class
>>> that supports suffixes in
>>> the repository.xml file as additional attributes:
>>>
>>> public class FwSqlGenerator extends SqlGeneratorDefaultImpl {
>>>    private Logger logger = LoggerFactory.getLogger(FwSqlGenerator.class);
>>>    private Platform m_platform;
>>> 	public FwSqlGenerator(Platform platform) {
>>> 		super(platform);
>>> 		this.m_platform = platform;
>>> 	}
>>>
>>> 	public SelectStatement getPreparedSelectStatement(Query query, ClassDescriptor
>>> cld) {
>>>                         SelectStatement sql = new FwSqlSelectStatement(m_platform,
>>> cld, query, logger);
>>>                         if (logger.isDebugEnabled())
>>>                         {
>>>                                logger.debug("SQL:" + sql.getStatement());
>>>                          }
>>>                          return sql;
>>>                    }
>>> }
>>>
>>> public class FwSqlSelectStatement extends SqlSelectStatement {
>>> 	public static final String SQL_SELECT_SUFFIX="sql-select-suffix";
>>> 	public FwSqlSelectStatement(Platform pf, ClassDescriptor cld, Query query,
>>> 			Logger logger) {
>>> 		super(pf, cld, query, logger);
>>> 	}
>>> 	public FwSqlSelectStatement(SqlQueryStatement parent, Platform pf,
>>> 			ClassDescriptor cld, Query query, Logger logger) {
>>> 		super(parent, pf, cld, query, logger);
>>> 	}
>>>
>>> 	protected String buildStatement(){
>>> 		String statment=super.buildStatement();
>>> 		ClassDescriptor cld=getBaseClassDescriptor();
>>> 		//get suffix
>>> 		String suffix=cld.getAttribute(SQL_SELECT_SUFFIX);
>>> 		//if the attribute "sql-select-suffix" in the repository.xml file is
>> set
>>> 		//then add the suffix to the statment.
>>> 		if(suffix!=null && suffix.length()>0){
>>> 			statment=statment+" "+suffix;
>>> 		}
>>> 		return statment;
>>> 	}
>>> }
>>>
>>> In the OJB.properties you have to change the propertie "SqlGeneratorClass="
>>> to your SQLGenerator Class.
>>>
>>> In the repository.xml file you can now add suffixes for optimise the sql
>>> like this:
>>> <class-descriptor
>>>  class="User"
>>>  table="User">
>>>  ...
>>>
>>>  <attribute attribute-name="sql-select-suffix" attribute-value="FOR READ
>>> ONLY" />
>>> </class-descriptor>
>>>
>>> I hope that heps you.
>>>
>>> If you have more questions about this problem
>>> you can conntact me at [email protected]
>>>
>>> regards
>>> Matthias Roth
>>>
>>>> -- Original-Nachricht --
>>>> Reply-To: "OJB Users List" <[email protected]>
>>>> Date: Tue, 20 Mar 2007 16:01:58 +0100
>>>> From: Björn Agel <[email protected]>
>>>> To:  [email protected]
>>>> Subject: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
>>>>
>>>>
>>>> hi there,
>>>>
>>>> i figured out a performance problem with large tables using
>>>> q.setStartAtIndex() and q.setEndAtIndex()
>>>> there are more than 100.000 entries in the table and a simple search
>>>> which should return about 90.000 items still takes more than 10(!) seconds.
>>>> i am using the index methods to retrieve only 10 datasets per query out
>>>> of these 90.000 total items using an iterator.
>>>>
>>>> when i implement the same query directly as a sql command like:
>>>> "SELECT * FROM user WHERE name LIKE '%name%' LIMIT 50000,10"
>>>> the query takes less than 1 second ...
>>>>
>>>> am i doing something wrong or is it a bug ?
>>>>
>>>> with best regards,
>>>> Björn
>>>>
>>>>
>>>> ---------------------------------------------------------------------
>>>> To unsubscribe, e-mail: [email protected]
>>>> For additional commands, e-mail: [email protected]
>>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [email protected]
>>> For additional commands, e-mail: [email protected]
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
> 
>