Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlbug
Posting Yak Master
201 Posts |
 Posted - 2013-11-05 : 15:59:27
|
| Hello,I have this big report that comes up with 30,40,50 pages depending on data. It can be grouped by up to 5 parameters and the more the parameters, the more the number of pages.The Report runs in a ReportViewer hosted on a .NET webpage.When I run the stored procedure in the query analyzer, it takes only few seconds.But looks like because of the large amount of data to be displayed, the report times out.Is there a way I can speedup this type of reports?Thanks so much. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-11-05 : 16:13:04
|
| Executing it in Query Analyzer may not reproduce the execution of the stored procedure enough as you could have a different execution plan than the report. Run a trace to see what the duration is for the report and add the showplan xml to the trace and compare it to the execution plan in Query Analyzer.The reason why you might have different plans is because of connection properties. If you grab the same connection properties as the report and run those commands in QA, then you'd mimic the report running the stored procedure. You can get the connection properties in the trace via the existing connections event.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
sqlbug
Posting Yak Master
201 Posts |
Posted - 2013-11-05 : 20:10:33
|
| Thanks Tara, there was little bit of difference. But How do I get the report use a connection property that's faster. And if this doesn't make much difference - what else can we do to run the report faster?Thanks so much. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-11-06 : 15:33:50
|
| Well it's not the connection property that's the issue. The issue is likely with a different execution plan. So you need to figure out what execution plan the report is getting and what execution plan you are getting.What version of SQL Server are you using? You've used the "Query Analyzer" term which is SQL Server 2000. Not too many options back then...Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
sqlbug
Posting Yak Master
201 Posts |
Posted - 2013-11-07 : 12:02:13
|
| We are using SQL Server 2008 R2 and ReportViewer 10. If the report gets a different execution plan that's slower, how can we fix it?Thanks Tara. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-11-07 : 12:26:37
|
| Have you verified that it's a bad plan issue? We need to figure out what the issue is before we come up with solutions. You do have more options to resolve a bad plan in 2008 R2, though 2012 would have even more options.To give you some ideas though of solutions:- updating statistics- adding WITH RECOMPILE/OPTION RECOMPILE- creating a plan guide- changing indexes- refactoring codeTara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
Report timeout