Hi Matthias or other DB2 user,
some time ago i checked in native limit/offset support for the most
popular databases. But I don't have DB2 installed.
It would be great if a DB2 user could test the native limit/offset
support for DB2.
TODO:
- Get the latest version from SVN OJB_1_0_RELEASE branch
- cd /profiles/db2.profiles and setup the database
- cd /build.properties select 'profile=db2'
- setup test tables, console: ant -Duse-ddlutils=true prepare-testdb
- Prepare your IDE to run test '...broker.PaginationTest' from OJB test
suite (recommended to set the working directory to /target/test/ojb)
- Run the test - should run without errors. If the driver doesn't proper
support ResultSet.absolute(...) and ResultSet.relative(...) the
testPagingPosition_XY tests may fail (e.g. this happens with maxDB/sapDB).
- Now refer to PlatformDb2Impl
http://svn.apache.org/viewvc/db/ojb/branches/OJB_1_0_RELEASE/src/java/org/apache/ojb/broker/platforms/PlatformDb2Impl.java?view=markup
and comment in one of the two versions for limit+offset in method
#addPagingSql(...) and set #supportsOffset() to return true.
- Run the PaginationTest again
- Post your experience, patches .... ;-)
regards,
Armin
Matthias Roth wrote:
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
