Performance problem -- Execute stored procedure

Hello,
I have a problem on running one particular stored procedure. It takes
less than 1 second when I run this stored procedure using SQL query
analyzer. However, when I run the same stored procedure using
Reporting Services, it takes 3,4 minutes to execute. This stored
procedure returns 52 rows with 16 fields. Does anyone know why and
how to solve this problem?
Thanks!I have never seen this myself but have heard of it before. For whatever
reason the query plan is messed up for that stored procedure when executing
it from RS. Try one of the below (I would start off with the With Recompile
as a test of whether this is the problem).
Forcing a Stored Procedure to Recompile
SQL Server provides three ways to force a stored procedure to recompile:
a.. The sp_recompile system stored procedure forces a recompile of a
stored procedure the next time it is run.
b.. Creating a stored procedure that specifies the WITH RECOMPILE option
in its definition indicates that SQL Server does not cache a plan for this
stored procedure; the stored procedure is recompiled each time it is
executed. Use the WITH RECOMPILE option when stored procedures take
parameters whose values differ widely between executions of the stored
procedure, resulting in different execution plans to be created each time.
Use of this option is uncommon and causes the stored procedure to execute
more slowly, because the stored procedure must be recompiled each time it is
executed.
If you only want individual queries inside the stored procedure to be
recompiled, rather than the entire stored procedure, specify the RECOMPILE
query hint inside each query you want recompiled. This behavior mimics SQL
Server's statement-level recompilation behavior noted above, but in addition
to using the stored procedure's current parameter values, the RECOMPILE
query hint also uses the values of any local variables inside the stored
procedure when compiling the statement. Use this option when atypical or
temporary values are used in only a subset of queries belonging to the
stored procedure. For more information, see Query Hint (Transact-SQL).
c.. You can force the stored procedure to be recompiled by specifying the
WITH RECOMPILE option when you execute the stored procedure. Use this option
only if the parameter you are supplying is atypical or if the data has
significantly changed since the stored procedure was created.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<chiekot@.taiweb.com> wrote in message
news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> Hello,
> I have a problem on running one particular stored procedure. It takes
> less than 1 second when I run this stored procedure using SQL query
> analyzer. However, when I run the same stored procedure using
> Reporting Services, it takes 3,4 minutes to execute. This stored
> procedure returns 52 rows with 16 fields. Does anyone know why and
> how to solve this problem?
> Thanks!
>|||On Oct 15, 2:37 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> I have never seen this myself but have heard of it before. For whatever
> reason the query plan is messed up for that stored procedure when executing
> it from RS. Try one of the below (I would start off with the With Recompile
> as a test of whether this is the problem).
> Forcing a Stored Procedure to Recompile
> SQL Server provides three ways to force a stored procedure to recompile:
> a.. The sp_recompile system stored procedure forces a recompile of a
> stored procedure the next time it is run.
> b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> in its definition indicates that SQL Server does not cache a plan for this
> stored procedure; the stored procedure is recompiled each time it is
> executed. Use the WITH RECOMPILE option when stored procedures take
> parameters whose values differ widely between executions of the stored
> procedure, resulting in different execution plans to be created each time.
> Use of this option is uncommon and causes the stored procedure to execute
> more slowly, because the stored procedure must be recompiled each time it is
> executed.
> If you only want individual queries inside the stored procedure to be
> recompiled, rather than the entire stored procedure, specify the RECOMPILE
> query hint inside each query you want recompiled. This behavior mimics SQL
> Server's statement-level recompilation behavior noted above, but in addition
> to using the stored procedure's current parameter values, the RECOMPILE
> query hint also uses the values of any local variables inside the stored
> procedure when compiling the statement. Use this option when atypical or
> temporary values are used in only a subset of queries belonging to the
> stored procedure. For more information, see Query Hint (Transact-SQL).
> c.. You can force the stored procedure to be recompiled by specifying the
> WITH RECOMPILE option when you execute the stored procedure. Use this option
> only if the parameter you are supplying is atypical or if the data has
> significantly changed since the stored procedure was created.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <chie...@.taiweb.com> wrote in message
> news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> > Hello,
> > I have a problem on running one particular stored procedure. It takes
> > less than 1 second when I run this stored procedure using SQL query
> > analyzer. However, when I run the same stored procedure using
> > Reporting Services, it takes 3,4 minutes to execute. This stored
> > procedure returns 52 rows with 16 fields. Does anyone know why and
> > how to solve this problem?
> > Thanks!
Also, to improve the performance of the stored procedure in general,
you could evaluate it with the Database Engine Tuning Advisor and
implement the suggested indexes where acceptable. Hope this helps
further.
Regards,
Enrique Martinez
Sr. Software Consultant|||Bruce and Enrique, Thank you very much for your responses. We solved
this problem. What our DBA told me is that he changed that the stored
procedure is created with SET QUOTED_IDENTIFIER to ON. I appreciate
all your suggestions.
Retards,
Chieko
On Oct 15, 6:03 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Oct 15, 2:37 pm, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
> wrote:
>
>
> > I have never seen this myself but have heard of it before. For whatever
> > reason the query plan is messed up for that stored procedure when executing
> > it from RS. Try one of the below (I would start off with the With Recompile
> > as a test of whether this is the problem).
> > Forcing a Stored Procedure to Recompile
> > SQL Server provides three ways to force a stored procedure to recompile:
> > a.. The sp_recompile system stored procedure forces a recompile of a
> > stored procedure the next time it is run.
> > b.. Creating a stored procedure that specifies the WITH RECOMPILE option
> > in its definition indicates that SQL Server does not cache a plan for this
> > stored procedure; the stored procedure is recompiled each time it is
> > executed. Use the WITH RECOMPILE option when stored procedures take
> > parameters whose values differ widely between executions of the stored
> > procedure, resulting in different execution plans to be created each time.
> > Use of this option is uncommon and causes the stored procedure to execute
> > more slowly, because the stored procedure must be recompiled each time it is
> > executed.
> > If you only want individual queries inside the stored procedure to be
> > recompiled, rather than the entire stored procedure, specify the RECOMPILE
> > query hint inside each query you want recompiled. This behavior mimics SQL
> > Server's statement-level recompilation behavior noted above, but in addition
> > to using the stored procedure's current parameter values, the RECOMPILE
> > query hint also uses the values of any local variables inside the stored
> > procedure when compiling the statement. Use this option when atypical or
> > temporary values are used in only a subset of queries belonging to the
> > stored procedure. For more information, see Query Hint (Transact-SQL).
> > c.. You can force the stored procedure to be recompiled by specifying the
> > WITH RECOMPILE option when you execute the stored procedure. Use this option
> > only if the parameter you are supplying is atypical or if the data has
> > significantly changed since the stored procedure was created.
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> > <chie...@.taiweb.com> wrote in message
> >news:1192470971.617725.137060@.e34g2000pro.googlegroups.com...
> > > Hello,
> > > I have a problem on running one particular stored procedure. It takes
> > > less than 1 second when I run this stored procedure using SQL query
> > > analyzer. However, when I run the same stored procedure using
> > > Reporting Services, it takes 3,4 minutes to execute. This stored
> > > procedure returns 52 rows with 16 fields. Does anyone know why and
> > > how to solve this problem?
> > > Thanks!
> Also, to improve the performance of the stored procedure in general,
> you could evaluate it with the Database Engine Tuning Advisor and
> implement the suggested indexes where acceptable. Hope this helps
> further.
> Regards,
> Enrique Martinez
> Sr. Software Consultant- Hide quoted text -
> - Show quoted text -