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

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]
>