SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Report timeout
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlbug
Posting Yak Master

Canada
201 Posts

Posted - 11/05/2013 :  15:59:27  Show Profile  Reply with Quote
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

USA
36997 Posts

Posted - 11/05/2013 :  16:13:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 11/05/2013 16:13:21
Go to Top of Page

sqlbug
Posting Yak Master

Canada
201 Posts

Posted - 11/05/2013 :  20:10:33  Show Profile  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 11/06/2013 :  15:33:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sqlbug
Posting Yak Master

Canada
201 Posts

Posted - 11/07/2013 :  12:02:13  Show Profile  Reply with Quote
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.

Edited by - sqlbug on 11/07/2013 12:02:55
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36997 Posts

Posted - 11/07/2013 :  12:26:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 code

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 11/07/2013 12:27:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000