Cursors inside SQL Functions - Very long execution time

I have a report that requires the use of cursors within a function because we
don't have control over the design of the database. So, the datasource for
the report is as follows:
1. Report calls a Stored Proc
2. Stored Proc runs this statement: Select... dbo.Function()...
3. Inside the Function(), I loop through a cursor.
Now when I run this Stored Proc directly in Query Analyzer, it takes 3
seconds.
But, when the report is run through SQL Reporting Services, it takes 108
seconds. I ran a SQL Profiler and noticed that Reporting Services was using
.Net Provider and the provider is way too slow for running cursors
- 2000ms for .Net Provider vs 0ms for Query Analyzer.
Is there anything I can do other than finding a way out of using cursors?Assuming that you call a stored procedure from RS it makes absolutely no
difference as far as executing the procedure. Stored procedures execute on
the server. It is not like the dotnet provider is opening a cursor, it is
not, it calls the stored procedure and gets back the results. What is most
likely happening is one of two things. Sometime there can be a delay due to
security authentication. More likely, however, is that you are returning a
lot of records and the rendering is what is taking the time. What are you
rendering to? Html, pdf, excel? PDF and Excel take much longer to render to
than HTML. Are you using a filter? Filters bring over all the data and then
filters it. So, the key questions are, what are you rendering to and how
many records is the stored procedure returning.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sanjay Gandhi" <SanjayGandhi@.discussions.microsoft.com> wrote in message
news:36286553-9F41-488F-BB24-4DC46C680168@.microsoft.com...
>I have a report that requires the use of cursors within a function because
>we
> don't have control over the design of the database. So, the datasource for
> the report is as follows:
> 1. Report calls a Stored Proc
> 2. Stored Proc runs this statement: Select... dbo.Function()...
> 3. Inside the Function(), I loop through a cursor.
> Now when I run this Stored Proc directly in Query Analyzer, it takes 3
> seconds.
> But, when the report is run through SQL Reporting Services, it takes 108
> seconds. I ran a SQL Profiler and noticed that Reporting Services was
> using
> .Net Provider and the provider is way too slow for running cursors
> - 2000ms for .Net Provider vs 0ms for Query Analyzer.
> Is there anything I can do other than finding a way out of using cursors?|||The number of records returned for the parameters I specified is only 69. And
I am rendering the report using the Reporting Server interface.
I may have solved this issue. I ran the Index Tuning Wizard on the query
used by the cursor and resulted in a 95% improvement. So that leads me to
think that when the cursor is run through Reporting Server, it did not use
the best execution plan. I'll do some more testing and see if that's it.
"Bruce L-C [MVP]" wrote:
> Assuming that you call a stored procedure from RS it makes absolutely no
> difference as far as executing the procedure. Stored procedures execute on
> the server. It is not like the dotnet provider is opening a cursor, it is
> not, it calls the stored procedure and gets back the results. What is most
> likely happening is one of two things. Sometime there can be a delay due to
> security authentication. More likely, however, is that you are returning a
> lot of records and the rendering is what is taking the time. What are you
> rendering to? Html, pdf, excel? PDF and Excel take much longer to render to
> than HTML. Are you using a filter? Filters bring over all the data and then
> filters it. So, the key questions are, what are you rendering to and how
> many records is the stored procedure returning.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sanjay Gandhi" <SanjayGandhi@.discussions.microsoft.com> wrote in message
> news:36286553-9F41-488F-BB24-4DC46C680168@.microsoft.com...
> >I have a report that requires the use of cursors within a function because
> >we
> > don't have control over the design of the database. So, the datasource for
> > the report is as follows:
> > 1. Report calls a Stored Proc
> > 2. Stored Proc runs this statement: Select... dbo.Function()...
> > 3. Inside the Function(), I loop through a cursor.
> >
> > Now when I run this Stored Proc directly in Query Analyzer, it takes 3
> > seconds.
> >
> > But, when the report is run through SQL Reporting Services, it takes 108
> > seconds. I ran a SQL Profiler and noticed that Reporting Services was
> > using
> > .Net Provider and the provider is way too slow for running cursors
> > - 2000ms for .Net Provider vs 0ms for Query Analyzer.
> >
> > Is there anything I can do other than finding a way out of using cursors?
>
>