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
> >>
> >>
> >>
>
>