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" <ojb-user@db.apache.org> >Date: Tue, 20 Mar 2007 16:01:58 +0100 >From: Björn Agel <[EMAIL PROTECTED]> >To: ojb-user@db.apache.org >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]