Re: Performance Issue on lookup on a table

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:[email protected]] 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:[email protected]] 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:[email protected]] 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:[email protected]] 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:[email protected]] 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:[email protected]] 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: [email protected] For
>>> additional commands, e-mail: [email protected]
>>> 
>>> 
>>> ---------------------------------------------------------------------
>>>  To unsubscribe, e-mail: [email protected] For
>>> additional commands, e-mail: [email protected]
>>> 
>>> 
>>> ---------------------------------------------------------------------
>>>  To unsubscribe, e-mail: [email protected] For
>>> additional commands, e-mail: [email protected] 
>>> **********************************************************************
>>>  ** * 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: [email protected] For
>>> additional commands, e-mail: [email protected]
>>> 
>>> 
>>> ---------------------------------------------------------------------
>>>  To unsubscribe, e-mail: [email protected] For
>>> additional commands, e-mail: [email protected]
>>> 
>>> 
>> ---------------------------------------------------------------------
>>  To unsubscribe, e-mail: [email protected] For
>> additional commands, e-mail: [email protected]
>> 
>> 
>> 
>> ---------------------------------------------------------------------
>>  To unsubscribe, e-mail: [email protected] For
>> additional commands, e-mail: [email protected]
>> 
>> 
> 
> ---------------------------------------------------------------------
>  To unsubscribe, e-mail: [email protected] For
> additional commands, e-mail: [email protected]
> 
> 
> 
> 
> ---------------------------------------------------------------------
>  To unsubscribe, e-mail: [email protected] For
> additional commands, e-mail: [email protected]
> 
>