| Groups | Blog | Home |
sql server reporting services : Report Running Slowly
How long does it take from the development environment versus the server?
-- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] <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>
[quoted text, click to view] 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.
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?
[quoted text, click to view]
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
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 [quoted text, click to view] <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>
[quoted text, click to view] 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 [quoted text, click to view] <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 >
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 [quoted text, click to view] <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-726af78f7c18.htm Can't get that link to work. Are you able to provide a topic title, or a microsoft.com link?
On Jul 3, 1:58 am, "Bruce L-C [MVP]" <bruce_lcNOS...@hotmail.com>
[quoted text, click to view] wrote: 26=ADaf78f7c18.htm> 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= Can't get that link to work. Are you able to provide a topic title, or a microsoft.com link?
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 |