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]
