December 16, 2008 at 2:16 am
I have a table with more than 200000 records and gets increasing. I am displaying these records in JSP report but the query takes lot of times when a report button on JSP page is clicked because number of records is high. It is an online intranet based application so when report is clicked whole system gets slow.
How can I solve this problem? I think paging is the better solution but how to use it ?
Database is SQL Server 2005.
Please help me.
Thanks in advance.
Ankur Bhardwaj
December 16, 2008 at 2:41 am
cant help much with jsp. however can u paste the table definition and the query you're using to fetch records. What all indexes do you have on this table?
December 16, 2008 at 2:42 am
That table is still relatively small, but showing 200000 records on a screen is of course useless. Is there a better way of decreasing the number of rows presented? Do you use indexes?
Also, if you want better help, take a look at the link in my signature.
Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2
December 16, 2008 at 5:51 am
Human beings do not look at 200000 rows worth of data... ever. So, you need to identify the data that is actually needed for the report, the last day's worth of data, an aggregation of the records, all the values for a given region... something that filters the data down to something human's deal with, usually less than 200 rows. Anything more than that, people are not actually evaluating the data, they're counting the number of rows and glancing around.
With filter criteria, then you can work on indexes, good queries, etc., to make the system hum.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 16, 2008 at 8:20 am
Grant Fritchey (12/16/2008)
Human beings do not look at 200000 rows worth of data... ever. So, you need to identify the data that is actually needed for the report, the last day's worth of data, an aggregation of the records, all the values for a given region... something that filters the data down to something human's deal with, usually less than 200 rows. Anything more than that, people are not actually evaluating the data, they're counting the number of rows and glancing around.With filter criteria, then you can work on indexes, good queries, etc., to make the system hum.
Ditto. This sounds like one of the old green bar reports that prints 500 pages daily and they tear off the last page with summary information and throw out 499 pages.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply