|
|
You're in the
sql server reporting services
group:ice…
sql server reporting services:
the size/total record of the table is increasing, the display process becomes slower and slower. I managed to trace it and find out that in my case, 300,000 records consumes more than 2 minutes for the report manage to render it, while loading the data from database only take 10 seconds! Anybody knows
Is the result supposed to have 300,000 rows? If not, then be sure to use
query parameters to limit the data to the amount the report should have. Don't use filters. Consider drill through instead of drill down, etc. I.e. design it so you are not rendering 300,000 rows. The amount of rows the table has doesn't matter. I report against an 80 million row table. What matters is the number of records returned. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "NotCpiP" <NotCpiP@discussions.microsoft.com> wrote in message news:B85FCC8B-5198-4984-97B2-F1EE02DEC628@microsoft.com... > Just find out that for small size table, reporting service works fine. > When > the size/total record of the table is increasing, the display process > becomes > slower and slower. I managed to trace it and find out that in my case, > 300,000 records consumes more than 2 minutes for the report manage to > render > it, while loading the data from database only take 10 seconds! Anybody > knows > how to improve it?
Thanks for your reply. Yes, the returned dataset has one table with 300,000
rows inside, and this report will have several thousands of pages in total. I know all resource are limited, but I am just thinking, can we let the reporting service only process one page of report, instead of rendering all pages at onece (I guess thats the reason why it took so long)? Is there anything like dynamic rendering? Thank you so much. [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Is the result supposed to have 300,000 rows? If not, then be sure to use > query parameters to limit the data to the amount the report should have. > Don't use filters. Consider drill through instead of drill down, etc. I.e. > design it so you are not rendering 300,000 rows. The amount of rows the > table has doesn't matter. I report against an 80 million row table. What > matters is the number of records returned.
I'm thinking that a report that size does not need to be rendered with
fresh data on-demand. Can you run it as a subscription and cache it? "=?Utf-8?B?Tm90Q3BpUA==?=" <NotCpiP@discussions.microsoft.com> wrote in news:9F53987D-AAB7-42B6-B1D6-297638746375@microsoft.com: [quoted text, click to view] > Thanks for your reply. Yes, the returned dataset has one table with
> 300,000 rows inside, and this report will have several thousands of > pages in total. I know all resource are limited, but I am just > thinking, can we let the reporting service only process one page of > report, instead of rendering all pages at onece (I guess thats the > reason why it took so long)? Is there anything like dynamic rendering? > Thank you so much. > > > "Bruce L-C [MVP]" wrote: > >> Is the result supposed to have 300,000 rows? If not, then be sure to >> use query parameters to limit the data to the amount the report >> should have. Don't use filters. Consider drill through instead of >> drill down, etc. I.e. design it so you are not rendering 300,000 >> rows. The amount of rows the table has doesn't matter. I report >> against an 80 million row table. What matters is the number of >> records returned. > >
I find it hard to believe that any one user is digesting a thousand page
report. Sounds like you are returning the entire data set and then relying on the user to navigate to the data they need. Try using parameters to allow the users to specify the data they need. That will limit the data that is returned and prevent this one reprot form killing the server. [quoted text, click to view] "NotCpiP" wrote:
> Just find out that for small size table, reporting service works fine. When > the size/total record of the table is increasing, the display process becomes > slower and slower. I managed to trace it and find out that in my case, > 300,000 records consumes more than 2 minutes for the report manage to render > it, while loading the data from database only take 10 seconds! Anybody knows
OK. This may be the way. Thanks.
[quoted text, click to view] "Asher_N" wrote:
> I'm thinking that a report that size does not need to be rendered with > fresh data on-demand. Can you run it as a subscription and cache it? > > "=?Utf-8?B?Tm90Q3BpUA==?=" <NotCpiP@discussions.microsoft.com> wrote in > news:9F53987D-AAB7-42B6-B1D6-297638746375@microsoft.com: > > > Thanks for your reply. Yes, the returned dataset has one table with > > 300,000 rows inside, and this report will have several thousands of > > pages in total. I know all resource are limited, but I am just > > thinking, can we let the reporting service only process one page of > > report, instead of rendering all pages at onece (I guess thats the > > reason why it took so long)? Is there anything like dynamic rendering? > > Thank you so much. > > > > > > "Bruce L-C [MVP]" wrote: > > > >> Is the result supposed to have 300,000 rows? If not, then be sure to > >> use query parameters to limit the data to the amount the report > >> should have. Don't use filters. Consider drill through instead of > >> drill down, etc. I.e. design it so you are not rendering 300,000 > >> rows. The amount of rows the table has doesn't matter. I report > >> against an 80 million row table. What matters is the number of > >> records returned. > > > > >
Thanks.
[quoted text, click to view] "williamericnichols" wrote:
> I find it hard to believe that any one user is digesting a thousand page > report. Sounds like you are returning the entire data set and then relying > on the user to navigate to the data they need. Try using parameters to allow > the users to specify the data they need. That will limit the data that is > returned and prevent this one reprot form killing the server. > > "NotCpiP" wrote: > > > Just find out that for small size table, reporting service works fine. When > > the size/total record of the table is increasing, the display process becomes > > slower and slower. I managed to trace it and find out that in my case, > > 300,000 records consumes more than 2 minutes for the report manage to render > > it, while loading the data from database only take 10 seconds! Anybody knows
I also encounter the need to render very large reports, and take some
exception to the comments about 'you shouldn't need to do that'. Reporting Services should be able to work with and render very large datasets and reports if it is to be accepted as a professional tool. Rendering time should be proportional to output size, and not progressively worse as you work with larger result sets. Certainly, not everyone has this kind need. But for those who do, dismissive comments about how they should adapt their processing to fit the limitations of the tool are not helpful, although advice on how to work around such limitations are. Advice on how to configure and tune the system to render large datasets would be most useful, and I am sure product improvements in this area would benefit all users.
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |