sorry I try again, it seam that some filter remove the statment:
but the attribute tag at the end of your class descriptor
"<attribute
attribute-name="sql-select-suffix"
attribute-value="LIMIT 50000,10" />"
regards
Matthias
>-- Original-Nachricht --
>Reply-To: "OJB Users List" <ojb-user@db.apache.org>
>Date: Tue, 20 Mar 2007 17:07:09 +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>
>
>
>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]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
