Solved

Implemetation of pagging to improve performance

Posted on 2004-09-23
12
307 Views
Last Modified: 2008-01-09
Hi


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.

0
Comment
Question by:paku
  • 3
  • 2
  • 2
  • +2
12 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 12136279
0
 
LVL 92

Expert Comment

by:objects
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.
0
 

Author Comment

by:paku
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"


0
 
LVL 7

Expert Comment

by:grim_toaster
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.

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:paku
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.

0
 
LVL 7

Accepted Solution

by:
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 * FROM (
    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...).
0
 
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.
0
 
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).
0
 
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.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now