Report Running Slowly

I have a report in SQL Reporting Services 2005 which calls a stored
proc and the report takes a very long time to run and sometimes
returns zero records. But when i run the stored proc in query analyzer
it takes about 4 seconds.
I have checked the execution log on the RS using the below sql:
Select * from ExecutionLog with (nolock) order by TimeStart DESC
It shows that i have a large amount of time for the dataretrieval
(601309ms, about 10mins) and does not return any records.
TimeDataRetrieval TimeProcessing TimeRendering
Source Status ByteCount RowCount
601309 2227
3 1 rsSuccess 4916 0
The weird thing is that when i run it in query analyzer, i get about
400 records in 4 seconds !!
I dont understand what RS is doing to take up so much time like this
to retrieve data.
The report is very simple - it basically returns the records straight
out into a table.
Can anyone suggest where to look or how to troubleshoot this problem?On Jun 27, 9:19 pm, Mark.J.Br...@.gmail.com wrote:
> I have a report in SQL Reporting Services 2005 which calls a stored
> proc and the report takes a very long time to run and sometimes
> returns zero records. But when i run the stored proc in query analyzer
> it takes about 4 seconds.
> I have checked the execution log on the RS using the below sql:
> Select * from ExecutionLog with (nolock) order by TimeStart DESC
> It shows that i have a large amount of time for the dataretrieval
> (601309ms, about 10mins) and does not return any records.
> TimeDataRetrieval TimeProcessing TimeRendering
> Source Status ByteCount RowCount
> 601309 2227
> 3 1 rsSuccess 4916 0
> The weird thing is that when i run it in query analyzer, i get about
> 400 records in 4 seconds !!
> I dont understand what RS is doing to take up so much time like this
> to retrieve data.
> The report is very simple - it basically returns the records straight
> out into a table.
> Can anyone suggest where to look or how to troubleshoot this problem?
Of course, SSRS has some overhead in being a service as opposed to
directly being a part of the database (as w/a query). Also, it
accesses IIS, etc (which adds overhead). The first thing that I can
suggest doing is to run the Database Engine Tuning Advisor against the
query used in the report and implement any indexes that it suggests.
Also if you are using report parameters, you should verify that they
are linked correctly in the datasets (via Data view -> Edit Dataset
[...] -> Parameters tab). Also, verify that there are not other
processes running on the machine that are consuming excessive
resources (though this could be a long shot). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||How long does it take from the development environment versus the server?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1182997161.429465.190340@.a26g2000pre.googlegroups.com...
>I have a report in SQL Reporting Services 2005 which calls a stored
> proc and the report takes a very long time to run and sometimes
> returns zero records. But when i run the stored proc in query analyzer
> it takes about 4 seconds.
> I have checked the execution log on the RS using the below sql:
> Select * from ExecutionLog with (nolock) order by TimeStart DESC
> It shows that i have a large amount of time for the dataretrieval
> (601309ms, about 10mins) and does not return any records.
> TimeDataRetrieval TimeProcessing TimeRendering
> Source Status ByteCount RowCount
> 601309 2227
> 3 1 rsSuccess 4916 0
> The weird thing is that when i run it in query analyzer, i get about
> 400 records in 4 seconds !!
> I dont understand what RS is doing to take up so much time like this
> to retrieve data.
> The report is very simple - it basically returns the records straight
> out into a table.
> Can anyone suggest where to look or how to troubleshoot this problem?
>|||On Jun 28, 1:29 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> How long does it take from the development environment versus the server?
The report is scheduled to run out of hours, when no other tasks are
scheduled to run (in SQL or Reporting Services or Windows).
Most scheduled reports work correctly and contain data. Occassionally
this report will return 0 rows.
The only common denominator between the reports that return 0 rows is
they always exceed 600,000 in the TimeDataRetrieval column.|||Are you able to run it from the development environment as a test?
It could be that you are getting into some locking issues when this is
running.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1183003249.202007.213580@.x35g2000prf.googlegroups.com...
> On Jun 28, 1:29 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> How long does it take from the development environment versus the server?
> The report is scheduled to run out of hours, when no other tasks are
> scheduled to run (in SQL or Reporting Services or Windows).
> Most scheduled reports work correctly and contain data. Occassionally
> this report will return 0 rows.
> The only common denominator between the reports that return 0 rows is
> they always exceed 600,000 in the TimeDataRetrieval column.
>|||On Jun 29, 10:53 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Are you able to run it from the development environment as a test?
> It could be that you are getting into some locking issues when this is
> running.
Same problem in development environment.
As to the live data, there should be no other processes running at
that time, or users accessing the data.
Mark|||I have heard about but never seen an issue where SQL from RS ends up using a
different query plan.
Try one of the two methods in this link to force a recompile:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b90deb27-0099-4fe7-ba60-726af78f7c18.htm
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1183337771.100767.292930@.z28g2000prd.googlegroups.com...
> On Jun 29, 10:53 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>> Are you able to run it from the development environment as a test?
>> It could be that you are getting into some locking issues when this is
>> running.
> Same problem in development environment.
> As to the live data, there should be no other processes running at
> that time, or users accessing the data.
> Mark
>|||On Jul 3, 1:58 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have heard about but never seen an issue where SQL from RS ends up usin=g a
> different query plan.
> Try one of the two methods in this link to force a recompile:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b90deb27-0099-4fe7-ba60-7=26=ADaf78f7c18.htm
Can't get that link to work. Are you able to provide a topic title,
or a microsoft.com link?|||This isn't the MS link but it describes someone who had a performance
problem with the stored procedure. What you want to do is use the With
Recompile option with the stored procedure.
http://articles.techrepublic.com.com/5100-9592_11-5662581.html
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<Mark.J.Brown@.gmail.com> wrote in message
news:1183506487.200772.282060@.j4g2000prf.googlegroups.com...
On Jul 3, 1:58 am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have heard about but never seen an issue where SQL from RS ends up using
> a
> different query plan.
> Try one of the two methods in this link to force a recompile:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b90deb27-0099-4fe7-ba60-726­af78f7c18.htm
Can't get that link to work. Are you able to provide a topic title,
or a microsoft.com link?