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

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