Solved

Implemetation of pagging to improve performance

Posted on 2004-09-23
12
306 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
Comment Utility
0
 
LVL 92

Expert Comment

by:objects
Comment Utility
> 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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:paku
Comment Utility
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
Comment Utility
--> 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:mayankeagle
Comment Utility
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:mayankeagle
Comment Utility
>> 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:mayankeagle
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Java 1603 Error 2 42
strCount chalenge 3 50
Starting to learn JAVA, 7 47
Java array passed to SQL where clause 23 39
For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
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 …

772 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

12 Experts available now in Live!

Get 1:1 Help Now