Implemetation of pagging to improve performance

Posted on 2004-09-23
Last Modified: 2008-01-09

We are having screen with three frames.
First frame is having 60 columns with first column hyperlinked.
Second and thrird frame will be hyperlinked based on the first frame.

The query on the first frame is  dynamic something like this

SELECT A,B,Max(C),D,E .. from table1,table2 where join between table1 and table2 and other
conditions group by field names other then max()
order by...
some fields.

The problem is the query is returning more than 10000 records and to display 10000 records on same
page is not possible.. I am thinking of paging but because of use of  max(), group by and order by clause
i cannot take the count.
To scroll thru resultset is also time consuming..

Can anybody suggest me how do  i implement pagging concetp here.
I would be more than happy...This would improve the performance of my tool a lot....

Thanks in advance.

Question by:paku
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
LVL 86

Expert Comment

ID: 12136279
LVL 92

Expert Comment

ID: 12138652
> I am thinking of paging but because of use of  max(), group by and order by clause
> i cannot take the count.

You can still use paging, but read the entire result set into memory and only display say 100 records at a time.
Additionally you could set it up to display the first page while the rest is still loading so the user does not need to wait for all the data to load.

Author Comment

ID: 12140315
Hi CEHJ and objects,

I am currently having tag for printing the result set.

CEHJ  i have download the paging.war file from specified link but i am not getting exactly how to pass the resultset to that war file.
Can you guide me how to use that tag lib.

objects Is it adviseble to store entire result set into memory.. how do i do this.
"Additionally you could set it up to display the first page while the rest is still loading so the user does not need to wait for all the data to load"

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 12140339
How likely is it that the user will want to scroll through every result returned?  Normally when a query returns hundreds or thousands of entries the user should refine their search.  In which case if you cache the entire resultset on the java side, you have effectively wasted server resources processing the query, and storing it.  For these sorts of things, it is normally better to page at the database level, using database specific features (for example, oracle has rownum pseudo-columns for this kind of thing) to return a new range for each screen displayed.


Author Comment

ID: 12140435
Hi  grim_toaster,

The rownum concept cannot be used out here because of order by and group by clause.
Yes you are right there is one option to tell user to refine here search.
But there is requirement that the want the pagging so i am looking for that.


Accepted Solution

grim_toaster earned 250 total points
ID: 12140453
--> The rownum concept cannot be used out here because of order by and group by clause.
Not true, it would just look a little bit complicated for a simple example (I believe you could go into analytics to get it to look a little tidier, but no real benefits):

    SELECT t1.*, ROWNUM AS row_num FROM (
        SELECT tablespace_name, COUNT(tablespace_name)
        FROM   user_tables
        GROUP BY tablespace_name
        ORDER BY COUNT(tablespace_name)
    ) t1
) WHERE row_num BETWEEN 2 AND 3

And obviously change the between range for each page (and have larger ranges, but I just needed something to test with here...).
LVL 30

Expert Comment

by:Mayank S
ID: 12140708
Try using a CachedRowSet for better performance while scrolling. I would incorporate that along with the ROWNUM approach to get better results.
LVL 30

Expert Comment

by:Mayank S
ID: 12140716
>> The rownum concept cannot be used out here because of order by and group by clause.

As it has been shown, once you have the query fixed, the ROWNUM would be taken on that query (you would make another outer query for that).
LVL 30

Expert Comment

by:Mayank S
ID: 12450907
Venabili, when you'd posted your clean-up comment, I'd tried replying to it by saying that Grim's comment should be accepted as answer. But for some reason, either EE went slow or my Internet connection went slow and the request kept getting timed out. After that, I forgot to come back to the page and post it again. I think he should get the points here.

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ejb entity bean example 2 31
Is there a simpler dropbox system? 10 44
Bot application - advice 3 58
Java Eclipse Loop 3 29
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question