Solved

Implemetation of pagging to improve performance

Posted on 2004-09-23
12
311 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
[X]
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
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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
 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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
tomcat startup error 5 167
Which non-HTML GUI front end to use with Java? 3 66
Problem to Alipay 10 94
Bitwise and to sum elements 2 52
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…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

732 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