You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Björn Agel <b....@t-online.de> on 2007/03/20 16:01:58 UTC
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: best solution for updating many objects ?
Posted by Vasily Ivanov <ba...@gmail.com>.
Sorry, can't help. I'm using Spring Jdbc abstraction to do plain sql calls.
Cheers,
Vasily
On 4/19/07, Björn Agel <b....@t-online.de> wrote:
> can you give a code example pls ?
>
> im sticking in how to execute plain sql with ojb ...
>
> thank,
> Björn
>
> Vasily Ivanov wrote:
> > Hi Björn,
> >
> > I usually do retrieve list of IDs via OJB's
> > getReportQueryIteratorByQuery and then run simple plain SQL to bulk
> > update one field in each record by id.
> >
> > Cheers,
> > Vasily
> >
> > On 4/17/07, Björn Agel <b....@t-online.de> wrote:
> >> hi,
> >>
> >> say we have a table, where many thousands objects with about 30 fields
> >> are stored.
> >> Now I want to update only 1 of these 30 colums for a few hundred
> >> objects.
> >>
> >> Atm I retrieve every single object, change 1 value and store it ...
> >>
> >> What is the best solution for this task ?
> >> Is there anything similar to the "ALTER TABLE" statement ?
> >>
> >> with best regards,
> >> Björn Agel
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: best solution for updating many objects ?
Posted by Björn Agel <b....@t-online.de>.
thanks,
thats what i am searching for ;)
regards,
Björn
Armin Waibel wrote:
> Hi Björn,
>
> Björn Agel wrote:
>> can you give a code example pls ?
>>
>> im sticking in how to execute plain sql with ojb ...
>>
>
> you can lookup Statements from the connection and perform the update
> by your own
> http://db.apache.org/ojb/docu/guides/connection.html#Can+I+directly+obtain+a
>
>
>
> or you can use OJB service classes:
>
> broker.beginTransaction();
> ClassDescriptor cld = broker.getClassDescriptor(Article.class);***
> int result = broker.serviceJdbcAccess().executeUpdateSQL("UPDATE
> ARTICLE A0 SET A0.TAX='23' WHERE A0.ID IN ('7','23', '34')", cld);
> broker.commitTransaction();
>
> *** If you execute plain SQL it should work with every
> class-descriptor, no need to specify the correct one (but I never
> tried this).
>
> regards,
> Armin
>
>
>> thank,
>> Björn
>>
>> Vasily Ivanov wrote:
>>> Hi Björn,
>>>
>>> I usually do retrieve list of IDs via OJB's
>>> getReportQueryIteratorByQuery and then run simple plain SQL to bulk
>>> update one field in each record by id.
>>>
>>> Cheers,
>>> Vasily
>>>
>>> On 4/17/07, Björn Agel <b....@t-online.de> wrote:
>>>> hi,
>>>>
>>>> say we have a table, where many thousands objects with about 30 fields
>>>> are stored.
>>>> Now I want to update only 1 of these 30 colums for a few hundred
>>>> objects.
>>>>
>>>> Atm I retrieve every single object, change 1 value and store it ...
>>>>
>>>> What is the best solution for this task ?
>>>> Is there anything similar to the "ALTER TABLE" statement ?
>>>>
>>>> with best regards,
>>>> Björn Agel
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: best solution for updating many objects ?
Posted by Armin Waibel <ar...@apache.org>.
Hi Björn,
Björn Agel wrote:
> can you give a code example pls ?
>
> im sticking in how to execute plain sql with ojb ...
>
you can lookup Statements from the connection and perform the update by
your own
http://db.apache.org/ojb/docu/guides/connection.html#Can+I+directly+obtain+a
or you can use OJB service classes:
broker.beginTransaction();
ClassDescriptor cld = broker.getClassDescriptor(Article.class);***
int result = broker.serviceJdbcAccess().executeUpdateSQL("UPDATE ARTICLE
A0 SET A0.TAX='23' WHERE A0.ID IN ('7','23', '34')", cld);
broker.commitTransaction();
*** If you execute plain SQL it should work with every class-descriptor,
no need to specify the correct one (but I never tried this).
regards,
Armin
> thank,
> Björn
>
> Vasily Ivanov wrote:
>> Hi Björn,
>>
>> I usually do retrieve list of IDs via OJB's
>> getReportQueryIteratorByQuery and then run simple plain SQL to bulk
>> update one field in each record by id.
>>
>> Cheers,
>> Vasily
>>
>> On 4/17/07, Björn Agel <b....@t-online.de> wrote:
>>> hi,
>>>
>>> say we have a table, where many thousands objects with about 30 fields
>>> are stored.
>>> Now I want to update only 1 of these 30 colums for a few hundred
>>> objects.
>>>
>>> Atm I retrieve every single object, change 1 value and store it ...
>>>
>>> What is the best solution for this task ?
>>> Is there anything similar to the "ALTER TABLE" statement ?
>>>
>>> with best regards,
>>> Björn Agel
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: best solution for updating many objects ?
Posted by Björn Agel <b....@t-online.de>.
can you give a code example pls ?
im sticking in how to execute plain sql with ojb ...
thank,
Björn
Vasily Ivanov wrote:
> Hi Björn,
>
> I usually do retrieve list of IDs via OJB's
> getReportQueryIteratorByQuery and then run simple plain SQL to bulk
> update one field in each record by id.
>
> Cheers,
> Vasily
>
> On 4/17/07, Björn Agel <b....@t-online.de> wrote:
>> hi,
>>
>> say we have a table, where many thousands objects with about 30 fields
>> are stored.
>> Now I want to update only 1 of these 30 colums for a few hundred
>> objects.
>>
>> Atm I retrieve every single object, change 1 value and store it ...
>>
>> What is the best solution for this task ?
>> Is there anything similar to the "ALTER TABLE" statement ?
>>
>> with best regards,
>> Björn Agel
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: best solution for updating many objects ?
Posted by Vasily Ivanov <ba...@gmail.com>.
Hi Björn,
I usually do retrieve list of IDs via OJB's
getReportQueryIteratorByQuery and then run simple plain SQL to bulk
update one field in each record by id.
Cheers,
Vasily
On 4/17/07, Björn Agel <b....@t-online.de> wrote:
> hi,
>
> say we have a table, where many thousands objects with about 30 fields
> are stored.
> Now I want to update only 1 of these 30 colums for a few hundred objects.
>
> Atm I retrieve every single object, change 1 value and store it ...
>
> What is the best solution for this task ?
> Is there anything similar to the "ALTER TABLE" statement ?
>
> with best regards,
> Björn Agel
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
best solution for updating many objects ?
Posted by Björn Agel <b....@t-online.de>.
hi,
say we have a table, where many thousands objects with about 30 fields
are stored.
Now I want to update only 1 of these 30 colums for a few hundred objects.
Atm I retrieve every single object, change 1 value and store it ...
What is the best solution for this task ?
Is there anything similar to the "ALTER TABLE" statement ?
with best regards,
Björn Agel
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
Posted by Dennis Bekkering <dj...@gmail.com>.
I think that is because ojb does not add the LIMIT thing to the query. Only
the objects in the range are materialized but everything is selected from
the DB. I guess it is becasue it is not standard SQL, every DB has its own
way.
2007/3/20, Björn Agel <b....@t-online.de>:
>
> 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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
--
mvg,
Dennis
Re: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
Posted by Armin Waibel <ar...@apache.org>.
Hi Björn,
(sorry for the late replay, googlemail marked you as spam)
in upcoming OJB 1.0.5 native limit/offset will be supported for most
popular databases and can be easily implemented for all databases.
See also:
http://marc.info/?l=ojb-user&m=117320896526677&w=2
regards,
Armin
Björn Agel wrote:
> 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: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
Posted by Armin Waibel <ar...@apache.org>.
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" <oj...@db.apache.org>
>> Date: Tue, 20 Mar 2007 17:07:09 +0100
>> From: "Matthias Roth" <ma...@impart.ch>
>> Subject: RE: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
>> To: "OJB Users List" <oj...@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" <oj...@db.apache.org>
>>> Date: Tue, 20 Mar 2007 16:51:24 +0100
>>> From: "Matthias Roth" <ma...@impart.ch>
>>> Subject: RE: massive performance problem with q.setStartAtIndex() and
> q.setEndAtIndex()
>>> To: "OJB Users List" <oj...@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 info@impart.ch
>>>
>>> regards
>>> Matthias Roth
>>>
>>>> -- Original-Nachricht --
>>>> Reply-To: "OJB Users List" <oj...@db.apache.org>
>>>> Date: Tue, 20 Mar 2007 16:01:58 +0100
>>>> From: Björn Agel <b....@t-online.de>
>>>> 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: ojb-user-unsubscribe@db.apache.org
>>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
Posted by Matthias Roth <ma...@impart.ch>.
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" <oj...@db.apache.org>
>Date: Tue, 20 Mar 2007 17:07:09 +0100
>From: "Matthias Roth" <ma...@impart.ch>
>Subject: RE: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
>To: "OJB Users List" <oj...@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" <oj...@db.apache.org>
>>Date: Tue, 20 Mar 2007 16:51:24 +0100
>>From: "Matthias Roth" <ma...@impart.ch>
>>Subject: RE: massive performance problem with q.setStartAtIndex() and
q.setEndAtIndex()
>>To: "OJB Users List" <oj...@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 info@impart.ch
>>
>>regards
>>Matthias Roth
>>
>>>-- Original-Nachricht --
>>>Reply-To: "OJB Users List" <oj...@db.apache.org>
>>>Date: Tue, 20 Mar 2007 16:01:58 +0100
>>>From: Björn Agel <b....@t-online.de>
>>>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: ojb-user-unsubscribe@db.apache.org
>>>For additional commands, e-mail: ojb-user-help@db.apache.org
>>>
>>
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
Posted by Matthias Roth <ma...@impart.ch>.
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" <oj...@db.apache.org>
>Date: Tue, 20 Mar 2007 16:51:24 +0100
>From: "Matthias Roth" <ma...@impart.ch>
>Subject: RE: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
>To: "OJB Users List" <oj...@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 info@impart.ch
>
>regards
>Matthias Roth
>
>>-- Original-Nachricht --
>>Reply-To: "OJB Users List" <oj...@db.apache.org>
>>Date: Tue, 20 Mar 2007 16:01:58 +0100
>>From: Björn Agel <b....@t-online.de>
>>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: ojb-user-unsubscribe@db.apache.org
>>For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: massive performance problem with q.setStartAtIndex() and q.setEndAtIndex()
Posted by Matthias Roth <ma...@impart.ch>.
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 info@impart.ch
regards
Matthias Roth
>-- Original-Nachricht --
>Reply-To: "OJB Users List" <oj...@db.apache.org>
>Date: Tue, 20 Mar 2007 16:01:58 +0100
>From: Björn Agel <b....@t-online.de>
>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: ojb-user-unsubscribe@db.apache.org
>For additional commands, e-mail: ojb-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org