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]
