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 "Ferrer, Eric" <er...@transcore.com> on 2007/02/02 00:51:44 UTC
Performance Issue on lookup on a table
I am experience an issue with an OJB query using a persistable object.
Basically its taking 7 minutes to return on the call getObjectByQuery.
When I run the query
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Performance Issue on lookup on a table
Posted by "Ferrer, Eric" <er...@transcore.com>.
Armin,
Thanks for your reply. Honestly I am at a lost here as well. I understand what Roger was talking about, but how to solve it i dont know. Using a profiler, I can see the query generate contains select top 1 * from xyzTable but it looks the calls from OJB through to the JDBC driver is not performing the same function call as making a simple
JDBC select statement.
Looking at the profiler, there are a lot of cursor setups and stored procedure calls occuring when we call that table using the persistable calls. Not being a DBA myself I dont know why.
The query from a native SQL client is very fast, so is that same query being performed from JDBC and also from OJB when using that queryWithSQL method. I know from some JDBC drivers you can make the call statement.setMaxRow() I believe but it looks like, and I havent tried this yet, that the persistanct call is loading all 3gigs worth of data into either a ROWSET or Collection and then the cursor is being navigated to the row that matches our criteria. Can you explain more on the behavior I am seeing please? Its tough to talk to our DBA and explain whats going on with my limited knowledge about the inner workings of the OJB sql query engine.
Can we get some input on the person(s) that work with the query-engine?
When is the next major OJB release?
Thanks
-Eric
________________________________
From: Armin Waibel [mailto:arminw@apache.org]
Sent: Fri 2/2/2007 7:05 PM
To: OJB Users List
Subject: Re: Performance Issue on lookup on a table
Hi Eric,
Ferrer, Eric wrote:
> Thanks for the reply, Can the OJB developers reply with their thoughts
> on this.
>
> I would have figured the load by persistence object calls we make and we
> do them when we only want 1 record a lot would do the same job as the
> SQL select statement.
>
> For a while I thought maybe our driver was an issue.
>
> This may be major for us since we auto generate a lot of our OR mapping
> using Velocity so as to not expose the data model. From what you just
> told me we have to analyze table growth and convert our Persistence Load
> Query to SQL calls.
>
First, I'm not a sql/database expert and only do marginal work on the
query-engine of OJB - so please bear with me ;-)
I think it would be possible to add support for l"imiting row numbers"
in OJB 1.0.x branch. As far as I know to "limit the number of rows
returned" is not SQL standard and depends on the DB vendor:
DB2: select * from table fetch first 5 rows only
MySQL and PostgreSQL: select * from table limit 5
Oracle: select * from table rownum <= 5
SQL Server: select top 5 * from table
Here is my train of thoughts:
I don't know if we can use method Query.set(Start/End)AtIndex(...) to
add "row limit" support. Maybe we have to add a new method
Query.setRowLimit(...).
Before a Query object is executed in OJB method query.preprocess(broker)
is called. With the PB instance we can access the Platform class were we
have to locate the different "row limit" information.
Now the "only thing" to do is to integrate the limit expression string
in the query string. Think we can add the "limit expression string"
while the query string is created in SqlSelectStatement#buildSqlString().
What do you think?
regards,
Armin
> -Eric
>
> -----Original Message-----
> From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
> Sent: Friday, February 02, 2007 1:29 AM
> To: OJB Users List
> Subject: RE: Performance Issue on lookup on a table
>
> Hi,
>
> We have the same problem. This is because OJB does not (yet?) implement
> paging based on RDBMS native paging/limiting syntax. A query, hitting
> the complete resultset is executed and cursors are used to fetch the
> requested page. Large resultsets, will take a lot of time.
>
> We have been experiencing more and more problems over the last few years
> with this.
>
> I am wating for the OJB 1.1 release (For over 2 years now!) and hoping
> that it will implemented this feature. Right now, I have to expose the
> datamodel within my application code, bypassing our domain model and OJB
> as OR mapper, and that really hurts.
>
> Greetings,
>
> Roger Janssen
> iBanx
>
> -----Original Message-----
> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
> Sent: vrijdag 2 februari 2007 3:54
> To: OJB Users List
> Subject: RE: Performance Issue on lookup on a table
>
> Just to add it appears to work when passing a straight SQL query versus
> using the persistable method.
>
> -Eric
>
> -----Original Message-----
> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
> Sent: Thursday, February 01, 2007 4:57 PM
> To: OJB Users List
> Subject: RE: Performance Issue on lookup on a table
>
> Sorry
>
> Accidently hit send.
>
> What I was saying the query generate looks like this
>
> SELECT TOP 1
>
> A0.SERIAL_NUMBER,A0.AD_ID,A0.LIC_NUM,A0.RECEIVED_DTIME,A0.LIC_ST,A0.LIC_
> TYPE
>
> FROM MY_LIC_TLB A0
>
> WHERE ((A0.LIC_NUM = '1236713' ) AND A0.LIC_ST = 'DD' ) AND
> A0.LIC_TYPE = 'NORMAL'
>
> When I run it in the sql management tool it comes back in less than 20
> seconds, however via ojb it varies between 5 and 7 minutes.
>
> The table has 3 gigs of data.
>
> Any ideas what can be the difference?
>
> We do lots of similar calls and this is the first one we ever
> experienced this with.
>
> Thanks
> -Eric
>
> -----Original Message-----
> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
> Sent: Thursday, February 01, 2007 4:52 PM
> To: OJB Users List
> Subject: Performance Issue on lookup on a table
>
>
> I am experience an issue with an OJB query using a persistable object.
> Basically its taking 7 minutes to return on the call getObjectByQuery.
>
> When I run the query
>
>
> ---------------------------------------------------------------------
> 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
> ************************************************************************
> *
> The information contained in this communication is confidential and is
> intended solely for the use of the individual or entity to whom it is
> addressed.You should not copy, disclose or distribute this communication
> without the authority of iBanx bv. iBanx bv is neither liable for the
> proper and complete transmission of the information has been maintained
> nor that the communication is free of viruses, interceptions or
> interference.
>
> If you are not the intended recipient of this communication please
> return the communication to the sender and delete and destroy all
> copies.
>
>
>
>
>
> ---------------------------------------------------------------------
> 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: Performance Issue on lookup on a table
Posted by Armin Waibel <ar...@apache.org>.
Hi Eric,
Ferrer, Eric wrote:
> Armin,
>
> Its nice to know that you are thinking about the web, but my issues
> dont revolve around the web at this point.
>
> When I do a persistable query, if I get a collection, I set the max
> to what ever configurable param, display 20 records or more per page,
> and I already have another struts/jsf solution to handle the user
> interaction from paging x pages with out going back to the database.
> In fact I dont think we need this since on the pages we access for
> these types of queries, I believe its all in a collection or cached
> result set.
>
> What I want is for the select top query to be native supported since
> in most case we simply expect 1 record from a database table
native limit querying (in case of MSSQL: use of 'select top'-syntax) is
a subset of native paging. Thus if I find a possibility to support
native paging in OJB your problem will be solved too.
> or a 1:n
> relationship (i.e. 1 customer can have numerous contact). The table
> in question is populated monthly, outside of my controll and our
> lookups should only return 1 record always due to the referential
> integrity constraint. Actually we set our persistable object with
> the keys that are only indexed as for performance reason. At first I
> thought we was doing a full table scan. Now I know better.
> Hopefully there can be some short term solution to prevent the
> disclosing of our data model layer.
Is it possible to change the query and add another restriction field.
For example isn't the A0.RECEIVED_DTIME of the new object > then the old
one?
Is it possible to do a report-query first to the the PK of the object
and then to lookup the object by PK?
In your "select top 1 ..." example you don't use a 'order by' clause is
this a typing error?
>
> Does this behavior we are talking about occur on any other RDMS
> function calls we perform?
> I know its SQL Server and I had no
> choice, but I think i get page locks when I do logical persisted
> calls to update a set of tables per our business work flow processes
> (5 table prior to commit).
>
> Lets keep this discussion going, perhaps there are some other Object
> Relational Bridge tools we can bench off for this solution?
> Hibernate? EJB3? POJO ?
On my local PC I regular bench OJB against Hibernate and IBatis based on
the OJB performance test
http://db.apache.org/ojb/docu/guides/performance.html#OJB+performance+in+multi-threaded+environments
But this test only compares the performance of CRUD-operations.
AFAIK latest version of Hib. supports native limit support for MSSQL,
thus it should be faster then OJB in this specific case.
regards,
Armin
>
> -Eric
>
> ________________________________
>
> From: Armin Waibel [mailto:arminw@apache.org] Sent: Mon 2/5/2007 7:38
> PM To: OJB Users List Subject: Re: Performance Issue on lookup on a
> table
>
>
>
> Hi Roger,
>
> Janssen, Roger wrote:
>> Hi,
>>
>> Hearing that the pagination might be implemented using native SQL
>> limiting syntax puts a smile on my face. It might be a bit more
>> complex though then suggested by Armin.
>
> All beginnings are difficult ;-) The limit support can't substitute
> the real "native based" pagination. My thought was to combine current
> OJB-pagination with new limit support and fetch-size - e.g set limit
> to 300, Query.setStartAtIndex(150), Query.setEndAtIndex(300) to get
> results 150...300 and fetch-size=30 set to web-page result number
> size. This isn't perfect, but I think this will be much more
> effective on large result sets as current behavior (without limit).
>
>
>> We do not only want to limit the amount of records, but also want
>> to be able to define an offset, aka request a specific page. Since
>> every RDBMS has its own logic, it is not straightforward. A
>> re-write of the quey might even be necessary, instead of just
>> adding a limit-clause:
>>
>> MSSQL:
>>
>> SELECT * FROM ( SELECT TOP n * FROM ( SELECT TOP z columns --
>> (z=n+skip) FROM tablename ORDER BY key ASC ) AS FOO ORDER BY key
>> DESC -- ('FOO' may be anything) ) AS BAR ORDER BY key ASC --
>> ('BAR' may be anything)
>>
>> MYSQL:
>>
>> SELECT columns FROM tablename ORDER BY key ASC LIMIT n OFFSET skip
>>
>> ORACLE:
>>
>> SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rn,
>> columns FROM tablename ) WHERE rn > skip AND rn <= (n+skip)
>>
>> (I think the oracle example can be simplified though.)
>>
>> And of course, there are many other platforms as well.
>>
>
> You are right, native pagination support will be much more complex to
> implement than limit support (your examples point out the problem).
> But isn't it in most cases a simple sql-string split to integrate the
> paging query string?
>
>> [ I got this info from
>> http://troels.arvin.dk/db/rdbms/#select-limit-offset ]
>>
>
> Excellent link!
>
>
>> And to complicate it even more, if the platform will not be
>> supported by OJB, I think it would be nice if OJB falls back on
>> it's current implementation. But the buildup of the resultsets is
>> of course completey different using native pagination and the
>> current OJB implementation, can they co-exist?
>
> In the current implementation OJB simply use a RsIterator wrapper
> class to "simulate" paging. Thus if native paging is supported by OJB
> it has to use the default RsIterator and as fall back OJB can use the
> current wrapper class. Give me time to think more about it (seems it
> could be possible to implement native paging without changing too
> many classes and internal interfaces - I could be fatally wrong ;-)).
> By the way, it seems that someone started with paging support some
> time ago (see Platform class methods) but never implement it.
>
> regards, Armin
>
>> Roger Janssen iBanx
>>
>> -----Original Message----- From: Armin Waibel
>> [mailto:arminw@apache.org] Sent: zaterdag 3 februari 2007 3:05 To:
>> OJB Users List Subject: Re: Performance Issue on lookup on a table
>>
>> Hi Eric,
>>
>> Ferrer, Eric wrote:
>>> Thanks for the reply, Can the OJB developers reply with their
>>> thoughts on this.
>>>
>>> I would have figured the load by persistence object calls we make
>>> and we do them when we only want 1 record a lot would do the same
>>> job as the SQL select statement.
>>>
>>> For a while I thought maybe our driver was an issue.
>>>
>>> This may be major for us since we auto generate a lot of our OR
>>> mapping using Velocity so as to not expose the data model. From
>>> what you just told me we have to analyze table growth and convert
>>> our Persistence Load Query to SQL calls.
>>>
>> First, I'm not a sql/database expert and only do marginal work on
>> the query-engine of OJB - so please bear with me ;-)
>>
>> I think it would be possible to add support for l"imiting row
>> numbers" in OJB 1.0.x branch. As far as I know to "limit the number
>> of rows returned" is not SQL standard and depends on the DB vendor:
>>
>>
>> DB2: select * from table fetch first 5 rows only MySQL and
>> PostgreSQL: select * from table limit 5 Oracle: select * from table
>> rownum <= 5 SQL Server: select top 5 * from table
>>
>> Here is my train of thoughts: I don't know if we can use method
>> Query.set(Start/End)AtIndex(...) to add "row limit" support. Maybe
>> we have to add a new method Query.setRowLimit(...). Before a Query
>> object is executed in OJB method query.preprocess(broker) is
>> called. With the PB instance we can access the Platform class were
>> we have to locate the different "row limit" information. Now the
>> "only thing" to do is to integrate the limit expression string in
>> the query string. Think we can add the "limit expression string"
>> while the query string is created in
>> SqlSelectStatement#buildSqlString().
>>
>> What do you think?
>>
>> regards, Armin
>>
>>
>>> -Eric
>>>
>>> -----Original Message----- From: Janssen, Roger
>>> [mailto:roger.janssen@ibanx.nl] Sent: Friday, February 02, 2007
>>> 1:29 AM To: OJB Users List Subject: RE: Performance Issue on
>>> lookup on a table
>>>
>>> Hi,
>>>
>>> We have the same problem. This is because OJB does not (yet?)
>>> implement paging based on RDBMS native paging/limiting syntax. A
>>> query, hitting the complete resultset is executed and cursors are
>>> used to fetch the requested page. Large resultsets, will take a
>>> lot of
>> time.
>>> We have been experiencing more and more problems over the last
>>> few years with this.
>>>
>>> I am wating for the OJB 1.1 release (For over 2 years now!) and
>>> hoping that it will implemented this feature. Right now, I have
>>> to expose the datamodel within my application code, bypassing our
>>> domain model and OJB as OR mapper, and that really hurts.
>>>
>>> Greetings,
>>>
>>> Roger Janssen iBanx
>>>
>>> -----Original Message----- From: Ferrer, Eric
>>> [mailto:eric.ferrer@transcore.com] Sent: vrijdag 2 februari 2007
>>> 3:54 To: OJB Users List Subject: RE: Performance Issue on lookup
>>> on a table
>>>
>>> Just to add it appears to work when passing a straight SQL query
>>> versus using the persistable method.
>>>
>>> -Eric
>>>
>>> -----Original Message----- From: Ferrer, Eric
>>> [mailto:eric.ferrer@transcore.com] Sent: Thursday, February 01,
>>> 2007 4:57 PM To: OJB Users List Subject: RE: Performance Issue on
>>> lookup on a table
>>>
>>> Sorry
>>>
>>> Accidently hit send.
>>>
>>> What I was saying the query generate looks like this
>>>
>>> SELECT TOP 1
>>>
>>> A0.SERIAL_NUMBER,A0.AD_ID,A0.LIC_NUM,A0.RECEIVED_DTIME,A0.LIC_ST,A0.LI
>>> C_ TYPE
>>>
>>> FROM MY_LIC_TLB A0
>>>
>>> WHERE ((A0.LIC_NUM = '1236713' ) AND A0.LIC_ST = 'DD' ) AND
>>> A0.LIC_TYPE = 'NORMAL'
>>>
>>> When I run it in the sql management tool it comes back in less
>>> than 20 seconds, however via ojb it varies between 5 and 7
>>> minutes.
>>>
>>> The table has 3 gigs of data.
>>>
>>> Any ideas what can be the difference?
>>>
>>> We do lots of similar calls and this is the first one we ever
>>> experienced this with.
>>>
>>> Thanks -Eric
>>>
>>> -----Original Message----- From: Ferrer, Eric
>>> [mailto:eric.ferrer@transcore.com] Sent: Thursday, February 01,
>>> 2007 4:52 PM To: OJB Users List Subject: Performance Issue on
>>> lookup on a table
>>>
>>>
>>> I am experience an issue with an OJB query using a persistable
>>> object. Basically its taking 7 minutes to return on the call
>>> getObjectByQuery.
>>>
>>> When I run the query
>>>
>>>
>>> ---------------------------------------------------------------------
>>> 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
>>> **********************************************************************
>>> ** * The information contained in this communication is
>>> confidential and is intended solely for the use of the individual
>>> or entity to whom it is addressed.You should not copy, disclose
>>> or distribute this communication without the authority of iBanx
>>> bv. iBanx bv is neither liable for the proper and complete
>>> transmission of the information has been maintained nor that the
>>> communication is free of viruses, interceptions or interference.
>>>
>>> If you are not the intended recipient of this communication
>>> please return the communication to the sender and delete and
>>> destroy all copies.
>>>
>>>
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> 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
>
>
>
>
> ---------------------------------------------------------------------
> 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: Performance Issue on lookup on a table
Posted by "Ferrer, Eric" <er...@transcore.com>.
Armin,
Its nice to know that you are thinking about the web, but my issues dont revolve around the web at this point.
When I do a persistable query, if I get a collection, I set the max to what ever configurable param, display 20 records or more per page, and I already have another struts/jsf solution to handle the user interaction from paging x pages with out going back to the database. In fact I dont think we need this since on the pages we access for these types of queries, I believe its all in a collection or cached result set.
What I want is for the select top query to be native supported since in most case we simply expect 1 record from a database table or a 1:n relationship (i.e. 1 customer can have numerous contact). The table in question is populated monthly, outside of my controll and our lookups should only return 1 record always due to the referential integrity constraint. Actually we set our persistable object with the keys that are only indexed as for performance reason. At first I thought we was doing a full table scan. Now I know better. Hopefully there can be some short term solution to prevent the disclosing of our data model layer.
Does this behavior we are talking about occur on any other RDMS function calls we perform? I know its SQL Server and I had no choice, but I think i get page locks when I do logical persisted calls to update a set of tables per our business work flow processes (5 table prior to commit).
Lets keep this discussion going, perhaps there are some other Object Relational Bridge tools we can bench off for this solution? Hibernate? EJB3? POJO ?
-Eric
________________________________
From: Armin Waibel [mailto:arminw@apache.org]
Sent: Mon 2/5/2007 7:38 PM
To: OJB Users List
Subject: Re: Performance Issue on lookup on a table
Hi Roger,
Janssen, Roger wrote:
> Hi,
>
> Hearing that the pagination might be implemented using native SQL
> limiting syntax puts a smile on my face. It might be a bit more complex
> though then suggested by Armin.
All beginnings are difficult ;-)
The limit support can't substitute the real "native based" pagination.
My thought was to combine current OJB-pagination with new limit support
and fetch-size - e.g set limit to 300, Query.setStartAtIndex(150),
Query.setEndAtIndex(300) to get results 150...300 and fetch-size=30 set
to web-page result number size. This isn't perfect, but I think this
will be much more effective on large result sets as current behavior
(without limit).
> We do not only want to limit the amount
> of records, but also want to be able to define an offset, aka request a
> specific page. Since every RDBMS has its own logic, it is not
> straightforward. A re-write of the quey might even be necessary, instead
> of just adding a limit-clause:
>
> MSSQL:
>
> SELECT * FROM (
> SELECT TOP n * FROM (
> SELECT TOP z columns -- (z=n+skip)
> FROM tablename
> ORDER BY key ASC
> ) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
> ) AS BAR ORDER BY key ASC -- ('BAR' may be anything)
>
> MYSQL:
>
> SELECT columns
> FROM tablename
> ORDER BY key ASC
> LIMIT n OFFSET skip
>
> ORACLE:
>
> SELECT * FROM (
> SELECT
> ROW_NUMBER() OVER (ORDER BY key ASC) AS rn,
> columns
> FROM tablename
> )
> WHERE rn > skip AND rn <= (n+skip)
>
> (I think the oracle example can be simplified though.)
>
> And of course, there are many other platforms as well.
>
You are right, native pagination support will be much more complex to
implement than limit support (your examples point out the problem).
But isn't it in most cases a simple sql-string split to integrate the
paging query string?
> [ I got this info from
> http://troels.arvin.dk/db/rdbms/#select-limit-offset ]
>
Excellent link!
> And to complicate it even more, if the platform will not be supported by
> OJB, I think it would be nice if OJB falls back on it's current
> implementation. But the buildup of the resultsets is of course completey
> different using native pagination and the current OJB implementation,
> can they co-exist?
In the current implementation OJB simply use a RsIterator wrapper class
to "simulate" paging. Thus if native paging is supported by OJB it has
to use the default RsIterator and as fall back OJB can use the current
wrapper class.
Give me time to think more about it (seems it could be possible to
implement native paging without changing too many classes and internal
interfaces - I could be fatally wrong ;-)).
By the way, it seems that someone started with paging support some time
ago (see Platform class methods) but never implement it.
regards,
Armin
>
> Roger Janssen
> iBanx
>
> -----Original Message-----
> From: Armin Waibel [mailto:arminw@apache.org]
> Sent: zaterdag 3 februari 2007 3:05
> To: OJB Users List
> Subject: Re: Performance Issue on lookup on a table
>
> Hi Eric,
>
> Ferrer, Eric wrote:
>> Thanks for the reply, Can the OJB developers reply with their thoughts
>
>> on this.
>>
>> I would have figured the load by persistence object calls we make and
>> we do them when we only want 1 record a lot would do the same job as
>> the SQL select statement.
>>
>> For a while I thought maybe our driver was an issue.
>>
>> This may be major for us since we auto generate a lot of our OR
>> mapping using Velocity so as to not expose the data model. From what
>> you just told me we have to analyze table growth and convert our
>> Persistence Load Query to SQL calls.
>>
>
> First, I'm not a sql/database expert and only do marginal work on the
> query-engine of OJB - so please bear with me ;-)
>
> I think it would be possible to add support for l"imiting row numbers"
> in OJB 1.0.x branch. As far as I know to "limit the number of rows
> returned" is not SQL standard and depends on the DB vendor:
>
> DB2: select * from table fetch first 5 rows only MySQL and PostgreSQL:
> select * from table limit 5
> Oracle: select * from table rownum <= 5
> SQL Server: select top 5 * from table
>
> Here is my train of thoughts:
> I don't know if we can use method Query.set(Start/End)AtIndex(...) to
> add "row limit" support. Maybe we have to add a new method
> Query.setRowLimit(...).
> Before a Query object is executed in OJB method query.preprocess(broker)
> is called. With the PB instance we can access the Platform class were we
> have to locate the different "row limit" information.
> Now the "only thing" to do is to integrate the limit expression string
> in the query string. Think we can add the "limit expression string"
> while the query string is created in
> SqlSelectStatement#buildSqlString().
>
> What do you think?
>
> regards,
> Armin
>
>
>> -Eric
>>
>> -----Original Message-----
>> From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
>> Sent: Friday, February 02, 2007 1:29 AM
>> To: OJB Users List
>> Subject: RE: Performance Issue on lookup on a table
>>
>> Hi,
>>
>> We have the same problem. This is because OJB does not (yet?)
>> implement paging based on RDBMS native paging/limiting syntax. A
>> query, hitting the complete resultset is executed and cursors are used
>
>> to fetch the requested page. Large resultsets, will take a lot of
> time.
>> We have been experiencing more and more problems over the last few
>> years with this.
>>
>> I am wating for the OJB 1.1 release (For over 2 years now!) and hoping
>
>> that it will implemented this feature. Right now, I have to expose the
>
>> datamodel within my application code, bypassing our domain model and
>> OJB as OR mapper, and that really hurts.
>>
>> Greetings,
>>
>> Roger Janssen
>> iBanx
>>
>> -----Original Message-----
>> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
>> Sent: vrijdag 2 februari 2007 3:54
>> To: OJB Users List
>> Subject: RE: Performance Issue on lookup on a table
>>
>> Just to add it appears to work when passing a straight SQL query
>> versus using the persistable method.
>>
>> -Eric
>>
>> -----Original Message-----
>> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
>> Sent: Thursday, February 01, 2007 4:57 PM
>> To: OJB Users List
>> Subject: RE: Performance Issue on lookup on a table
>>
>> Sorry
>>
>> Accidently hit send.
>>
>> What I was saying the query generate looks like this
>>
>> SELECT TOP 1
>>
>> A0.SERIAL_NUMBER,A0.AD_ID,A0.LIC_NUM,A0.RECEIVED_DTIME,A0.LIC_ST,A0.LI
>> C_
>> TYPE
>>
>> FROM MY_LIC_TLB A0
>>
>> WHERE ((A0.LIC_NUM = '1236713' ) AND A0.LIC_ST = 'DD' ) AND
>> A0.LIC_TYPE = 'NORMAL'
>>
>> When I run it in the sql management tool it comes back in less than 20
>
>> seconds, however via ojb it varies between 5 and 7 minutes.
>>
>> The table has 3 gigs of data.
>>
>> Any ideas what can be the difference?
>>
>> We do lots of similar calls and this is the first one we ever
>> experienced this with.
>>
>> Thanks
>> -Eric
>>
>> -----Original Message-----
>> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
>> Sent: Thursday, February 01, 2007 4:52 PM
>> To: OJB Users List
>> Subject: Performance Issue on lookup on a table
>>
>>
>> I am experience an issue with an OJB query using a persistable object.
>> Basically its taking 7 minutes to return on the call getObjectByQuery.
>>
>> When I run the query
>>
>>
>> ---------------------------------------------------------------------
>> 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
>> **********************************************************************
>> **
>> *
>> The information contained in this communication is confidential and is
>
>> intended solely for the use of the individual or entity to whom it is
>
>> addressed.You should not copy, disclose or distribute this
>> communication without the authority of iBanx bv. iBanx bv is neither
>> liable for the proper and complete transmission of the information has
>
>> been maintained nor that the communication is free of viruses,
>> interceptions or interference.
>>
>> If you are not the intended recipient of this communication please
>> return the communication to the sender and delete and destroy all
>> copies.
>>
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> 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
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Performance Issue on lookup on a table
Posted by Armin Waibel <ar...@apache.org>.
Hi Eric and Roger,
checked in the initial version of native limit/offset support in CVS
(OJB_1_0_RELEASE branch). In class PaginationTest you can find example/tests
http://svn.apache.org/viewvc/db/ojb/branches/OJB_1_0_RELEASE/src/test/org/apache/ojb/broker/PaginationTest.java?view=markup
I run these tests again hsql, mysql, maxDB(get some failures, because of
driver bug) and oracleXE.
Native limit/offset support is implemented only for the most popular
databases - see ...broker.platforms.Platform classes source code. If you
need support for other database feel free to send patches ;-)
Please, if you can spend time give it a try.
regards,
Armin
Janssen, Roger wrote:
> Hi,
>
>> The limit support can't substitute the real "native based" pagination.
>> My thought was to combine current OJB-pagination with new limit support
> and
>> fetch-size - e.g set limit to 300, Query.setStartAtIndex(150),
>> Query.setEndAtIndex(300) to get results 150...300 and fetch-size=30 set
> to
>> web-page result number size. This isn't perfect, but I think this will
>> be much more effective on large result sets as current behavior
> (without limit).
>
> Suggested solution only results in improved performance iterating over
> the first pages. Nearing the end of the (large) resultset, performance
> issues will rear their ugly head again. But.... in real the world, this
> might work, since i do not believe people will iterate over manymanymany
> pages to get to the end of a large resultset. When searching, they would
> narrow their search criteria, for picklists, they would enter filter
> criteria like startswith to narrow down the resultset, so if fetching
> the first pages of a large resultset will have good performance, it
> think your solution might actually solve this problem in the real world
>
>
>> You are right, native pagination support will be much more complex to
>> implement than limit support (your examples point out the problem).
>> But isn't it in most cases a simple sql-string split to integrate
>> the paging query string?
>
> Yeah... It boils down to sql string manipulation, but before using OR
> mappers, I had to do this in my applications myself and I remember it
> being a pain to get it to work with all the supported databases.
>
>
> Roger Janssen
> iBanx
> *************************************************************************
> The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed.You should not copy, disclose or distribute this communication without the authority of iBanx bv. iBanx bv is neither liable for the proper and complete transmission of the information has been maintained nor that the communication is free of viruses, interceptions or interference.
>
> If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies.
>
>
>
> ---------------------------------------------------------------------
> 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: Performance Issue on lookup on a table
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
Hi,
>The limit support can't substitute the real "native based" pagination.
>My thought was to combine current OJB-pagination with new limit support
and
>fetch-size - e.g set limit to 300, Query.setStartAtIndex(150),
>Query.setEndAtIndex(300) to get results 150...300 and fetch-size=30 set
to
>web-page result number size. This isn't perfect, but I think this will
>be much more effective on large result sets as current behavior
(without limit).
Suggested solution only results in improved performance iterating over
the first pages. Nearing the end of the (large) resultset, performance
issues will rear their ugly head again. But.... in real the world, this
might work, since i do not believe people will iterate over manymanymany
pages to get to the end of a large resultset. When searching, they would
narrow their search criteria, for picklists, they would enter filter
criteria like startswith to narrow down the resultset, so if fetching
the first pages of a large resultset will have good performance, it
think your solution might actually solve this problem in the real world
>You are right, native pagination support will be much more complex to
>implement than limit support (your examples point out the problem).
>But isn't it in most cases a simple sql-string split to integrate
>the paging query string?
Yeah... It boils down to sql string manipulation, but before using OR
mappers, I had to do this in my applications myself and I remember it
being a pain to get it to work with all the supported databases.
Roger Janssen
iBanx
*************************************************************************
The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed.You should not copy, disclose or distribute this communication without the authority of iBanx bv. iBanx bv is neither liable for the proper and complete transmission of the information has been maintained nor that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies.
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Performance Issue on lookup on a table
Posted by Armin Waibel <ar...@apache.org>.
Hi Roger,
Janssen, Roger wrote:
> Hi,
>
> Hearing that the pagination might be implemented using native SQL
> limiting syntax puts a smile on my face. It might be a bit more complex
> though then suggested by Armin.
All beginnings are difficult ;-)
The limit support can't substitute the real "native based" pagination.
My thought was to combine current OJB-pagination with new limit support
and fetch-size - e.g set limit to 300, Query.setStartAtIndex(150),
Query.setEndAtIndex(300) to get results 150...300 and fetch-size=30 set
to web-page result number size. This isn't perfect, but I think this
will be much more effective on large result sets as current behavior
(without limit).
> We do not only want to limit the amount
> of records, but also want to be able to define an offset, aka request a
> specific page. Since every RDBMS has its own logic, it is not
> straightforward. A re-write of the quey might even be necessary, instead
> of just adding a limit-clause:
>
> MSSQL:
>
> SELECT * FROM (
> SELECT TOP n * FROM (
> SELECT TOP z columns -- (z=n+skip)
> FROM tablename
> ORDER BY key ASC
> ) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
> ) AS BAR ORDER BY key ASC -- ('BAR' may be anything)
>
> MYSQL:
>
> SELECT columns
> FROM tablename
> ORDER BY key ASC
> LIMIT n OFFSET skip
>
> ORACLE:
>
> SELECT * FROM (
> SELECT
> ROW_NUMBER() OVER (ORDER BY key ASC) AS rn,
> columns
> FROM tablename
> )
> WHERE rn > skip AND rn <= (n+skip)
>
> (I think the oracle example can be simplified though.)
>
> And of course, there are many other platforms as well.
>
You are right, native pagination support will be much more complex to
implement than limit support (your examples point out the problem).
But isn't it in most cases a simple sql-string split to integrate the
paging query string?
> [ I got this info from
> http://troels.arvin.dk/db/rdbms/#select-limit-offset ]
>
Excellent link!
> And to complicate it even more, if the platform will not be supported by
> OJB, I think it would be nice if OJB falls back on it's current
> implementation. But the buildup of the resultsets is of course completey
> different using native pagination and the current OJB implementation,
> can they co-exist?
In the current implementation OJB simply use a RsIterator wrapper class
to "simulate" paging. Thus if native paging is supported by OJB it has
to use the default RsIterator and as fall back OJB can use the current
wrapper class.
Give me time to think more about it (seems it could be possible to
implement native paging without changing too many classes and internal
interfaces - I could be fatally wrong ;-)).
By the way, it seems that someone started with paging support some time
ago (see Platform class methods) but never implement it.
regards,
Armin
>
> Roger Janssen
> iBanx
>
> -----Original Message-----
> From: Armin Waibel [mailto:arminw@apache.org]
> Sent: zaterdag 3 februari 2007 3:05
> To: OJB Users List
> Subject: Re: Performance Issue on lookup on a table
>
> Hi Eric,
>
> Ferrer, Eric wrote:
>> Thanks for the reply, Can the OJB developers reply with their thoughts
>
>> on this.
>>
>> I would have figured the load by persistence object calls we make and
>> we do them when we only want 1 record a lot would do the same job as
>> the SQL select statement.
>>
>> For a while I thought maybe our driver was an issue.
>>
>> This may be major for us since we auto generate a lot of our OR
>> mapping using Velocity so as to not expose the data model. From what
>> you just told me we have to analyze table growth and convert our
>> Persistence Load Query to SQL calls.
>>
>
> First, I'm not a sql/database expert and only do marginal work on the
> query-engine of OJB - so please bear with me ;-)
>
> I think it would be possible to add support for l"imiting row numbers"
> in OJB 1.0.x branch. As far as I know to "limit the number of rows
> returned" is not SQL standard and depends on the DB vendor:
>
> DB2: select * from table fetch first 5 rows only MySQL and PostgreSQL:
> select * from table limit 5
> Oracle: select * from table rownum <= 5
> SQL Server: select top 5 * from table
>
> Here is my train of thoughts:
> I don't know if we can use method Query.set(Start/End)AtIndex(...) to
> add "row limit" support. Maybe we have to add a new method
> Query.setRowLimit(...).
> Before a Query object is executed in OJB method query.preprocess(broker)
> is called. With the PB instance we can access the Platform class were we
> have to locate the different "row limit" information.
> Now the "only thing" to do is to integrate the limit expression string
> in the query string. Think we can add the "limit expression string"
> while the query string is created in
> SqlSelectStatement#buildSqlString().
>
> What do you think?
>
> regards,
> Armin
>
>
>> -Eric
>>
>> -----Original Message-----
>> From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
>> Sent: Friday, February 02, 2007 1:29 AM
>> To: OJB Users List
>> Subject: RE: Performance Issue on lookup on a table
>>
>> Hi,
>>
>> We have the same problem. This is because OJB does not (yet?)
>> implement paging based on RDBMS native paging/limiting syntax. A
>> query, hitting the complete resultset is executed and cursors are used
>
>> to fetch the requested page. Large resultsets, will take a lot of
> time.
>> We have been experiencing more and more problems over the last few
>> years with this.
>>
>> I am wating for the OJB 1.1 release (For over 2 years now!) and hoping
>
>> that it will implemented this feature. Right now, I have to expose the
>
>> datamodel within my application code, bypassing our domain model and
>> OJB as OR mapper, and that really hurts.
>>
>> Greetings,
>>
>> Roger Janssen
>> iBanx
>>
>> -----Original Message-----
>> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
>> Sent: vrijdag 2 februari 2007 3:54
>> To: OJB Users List
>> Subject: RE: Performance Issue on lookup on a table
>>
>> Just to add it appears to work when passing a straight SQL query
>> versus using the persistable method.
>>
>> -Eric
>>
>> -----Original Message-----
>> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
>> Sent: Thursday, February 01, 2007 4:57 PM
>> To: OJB Users List
>> Subject: RE: Performance Issue on lookup on a table
>>
>> Sorry
>>
>> Accidently hit send.
>>
>> What I was saying the query generate looks like this
>>
>> SELECT TOP 1
>>
>> A0.SERIAL_NUMBER,A0.AD_ID,A0.LIC_NUM,A0.RECEIVED_DTIME,A0.LIC_ST,A0.LI
>> C_
>> TYPE
>>
>> FROM MY_LIC_TLB A0
>>
>> WHERE ((A0.LIC_NUM = '1236713' ) AND A0.LIC_ST = 'DD' ) AND
>> A0.LIC_TYPE = 'NORMAL'
>>
>> When I run it in the sql management tool it comes back in less than 20
>
>> seconds, however via ojb it varies between 5 and 7 minutes.
>>
>> The table has 3 gigs of data.
>>
>> Any ideas what can be the difference?
>>
>> We do lots of similar calls and this is the first one we ever
>> experienced this with.
>>
>> Thanks
>> -Eric
>>
>> -----Original Message-----
>> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
>> Sent: Thursday, February 01, 2007 4:52 PM
>> To: OJB Users List
>> Subject: Performance Issue on lookup on a table
>>
>>
>> I am experience an issue with an OJB query using a persistable object.
>> Basically its taking 7 minutes to return on the call getObjectByQuery.
>>
>> When I run the query
>>
>>
>> ---------------------------------------------------------------------
>> 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
>> **********************************************************************
>> **
>> *
>> The information contained in this communication is confidential and is
>
>> intended solely for the use of the individual or entity to whom it is
>
>> addressed.You should not copy, disclose or distribute this
>> communication without the authority of iBanx bv. iBanx bv is neither
>> liable for the proper and complete transmission of the information has
>
>> been maintained nor that the communication is free of viruses,
>> interceptions or interference.
>>
>> If you are not the intended recipient of this communication please
>> return the communication to the sender and delete and destroy all
>> copies.
>>
>>
>>
>>
>>
>> ---------------------------------------------------------------------
>> 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: Performance Issue on lookup on a table
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
Hi,
Hearing that the pagination might be implemented using native SQL
limiting syntax puts a smile on my face. It might be a bit more complex
though then suggested by Armin. We do not only want to limit the amount
of records, but also want to be able to define an offset, aka request a
specific page. Since every RDBMS has its own logic, it is not
straightforward. A re-write of the quey might even be necessary, instead
of just adding a limit-clause:
MSSQL:
SELECT * FROM (
SELECT TOP n * FROM (
SELECT TOP z columns -- (z=n+skip)
FROM tablename
ORDER BY key ASC
) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
) AS BAR ORDER BY key ASC -- ('BAR' may be anything)
MYSQL:
SELECT columns
FROM tablename
ORDER BY key ASC
LIMIT n OFFSET skip
ORACLE:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY key ASC) AS rn,
columns
FROM tablename
)
WHERE rn > skip AND rn <= (n+skip)
(I think the oracle example can be simplified though.)
And of course, there are many other platforms as well.
[ I got this info from
http://troels.arvin.dk/db/rdbms/#select-limit-offset ]
And to complicate it even more, if the platform will not be supported by
OJB, I think it would be nice if OJB falls back on it's current
implementation. But the buildup of the resultsets is of course completey
different using native pagination and the current OJB implementation,
can they co-exist?
Roger Janssen
iBanx
-----Original Message-----
From: Armin Waibel [mailto:arminw@apache.org]
Sent: zaterdag 3 februari 2007 3:05
To: OJB Users List
Subject: Re: Performance Issue on lookup on a table
Hi Eric,
Ferrer, Eric wrote:
> Thanks for the reply, Can the OJB developers reply with their thoughts
> on this.
>
> I would have figured the load by persistence object calls we make and
> we do them when we only want 1 record a lot would do the same job as
> the SQL select statement.
>
> For a while I thought maybe our driver was an issue.
>
> This may be major for us since we auto generate a lot of our OR
> mapping using Velocity so as to not expose the data model. From what
> you just told me we have to analyze table growth and convert our
> Persistence Load Query to SQL calls.
>
First, I'm not a sql/database expert and only do marginal work on the
query-engine of OJB - so please bear with me ;-)
I think it would be possible to add support for l"imiting row numbers"
in OJB 1.0.x branch. As far as I know to "limit the number of rows
returned" is not SQL standard and depends on the DB vendor:
DB2: select * from table fetch first 5 rows only MySQL and PostgreSQL:
select * from table limit 5
Oracle: select * from table rownum <= 5
SQL Server: select top 5 * from table
Here is my train of thoughts:
I don't know if we can use method Query.set(Start/End)AtIndex(...) to
add "row limit" support. Maybe we have to add a new method
Query.setRowLimit(...).
Before a Query object is executed in OJB method query.preprocess(broker)
is called. With the PB instance we can access the Platform class were we
have to locate the different "row limit" information.
Now the "only thing" to do is to integrate the limit expression string
in the query string. Think we can add the "limit expression string"
while the query string is created in
SqlSelectStatement#buildSqlString().
What do you think?
regards,
Armin
> -Eric
>
> -----Original Message-----
> From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
> Sent: Friday, February 02, 2007 1:29 AM
> To: OJB Users List
> Subject: RE: Performance Issue on lookup on a table
>
> Hi,
>
> We have the same problem. This is because OJB does not (yet?)
> implement paging based on RDBMS native paging/limiting syntax. A
> query, hitting the complete resultset is executed and cursors are used
> to fetch the requested page. Large resultsets, will take a lot of
time.
>
> We have been experiencing more and more problems over the last few
> years with this.
>
> I am wating for the OJB 1.1 release (For over 2 years now!) and hoping
> that it will implemented this feature. Right now, I have to expose the
> datamodel within my application code, bypassing our domain model and
> OJB as OR mapper, and that really hurts.
>
> Greetings,
>
> Roger Janssen
> iBanx
>
> -----Original Message-----
> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
> Sent: vrijdag 2 februari 2007 3:54
> To: OJB Users List
> Subject: RE: Performance Issue on lookup on a table
>
> Just to add it appears to work when passing a straight SQL query
> versus using the persistable method.
>
> -Eric
>
> -----Original Message-----
> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
> Sent: Thursday, February 01, 2007 4:57 PM
> To: OJB Users List
> Subject: RE: Performance Issue on lookup on a table
>
> Sorry
>
> Accidently hit send.
>
> What I was saying the query generate looks like this
>
> SELECT TOP 1
>
> A0.SERIAL_NUMBER,A0.AD_ID,A0.LIC_NUM,A0.RECEIVED_DTIME,A0.LIC_ST,A0.LI
> C_
> TYPE
>
> FROM MY_LIC_TLB A0
>
> WHERE ((A0.LIC_NUM = '1236713' ) AND A0.LIC_ST = 'DD' ) AND
> A0.LIC_TYPE = 'NORMAL'
>
> When I run it in the sql management tool it comes back in less than 20
> seconds, however via ojb it varies between 5 and 7 minutes.
>
> The table has 3 gigs of data.
>
> Any ideas what can be the difference?
>
> We do lots of similar calls and this is the first one we ever
> experienced this with.
>
> Thanks
> -Eric
>
> -----Original Message-----
> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
> Sent: Thursday, February 01, 2007 4:52 PM
> To: OJB Users List
> Subject: Performance Issue on lookup on a table
>
>
> I am experience an issue with an OJB query using a persistable object.
> Basically its taking 7 minutes to return on the call getObjectByQuery.
>
> When I run the query
>
>
> ---------------------------------------------------------------------
> 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
> **********************************************************************
> **
> *
> The information contained in this communication is confidential and is
> intended solely for the use of the individual or entity to whom it is
> addressed.You should not copy, disclose or distribute this
> communication without the authority of iBanx bv. iBanx bv is neither
> liable for the proper and complete transmission of the information has
> been maintained nor that the communication is free of viruses,
> interceptions or interference.
>
> If you are not the intended recipient of this communication please
> return the communication to the sender and delete and destroy all
> copies.
>
>
>
>
>
> ---------------------------------------------------------------------
> 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: Performance Issue on lookup on a table
Posted by Armin Waibel <ar...@apache.org>.
Hi Eric,
Ferrer, Eric wrote:
> Thanks for the reply, Can the OJB developers reply with their thoughts
> on this.
>
> I would have figured the load by persistence object calls we make and we
> do them when we only want 1 record a lot would do the same job as the
> SQL select statement.
>
> For a while I thought maybe our driver was an issue.
>
> This may be major for us since we auto generate a lot of our OR mapping
> using Velocity so as to not expose the data model. From what you just
> told me we have to analyze table growth and convert our Persistence Load
> Query to SQL calls.
>
First, I'm not a sql/database expert and only do marginal work on the
query-engine of OJB - so please bear with me ;-)
I think it would be possible to add support for l"imiting row numbers"
in OJB 1.0.x branch. As far as I know to "limit the number of rows
returned" is not SQL standard and depends on the DB vendor:
DB2: select * from table fetch first 5 rows only
MySQL and PostgreSQL: select * from table limit 5
Oracle: select * from table rownum <= 5
SQL Server: select top 5 * from table
Here is my train of thoughts:
I don't know if we can use method Query.set(Start/End)AtIndex(...) to
add "row limit" support. Maybe we have to add a new method
Query.setRowLimit(...).
Before a Query object is executed in OJB method query.preprocess(broker)
is called. With the PB instance we can access the Platform class were we
have to locate the different "row limit" information.
Now the "only thing" to do is to integrate the limit expression string
in the query string. Think we can add the "limit expression string"
while the query string is created in SqlSelectStatement#buildSqlString().
What do you think?
regards,
Armin
> -Eric
>
> -----Original Message-----
> From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
> Sent: Friday, February 02, 2007 1:29 AM
> To: OJB Users List
> Subject: RE: Performance Issue on lookup on a table
>
> Hi,
>
> We have the same problem. This is because OJB does not (yet?) implement
> paging based on RDBMS native paging/limiting syntax. A query, hitting
> the complete resultset is executed and cursors are used to fetch the
> requested page. Large resultsets, will take a lot of time.
>
> We have been experiencing more and more problems over the last few years
> with this.
>
> I am wating for the OJB 1.1 release (For over 2 years now!) and hoping
> that it will implemented this feature. Right now, I have to expose the
> datamodel within my application code, bypassing our domain model and OJB
> as OR mapper, and that really hurts.
>
> Greetings,
>
> Roger Janssen
> iBanx
>
> -----Original Message-----
> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
> Sent: vrijdag 2 februari 2007 3:54
> To: OJB Users List
> Subject: RE: Performance Issue on lookup on a table
>
> Just to add it appears to work when passing a straight SQL query versus
> using the persistable method.
>
> -Eric
>
> -----Original Message-----
> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
> Sent: Thursday, February 01, 2007 4:57 PM
> To: OJB Users List
> Subject: RE: Performance Issue on lookup on a table
>
> Sorry
>
> Accidently hit send.
>
> What I was saying the query generate looks like this
>
> SELECT TOP 1
>
> A0.SERIAL_NUMBER,A0.AD_ID,A0.LIC_NUM,A0.RECEIVED_DTIME,A0.LIC_ST,A0.LIC_
> TYPE
>
> FROM MY_LIC_TLB A0
>
> WHERE ((A0.LIC_NUM = '1236713' ) AND A0.LIC_ST = 'DD' ) AND
> A0.LIC_TYPE = 'NORMAL'
>
> When I run it in the sql management tool it comes back in less than 20
> seconds, however via ojb it varies between 5 and 7 minutes.
>
> The table has 3 gigs of data.
>
> Any ideas what can be the difference?
>
> We do lots of similar calls and this is the first one we ever
> experienced this with.
>
> Thanks
> -Eric
>
> -----Original Message-----
> From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
> Sent: Thursday, February 01, 2007 4:52 PM
> To: OJB Users List
> Subject: Performance Issue on lookup on a table
>
>
> I am experience an issue with an OJB query using a persistable object.
> Basically its taking 7 minutes to return on the call getObjectByQuery.
>
> When I run the query
>
>
> ---------------------------------------------------------------------
> 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
> ************************************************************************
> *
> The information contained in this communication is confidential and is
> intended solely for the use of the individual or entity to whom it is
> addressed.You should not copy, disclose or distribute this communication
> without the authority of iBanx bv. iBanx bv is neither liable for the
> proper and complete transmission of the information has been maintained
> nor that the communication is free of viruses, interceptions or
> interference.
>
> If you are not the intended recipient of this communication please
> return the communication to the sender and delete and destroy all
> copies.
>
>
>
>
>
> ---------------------------------------------------------------------
> 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: Performance Issue on lookup on a table
Posted by "Ferrer, Eric" <er...@transcore.com>.
Thanks for the reply, Can the OJB developers reply with their thoughts
on this.
I would have figured the load by persistence object calls we make and we
do them when we only want 1 record a lot would do the same job as the
SQL select statement.
For a while I thought maybe our driver was an issue.
This may be major for us since we auto generate a lot of our OR mapping
using Velocity so as to not expose the data model. From what you just
told me we have to analyze table growth and convert our Persistence Load
Query to SQL calls.
-Eric
-----Original Message-----
From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
Sent: Friday, February 02, 2007 1:29 AM
To: OJB Users List
Subject: RE: Performance Issue on lookup on a table
Hi,
We have the same problem. This is because OJB does not (yet?) implement
paging based on RDBMS native paging/limiting syntax. A query, hitting
the complete resultset is executed and cursors are used to fetch the
requested page. Large resultsets, will take a lot of time.
We have been experiencing more and more problems over the last few years
with this.
I am wating for the OJB 1.1 release (For over 2 years now!) and hoping
that it will implemented this feature. Right now, I have to expose the
datamodel within my application code, bypassing our domain model and OJB
as OR mapper, and that really hurts.
Greetings,
Roger Janssen
iBanx
-----Original Message-----
From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
Sent: vrijdag 2 februari 2007 3:54
To: OJB Users List
Subject: RE: Performance Issue on lookup on a table
Just to add it appears to work when passing a straight SQL query versus
using the persistable method.
-Eric
-----Original Message-----
From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
Sent: Thursday, February 01, 2007 4:57 PM
To: OJB Users List
Subject: RE: Performance Issue on lookup on a table
Sorry
Accidently hit send.
What I was saying the query generate looks like this
SELECT TOP 1
A0.SERIAL_NUMBER,A0.AD_ID,A0.LIC_NUM,A0.RECEIVED_DTIME,A0.LIC_ST,A0.LIC_
TYPE
FROM MY_LIC_TLB A0
WHERE ((A0.LIC_NUM = '1236713' ) AND A0.LIC_ST = 'DD' ) AND
A0.LIC_TYPE = 'NORMAL'
When I run it in the sql management tool it comes back in less than 20
seconds, however via ojb it varies between 5 and 7 minutes.
The table has 3 gigs of data.
Any ideas what can be the difference?
We do lots of similar calls and this is the first one we ever
experienced this with.
Thanks
-Eric
-----Original Message-----
From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
Sent: Thursday, February 01, 2007 4:52 PM
To: OJB Users List
Subject: Performance Issue on lookup on a table
I am experience an issue with an OJB query using a persistable object.
Basically its taking 7 minutes to return on the call getObjectByQuery.
When I run the query
---------------------------------------------------------------------
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
************************************************************************
*
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed.You should not copy, disclose or distribute this communication
without the authority of iBanx bv. iBanx bv is neither liable for the
proper and complete transmission of the information has been maintained
nor that the communication is free of viruses, interceptions or
interference.
If you are not the intended recipient of this communication please
return the communication to the sender and delete and destroy all
copies.
---------------------------------------------------------------------
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: Performance Issue on lookup on a table
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
Hi,
We have the same problem. This is because OJB does not (yet?) implement
paging based on RDBMS native paging/limiting syntax. A query, hitting
the complete resultset is executed and cursors are used to fetch the
requested page. Large resultsets, will take a lot of time.
We have been experiencing more and more problems over the last few years
with this.
I am wating for the OJB 1.1 release (For over 2 years now!) and hoping
that it will implemented this feature. Right now, I have to expose the
datamodel within my application code, bypassing our domain model and OJB
as OR mapper, and that really hurts.
Greetings,
Roger Janssen
iBanx
-----Original Message-----
From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
Sent: vrijdag 2 februari 2007 3:54
To: OJB Users List
Subject: RE: Performance Issue on lookup on a table
Just to add it appears to work when passing a straight SQL query versus
using the persistable method.
-Eric
-----Original Message-----
From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
Sent: Thursday, February 01, 2007 4:57 PM
To: OJB Users List
Subject: RE: Performance Issue on lookup on a table
Sorry
Accidently hit send.
What I was saying the query generate looks like this
SELECT TOP 1
A0.SERIAL_NUMBER,A0.AD_ID,A0.LIC_NUM,A0.RECEIVED_DTIME,A0.LIC_ST,A0.LIC_
TYPE
FROM MY_LIC_TLB A0
WHERE ((A0.LIC_NUM = '1236713' ) AND A0.LIC_ST = 'DD' ) AND
A0.LIC_TYPE = 'NORMAL'
When I run it in the sql management tool it comes back in less than 20
seconds, however via ojb it varies between 5 and 7 minutes.
The table has 3 gigs of data.
Any ideas what can be the difference?
We do lots of similar calls and this is the first one we ever
experienced this with.
Thanks
-Eric
-----Original Message-----
From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
Sent: Thursday, February 01, 2007 4:52 PM
To: OJB Users List
Subject: Performance Issue on lookup on a table
I am experience an issue with an OJB query using a persistable object.
Basically its taking 7 minutes to return on the call getObjectByQuery.
When I run the query
---------------------------------------------------------------------
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
*************************************************************************
The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed.You should not copy, disclose or distribute this communication without the authority of iBanx bv. iBanx bv is neither liable for the proper and complete transmission of the information has been maintained nor that the communication is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies.
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Performance Issue on lookup on a table
Posted by "Ferrer, Eric" <er...@transcore.com>.
Just to add it appears to work when passing a straight SQL query versus
using the persistable method.
-Eric
-----Original Message-----
From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
Sent: Thursday, February 01, 2007 4:57 PM
To: OJB Users List
Subject: RE: Performance Issue on lookup on a table
Sorry
Accidently hit send.
What I was saying the query generate looks like this
SELECT TOP 1
A0.SERIAL_NUMBER,A0.AD_ID,A0.LIC_NUM,A0.RECEIVED_DTIME,A0.LIC_ST,A0.LIC_
TYPE
FROM MY_LIC_TLB A0
WHERE ((A0.LIC_NUM = '1236713' ) AND A0.LIC_ST = 'DD' ) AND
A0.LIC_TYPE = 'NORMAL'
When I run it in the sql management tool it comes back in less than 20
seconds, however via ojb it varies between 5 and 7 minutes.
The table has 3 gigs of data.
Any ideas what can be the difference?
We do lots of similar calls and this is the first one we ever
experienced this with.
Thanks
-Eric
-----Original Message-----
From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
Sent: Thursday, February 01, 2007 4:52 PM
To: OJB Users List
Subject: Performance Issue on lookup on a table
I am experience an issue with an OJB query using a persistable object.
Basically its taking 7 minutes to return on the call getObjectByQuery.
When I run the query
---------------------------------------------------------------------
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: Performance Issue on lookup on a table
Posted by "Ferrer, Eric" <er...@transcore.com>.
Sorry
Accidently hit send.
What I was saying the query generate looks like this
SELECT TOP 1
A0.SERIAL_NUMBER,A0.AD_ID,A0.LIC_NUM,A0.RECEIVED_DTIME,A0.LIC_ST,A0.LIC_
TYPE
FROM MY_LIC_TLB A0
WHERE ((A0.LIC_NUM = '1236713' ) AND A0.LIC_ST = 'DD' ) AND
A0.LIC_TYPE = 'NORMAL'
When I run it in the sql management tool it comes back in less than 20
seconds, however via ojb it varies between 5 and 7 minutes.
The table has 3 gigs of data.
Any ideas what can be the difference?
We do lots of similar calls and this is the first one we ever
experienced this with.
Thanks
-Eric
-----Original Message-----
From: Ferrer, Eric [mailto:eric.ferrer@transcore.com]
Sent: Thursday, February 01, 2007 4:52 PM
To: OJB Users List
Subject: Performance Issue on lookup on a table
I am experience an issue with an OJB query using a persistable object.
Basically its taking 7 minutes to return on the call getObjectByQuery.
When I run the query
---------------------------------------------------------------------
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