Performance Question

I have a stored procedure that a report uses. When I run this sp in
management studio it takes about four and a half minutes to run. When I run
the report, the report takes more than 15 minutes to generate. Any idea why
there is such a large difference in the time it takes to run the sp vs
generate the report?
Thanks.How many records are returned? The number of records being rendered makes a
big difference with Reporting Services.
Also, a 4 1/2 minute report is very long, have you looked into optimizing
it. For instance, does your database need some additional indexes?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>I have a stored procedure that a report uses. When I run this sp in
>management studio it takes about four and a half minutes to run. When I
>run the report, the report takes more than 15 minutes to generate. Any
>idea why there is such a large difference in the time it takes to run the
>sp vs generate the report?
> Thanks.
>|||Where is the data located?
Maybe on the reporting server it takes longer to transmit the info.
"Tim Kelley" wrote:
> I have a stored procedure that a report uses. When I run this sp in
> management studio it takes about four and a half minutes to run. When I run
> the report, the report takes more than 15 minutes to generate. Any idea why
> there is such a large difference in the time it takes to run the sp vs
> generate the report?
> Thanks.
>
>|||How is the data being rendered? Are you using a matrix versus a table
to display the data?
HTH
Jason Strate
On Oct 15, 2:27 pm, "Tim Kelley" <tkel...@.company.com> wrote:
> I have a stored procedure that a report uses. When I run this sp in
> management studio it takes about four and a half minutes to run. When I run
> the report, the report takes more than 15 minutes to generate. Any idea why
> there is such a large difference in the time it takes to run the sp vs
> generate the report?
> Thanks.|||The data is stored on a different SQL server.
Tim
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:99D473D3-5B0C-44DD-8326-5D63415A3510@.microsoft.com...
> Where is the data located?
> Maybe on the reporting server it takes longer to transmit the info.
>
>
> "Tim Kelley" wrote:
>> I have a stored procedure that a report uses. When I run this sp in
>> management studio it takes about four and a half minutes to run. When I
>> run
>> the report, the report takes more than 15 minutes to generate. Any idea
>> why
>> there is such a large difference in the time it takes to run the sp vs
>> generate the report?
>> Thanks.
>>|||Before optimizing the stored procedure it would run for 45 minutes and not
finish. The sp returns 998 records.
Tim
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:eeQTgM2DIHA.5788@.TK2MSFTNGP05.phx.gbl...
> How many records are returned? The number of records being rendered makes
> a big difference with Reporting Services.
> Also, a 4 1/2 minute report is very long, have you looked into optimizing
> it. For instance, does your database need some additional indexes?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tim Kelley" <tkelley@.company.com> wrote in message
> news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>>I have a stored procedure that a report uses. When I run this sp in
>>management studio it takes about four and a half minutes to run. When I
>>run the report, the report takes more than 15 minutes to generate. Any
>>idea why there is such a large difference in the time it takes to run the
>>sp vs generate the report?
>> Thanks.
>|||That is not many records so rendering should not be an issue. Try using With
Recompile when creating the stored procedure and see if that makes any
difference.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ubvT7s2DIHA.4772@.TK2MSFTNGP02.phx.gbl...
> Before optimizing the stored procedure it would run for 45 minutes and not
> finish. The sp returns 998 records.
> Tim
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:eeQTgM2DIHA.5788@.TK2MSFTNGP05.phx.gbl...
>> How many records are returned? The number of records being rendered makes
>> a big difference with Reporting Services.
>> Also, a 4 1/2 minute report is very long, have you looked into optimizing
>> it. For instance, does your database need some additional indexes?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tim Kelley" <tkelley@.company.com> wrote in message
>> news:e0FIgF2DIHA.4196@.TK2MSFTNGP04.phx.gbl...
>>I have a stored procedure that a report uses. When I run this sp in
>>management studio it takes about four and a half minutes to run. When I
>>run the report, the report takes more than 15 minutes to generate. Any
>>idea why there is such a large difference in the time it takes to run the
>>sp vs generate the report?
>> Thanks.
>>
>