Paging Performance

tads · **Posted:** Mon Mar 13, 2006 12:55 pm

Hi all,

I´m using Hibernate 2.1.4 with Oracle 9i.
I have a table with 200000 rows that´s represented
by bean Card and a table with 200000 rows
represented by bean Carrier.

I´m using paging of Hibernate like this:

Code:

query += "select distinct c  from Card c"
     + " left join fetch c.cardStatus cs"
     + " inner join fetch c.cardType ct"
     + " left join fetch c.carrier carrier"
     + " where ct.idCardType = 1"
     + " order by c.pan";

tx = session.beginTransaction();

Query q = session.createQuery(query);

q.setMaxResults(maxResults); // maxResults i this case is only 10
q.setFirstResult(initial); // initial = 0

list = q.list();

But the performance of result of data is low yet.
I´m using proxy, lazy=true, but...

Somebody have any idea about this case?

thanks

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Check your indices. You'll need some on all joining columns, in Card, CardStatus, CardType, Carrier, and also on Card.pan (because of the order by).

tads · **Posted:** Tue Mar 14, 2006 7:30 am

Hi,

Unfortunetelly I need all these data. I´ve checked indexes.
Hibernate generate the following query on Oracle:

Code:

 select * from
 ( select distinct card0_.pan as pan0_, cardstatus1_.id_cartao_status as id_carta1_1_
   from cartao card0_, cartao_status cardstatus1_
   where card0_.id_cartao_status=cardstatus1_.id_cartao_status(+)
   and ((card0_.id_cartao_tipo=1 ))
   order by  card0_.pan )
 where rownum <= 10

The subquery in from clause on oracle is the problem, because
its reads all blocks in database and, finally, takes the 10 rows.

thanks

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Ouch, I had no ideal Oracle's support for pagination was so poor. I use SQLServer, and that has the rather handy "top" keyword (select top 10 x, y from table where ...).

You could try using scrollable result sets. Hibernate supports this using Query.scroll(). This will allow you to query for the entire result set, but jump forward to the correct place in the result set, get just the rows you need, then close it. It's a bit more awkward to use, but it should improve your performance. In the 3.1 ref docs, it's in section 10.4.1.6. "Scrollable iteration".

tads · **Posted:** Wed Mar 15, 2006 2:24 pm

thank u!!!!

I´m going to try this.

[]´s