Report timeout - long running SPROC

Hi all,
I have a SPROC that returns 773,923 rows and takes 13mins to execute in SQL
Query Analyzer.
I think I have increased all available timeout and session settings, but the
report returns page cannot be displayed.
The same happens when I try to create a snapshot.
Any ideas?
Thank you in advance.
Cheers
ShaneThis is a very bad idea. RS renders everything in RAM. Depending on the
format it can take varying amounts of time. If you try to render to PDF or
Excel (can Excel even take that many rows, I'm not sure what the limit is
now, it was 64K in Excel 2000) it will most likely never finish. What are
you trying to do, a human will never look at that many rows. If you are
trying to do data extraction then there are better technologies to use. HTML
is fast and might be possible but not sure what it buys you since this is
probably 100,000 rows of data. I suggest reading up on drill through, that
is a very good technique for navigating large amounts of data. If you
explain what you are trying to do there might be some other way to
accomplish it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
> Hi all,
> I have a SPROC that returns 773,923 rows and takes 13mins to execute in
> SQL
> Query Analyzer.
> I think I have increased all available timeout and session settings, but
> the
> report returns page cannot be displayed.
> The same happens when I try to create a snapshot.
> Any ideas?
> Thank you in advance.
> Cheers
> Shane|||Thanks for taking the time to read my post Bruce.
The report is an age trial balance for our entire organisation. So each line
(all 773,923 rows) are invoice lines.
It allows our snr management to see what debt are owed to our company.
I use a drill down so when the report is initially loaded you see each of my
company's regions (10 of them) and the sum of the money owed by age 30 Days,
60-90 Days, 120+ Days...
If you click on the + it expands to reveal the different general leger
accounts and the sum of the money owed by age 30 Days, 60-90 Days, 120+
Days...
If you click on the + again, it will display the person who owes the money
and the sum of the money owed by age 30 Days, 60-90 Days, 120+ Days...
If you click on the + again, it will display each of the invoice details..
The report is being rendered in HTML and I also want the execution snapshot
to be rendered in HTML as well.
Cheers,
Shane
"Bruce L-C [MVP]" wrote:
> This is a very bad idea. RS renders everything in RAM. Depending on the
> format it can take varying amounts of time. If you try to render to PDF or
> Excel (can Excel even take that many rows, I'm not sure what the limit is
> now, it was 64K in Excel 2000) it will most likely never finish. What are
> you trying to do, a human will never look at that many rows. If you are
> trying to do data extraction then there are better technologies to use. HTML
> is fast and might be possible but not sure what it buys you since this is
> probably 100,000 rows of data. I suggest reading up on drill through, that
> is a very good technique for navigating large amounts of data. If you
> explain what you are trying to do there might be some other way to
> accomplish it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
> news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
> > Hi all,
> >
> > I have a SPROC that returns 773,923 rows and takes 13mins to execute in
> > SQL
> > Query Analyzer.
> >
> > I think I have increased all available timeout and session settings, but
> > the
> > report returns page cannot be displayed.
> >
> > The same happens when I try to create a snapshot.
> >
> > Any ideas?
> >
> > Thank you in advance.
> >
> > Cheers
> >
> > Shane
>
>|||OK, that is what I thought. Drill downs are great. Very intuitive.
Unfortunately they require all the data to be present. So although it is
great for the user it is not so a great a solution for speed. What I suggest
is to change this to limit the amount of data. Perhaps make it a combination
of drilldown and drillthrough. For instance, how much data is decreased if
you do a drillthrough for the involice details. Or perhaps the Person and
the invoice details. Make that it's own report with the appropriate
parameters and then drill through to it. My guess is that the sp would speed
up tremendously and the amount of data being dealt with by RS would drop to
a manageable level. My users (a very wide range from production operators to
management) have found drillthough to be very intuitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
news:3A3D9583-DF23-43B9-806F-0B4DD3A68F12@.microsoft.com...
> Thanks for taking the time to read my post Bruce.
> The report is an age trial balance for our entire organisation. So each
> line
> (all 773,923 rows) are invoice lines.
> It allows our snr management to see what debt are owed to our company.
> I use a drill down so when the report is initially loaded you see each of
> my
> company's regions (10 of them) and the sum of the money owed by age 30
> Days,
> 60-90 Days, 120+ Days...
> If you click on the + it expands to reveal the different general leger
> accounts and the sum of the money owed by age 30 Days, 60-90 Days, 120+
> Days...
> If you click on the + again, it will display the person who owes the money
> and the sum of the money owed by age 30 Days, 60-90 Days, 120+ Days...
> If you click on the + again, it will display each of the invoice details..
> The report is being rendered in HTML and I also want the execution
> snapshot
> to be rendered in HTML as well.
> Cheers,
> Shane
> "Bruce L-C [MVP]" wrote:
>> This is a very bad idea. RS renders everything in RAM. Depending on the
>> format it can take varying amounts of time. If you try to render to PDF
>> or
>> Excel (can Excel even take that many rows, I'm not sure what the limit is
>> now, it was 64K in Excel 2000) it will most likely never finish. What are
>> you trying to do, a human will never look at that many rows. If you are
>> trying to do data extraction then there are better technologies to use.
>> HTML
>> is fast and might be possible but not sure what it buys you since this is
>> probably 100,000 rows of data. I suggest reading up on drill through,
>> that
>> is a very good technique for navigating large amounts of data. If you
>> explain what you are trying to do there might be some other way to
>> accomplish it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
>> news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
>> > Hi all,
>> >
>> > I have a SPROC that returns 773,923 rows and takes 13mins to execute in
>> > SQL
>> > Query Analyzer.
>> >
>> > I think I have increased all available timeout and session settings,
>> > but
>> > the
>> > report returns page cannot be displayed.
>> >
>> > The same happens when I try to create a snapshot.
>> >
>> > Any ideas?
>> >
>> > Thank you in advance.
>> >
>> > Cheers
>> >
>> > Shane
>>|||Thanks again Bruce.
I think the drillthrough idea sounds like a good one. Similar to this Sample
report:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_samplereports_v1_4z95.asp
I'll give it a go and let you know how I go! :)
Cheers,
Shane
"Bruce L-C [MVP]" wrote:
> OK, that is what I thought. Drill downs are great. Very intuitive.
> Unfortunately they require all the data to be present. So although it is
> great for the user it is not so a great a solution for speed. What I suggest
> is to change this to limit the amount of data. Perhaps make it a combination
> of drilldown and drillthrough. For instance, how much data is decreased if
> you do a drillthrough for the involice details. Or perhaps the Person and
> the invoice details. Make that it's own report with the appropriate
> parameters and then drill through to it. My guess is that the sp would speed
> up tremendously and the amount of data being dealt with by RS would drop to
> a manageable level. My users (a very wide range from production operators to
> management) have found drillthough to be very intuitive.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
> news:3A3D9583-DF23-43B9-806F-0B4DD3A68F12@.microsoft.com...
> > Thanks for taking the time to read my post Bruce.
> >
> > The report is an age trial balance for our entire organisation. So each
> > line
> > (all 773,923 rows) are invoice lines.
> >
> > It allows our snr management to see what debt are owed to our company.
> >
> > I use a drill down so when the report is initially loaded you see each of
> > my
> > company's regions (10 of them) and the sum of the money owed by age 30
> > Days,
> > 60-90 Days, 120+ Days...
> >
> > If you click on the + it expands to reveal the different general leger
> > accounts and the sum of the money owed by age 30 Days, 60-90 Days, 120+
> > Days...
> >
> > If you click on the + again, it will display the person who owes the money
> > and the sum of the money owed by age 30 Days, 60-90 Days, 120+ Days...
> >
> > If you click on the + again, it will display each of the invoice details..
> >
> > The report is being rendered in HTML and I also want the execution
> > snapshot
> > to be rendered in HTML as well.
> >
> > Cheers,
> >
> > Shane
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> This is a very bad idea. RS renders everything in RAM. Depending on the
> >> format it can take varying amounts of time. If you try to render to PDF
> >> or
> >> Excel (can Excel even take that many rows, I'm not sure what the limit is
> >> now, it was 64K in Excel 2000) it will most likely never finish. What are
> >> you trying to do, a human will never look at that many rows. If you are
> >> trying to do data extraction then there are better technologies to use.
> >> HTML
> >> is fast and might be possible but not sure what it buys you since this is
> >> probably 100,000 rows of data. I suggest reading up on drill through,
> >> that
> >> is a very good technique for navigating large amounts of data. If you
> >> explain what you are trying to do there might be some other way to
> >> accomplish it.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Shane" <s.dixon@.hia.asn.auNOSPAM> wrote in message
> >> news:86D96692-5AD5-4BC0-953D-99A53D623AE3@.microsoft.com...
> >> > Hi all,
> >> >
> >> > I have a SPROC that returns 773,923 rows and takes 13mins to execute in
> >> > SQL
> >> > Query Analyzer.
> >> >
> >> > I think I have increased all available timeout and session settings,
> >> > but
> >> > the
> >> > report returns page cannot be displayed.
> >> >
> >> > The same happens when I try to create a snapshot.
> >> >
> >> > Any ideas?
> >> >
> >> > Thank you in advance.
> >> >
> >> > Cheers
> >> >
> >> > Shane
> >>
> >>
> >>
>
>

