Optimize pagging for DB2

djuzoran · **Joined:** Mon Sep 19, 2005 3:08 am **Posts:** 19

I tested two queries for DB2 paging.
Table has 135000 records.

First one (hibernate default) - page one:

Code:

select * from (
select rownumber() over(order by this_.transm asc) as rownumber_, this_.* from online.transm this_ where (1=1) order by this_.transm asc ) as temp_ where rownumber_ <= 20

Execution time:3.79 sec

Second query page one:

Code:

select * from transm this_ where (1=1) order by this_.transmsg asc fetch first 20 rows only

Execution time:1.1 sec
...
Execution time is better for second query for: page <5 (on this table)
When there is more records, pageNo increases and it is better to use second query for first N pages (in java we do ResultSet.next and return only results for page we need).
I tested on some tables and I have execution time (first query 30 seconds and second 4 seconds)

Is there a way to hibernate support this approach (maybe some parameter/true, false/), and than developer can decide when to use first and when second query.

max · **Posted:** Mon Aug 14, 2006 3:51 am

the dialect controls the generation of the paging sql, but not sure if it gets enough information to decide which one to use.

so check out DB2Dialect for this.

djuzoran · **Joined:** Mon Sep 19, 2005 3:08 am **Posts:** 19

max wrote:

the dialect controls the generation of the paging sql, but not sure if it gets enough information to decide which one to use.

so check out DB2Dialect for this.

I checked it already, and there are not enough information. I can't just override this class.
Maybe this is question for hibernate developers. Should I post it on some other place?

max · **Posted:** Mon Aug 14, 2006 4:33 am

did you see the "Hibernate Team" tag under my name ? :)

If the dialect does not have enough information to control it then it is not possible today.

You can try and see where those dialect methods are being used and see if you can patch hibernate to work like this.

But for it to be applied you should show that it is a worthwile optimization on e.g. other databases.

btw. you could *maybe* tweak this out via Interceptor.onPrepareStatement.

djuzoran · **Joined:** Mon Sep 19, 2005 3:08 am **Posts:** 19

max wrote:

did you see the "Hibernate Team" tag under my name ? :)

If the dialect does not have enough information to control it then it is not possible today.

You can try and see where those dialect methods are being used and see if you can patch hibernate to work like this.

But for it to be applied you should show that it is a worthwile optimization on e.g. other databases.

btw. you could *maybe* tweak this out via Interceptor.onPrepareStatement.

I didn't saw that you are member of hibernate team :-), sorry.

Yes I know that is worthwile for other databases, but my firm only works with DB2 and websphere (IBM bussiness partner) and I'm developing spring-hibernate framework for it.
Is there any chance to add this in next hibernate release, or I must think some other solution. I do not like to use interceptor just for this, I must thik about it.

Anyway thanks.

Can you check my other question:
http://forum.hibernate.org/viewtopic.php?t=963284

max · **Posted:** Mon Aug 14, 2006 5:03 am

It is not in the plans no.

Put a request in jira if you want to push for it - it helps if you provide a patch that makes it possible.

djuzoran · **Joined:** Mon Sep 19, 2005 3:08 am **Posts:** 19

max wrote:

did you see the "Hibernate Team" tag under my name ? :)

If the dialect does not have enough information to control it then it is not possible today.

You can try and see where those dialect methods are being used and see if you can patch hibernate to work like this.

But for it to be applied you should show that it is a worthwile optimization on e.g. other databases.

btw. you could *maybe* tweak this out via Interceptor.onPrepareStatement.

I didn't saw that you are member of hibernate team :-), sorry.

Yes I know that is worthwile for other databases, but my firm only works with DB2 and websphere (IBM bussiness partner) and I'm developing spring-hibernate framework for it.
Is there any chance to add this in next hibernate release, or I must think some other solution. I do not like to use interceptor just for this, I must thik about it.

Anyway thanks.

Can you check my other question:
http://forum.hibernate.org/viewtopic.php?t=963284