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" <ojb-user@db.apache.org>
>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" <ojb-user@db.apache.org>
>
>
>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]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