Report table does not display all rows from dataset

I have a dataset that when run returns 270 rows. The table using the dataset in the report only prints the first row. I have the table grouped by a status type, but this is for when I can get multi-select paramenters installed and working. For now I just need the report to print all the returned rows. Help!!

Thanks!

Terry

Try to remove the grouping and see if you get the 270 rows in your report.

Jarret

Report Subscriptions

Hello,

I have a dril lthrough report which takes a long time (5-7 minutes) to fetch the results. The reason is that the data it returns is huge as it fetches sales data till the lowest level.

when the users later on trz to export it to excel it takes again a lot of time and eventuallz the szstem freezes. I think the volume of export might be in the range of 14/15 MB.

Is there a way to reduce first the execution speed of the report and then secondly reduce the time to export the report?

Or a way where the report is executed once a day and everyone shares the already published report?

regards

Josh

Hello Josh,

Yes, you can cache your report once a day and have everyone share it.

Open Report Manager (http://ServerName/Reports) and drill down to your report. Click on the Properties tab across the top, then click Data Sources from the left pane. Check the box for 'Credentials stored securely in the report server' and enter a valid user/password to run the report. If your account is a domain account, check the box for 'Use as Windows credentials when connecting to the data source', then hit Apply. Now, click on the Execution link from the left pane. Select 'Render this report from a report execution snapshot', configure your schedule to run once daily at whatever time you wish, then hit Apply.

By doing this, whenever a user goes to view this report, they will see the cached version of the report and they won't have to wait for the report to be generated.

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks!

regards

Josh

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?