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:[EMAIL PROTECTED] 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:[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.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:[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]
