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]
