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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
