• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Implemetation of pagging to improve performance

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
paku
Asked:
paku
  • 3
  • 2
  • 2
  • +2
1 Solution
 
CEHJCommented:
0
 
objectsCommented:
> 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
 
pakuAuthor Commented:
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
Independent Software Vendors: 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!

 
grim_toasterCommented:
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
 
pakuAuthor Commented:
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
 
grim_toasterCommented:
--> 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
 
Mayank SAssociate Director - Product EngineeringCommented:
Try using a CachedRowSet for better performance while scrolling. I would incorporate that along with the ROWNUM approach to get better results.
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> 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
 
Mayank SAssociate Director - Product EngineeringCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now