Prev: for xml auto and synonyms
Next: Is any group function which concatenates data?
From: Jose Nadim on 15 Jun 2010 19:10
Hello,i have a stored procedure that is called from an ASP.net page
and it takes
about 2 minutes to execute. When I run it from QA it takes about 10
seconds. the procedure which is not recompiled and it is in procedure
cache.
Thanks for your help.
Jose Nadim
From: Tony Rogerson on 16 Jun 2010 00:38
Hi Jose,

It will probably be a bad plan in procedure cache.

add "WITH RECOMPILE" to the stored procedure definition...

create proc blah
with recompile
as

Does that fix the problem?

If that fixes the problem then the original problem was caused by "parameter
sniffing" and an original call to the stored procedure with a given value
skewing the data causing a plan that is now being reused to be created - a
plan that is grossly inefficient for the second query you are running.

Tony.

"Jose Nadim" <josenadim(a)gmail.com> wrote in message
news:00855529-3158-4bc0-8d60-89db707d8154(a)y11g2000yqm.googlegroups.com...
> Hello,i have a stored procedure that is called from an ASP.net page
> and it takes
> about 2 minutes to execute. When I run it from QA it takes about 10
> seconds. the procedure which is not recompiled and it is in procedure
> cache.
> Thanks for your help.
> Jose Nadim

From: Jose Nadim on 16 Jun 2010 10:43
Hi Tony !,
I will read about of parameter sniffing; and i forget how is create
my sp : (exec to 25 sotred procedures)
ALTER PROCEDURE [dbo].[Proc_Consultas]
-- Add the parameters for the stored procedure here
@fecha1 as datetime,
@fecha2 as datetime,
@codisec as char(2),
@codiest as char(2),
@coditra as char(2),
@codipat as char(3),
@funcion as char(12),
@combo as char(1),
@markmue as char(1)
AS
BEGIN

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
if @funcion = 'DOCCONSA'
EXEC PROC_DOCCONSA @fecha1,@fecha2,@coditra,@combo
ELSE if @funcion = 'DOCCONSRECA'
EXEC PROCDOCCONSRECA @fecha1,@fecha2,@combo
ELSE if @funcion = 'DOCCONSINCA'
EXEC PROCDOCCONSINCA @fecha1,@fecha2,@combo

ELSE if @funcion = 'DOCCONSA1'
EXEC PROCDOCCONSA1 @fecha1,@fecha2,@coditra,@combo
ELSE if @funcion = 'DOCCONSBA'
EXEC PROCDOCCONBA @fecha1,@fecha2
ELSE if @funcion = 'DOCCONSAU'
EXEC PROCDOCCONSAU @fecha1,@fecha2
etc,etc,etc
END

thanks

On 15 jun, 23:38, "Tony Rogerson" <tonyroger...(a)torver.net> wrote:
> Hi Jose,
>
> It will probably be a bad plan in procedure cache.
>
> add "WITH RECOMPILE" to the stored procedure definition...
>
> create proc blah
>     with recompile
> as
>
> Does that fix the problem?
>
> If that fixes the problem then the original problem was caused by "parameter
> sniffing" and an original call to the stored procedure with a given value
> skewing the data causing a plan that is now being reused to be created - a
> plan that is grossly inefficient for the second query you are running.
>
> Tony.
>
> "Jose Nadim" <josena...(a)gmail.com> wrote in message
>
> news:00855529-3158-4bc0-8d60-89db707d8154(a)y11g2000yqm.googlegroups.com...
>
>
>
> > Hello,i have a stored procedure that is called from an ASP.net page
> > and it takes
> > about 2 minutes to execute. When I run it from QA it takes about 10
> > seconds. the procedure which is not recompiled and it is in procedure
> > cache.
> > Thanks for your help.
> > Jose Nadim- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

From: Bob Barrows on 16 Jun 2010 10:49
Start by using SQL Profiler to isolate the slow sql statement. You will
need the SP:StmtCompleted event at a minimum.

Jose Nadim wrote:
> Hi Tony !,
> I will read about of parameter sniffing; and i forget how is create
> my sp : (exec to 25 sotred procedures)
> ALTER PROCEDURE [dbo].[Proc_Consultas]
> -- Add the parameters for the stored procedure here
> @fecha1 as datetime,
> @fecha2 as datetime,
> @codisec as char(2),
> @codiest as char(2),
> @coditra as char(2),
> @codipat as char(3),
> @funcion as char(12),
> @combo as char(1),
> @markmue as char(1)
> AS
> BEGIN
>
> SET NOCOUNT ON;
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> if @funcion = 'DOCCONSA'
> EXEC PROC_DOCCONSA @fecha1,@fecha2,@coditra,@combo
> ELSE if @funcion = 'DOCCONSRECA'
> EXEC PROCDOCCONSRECA @fecha1,@fecha2,@combo
> ELSE if @funcion = 'DOCCONSINCA'
> EXEC PROCDOCCONSINCA @fecha1,@fecha2,@combo
>
> ELSE if @funcion = 'DOCCONSA1'
> EXEC PROCDOCCONSA1 @fecha1,@fecha2,@coditra,@combo
> ELSE if @funcion = 'DOCCONSBA'
> EXEC PROCDOCCONBA @fecha1,@fecha2
> ELSE if @funcion = 'DOCCONSAU'
> EXEC PROCDOCCONSAU @fecha1,@fecha2
> etc,etc,etc
> END
>
> thanks
>
> On 15 jun, 23:38, "Tony Rogerson" <tonyroger...(a)torver.net> wrote:
>> Hi Jose,
>>
>> It will probably be a bad plan in procedure cache.
>>
>> add "WITH RECOMPILE" to the stored procedure definition...
>>
>> create proc blah
>> with recompile
>> as
>>
>> Does that fix the problem?
>>
>> If that fixes the problem then the original problem was caused by
>> "parameter sniffing" and an original call to the stored procedure
>> with a given value skewing the data causing a plan that is now being
>> reused to be created - a plan that is grossly inefficient for the
>> second query you are running.
>>
>> Tony.
>>
>> "Jose Nadim" <josena...(a)gmail.com> wrote in message
>>
>>
news:00855529-3158-4bc0-8d60-89db707d8154(a)y11g2000yqm.googlegroups.com...
>>
>>
>>
>>> Hello,i have a stored procedure that is called from an ASP.net page
>>> and it takes
>>> about 2 minutes to execute. When I run it from QA it takes about 10
>>> seconds. the procedure which is not recompiled and it is in
>>> procedure cache.
>>> Thanks for your help.
>>> Jose Nadim- Ocultar texto de la cita -
>>
>> - Mostrar texto de la cita -

--
HTH,
Bob Barrows


From: Jose Nadim on 16 Jun 2010 11:28
Hi Bob, on profiler :
1. QA ok 10 seconds
2. Web page asp 2 minutes

And i put WITH RECOMPILE on my second stored procedure and it
works !!
but i dont understand the executions time from client application :
QA its OK and web pages is bad
the time should be same on both client applications,because the sotred
procedure is on the server.

thanks

Jose Nadim


On 16 jun, 09:49, "Bob Barrows" <reb01...(a)NOyahoo.SPAMcom> wrote:
> Start by using SQL Profiler to isolate the slow sql statement. You will
> need the SP:StmtCompleted event at a minimum.
>
>
>
>
>
> Jose Nadim wrote:
> > Hi Tony !,
> > I will read about of parameter sniffing; and  i forget how is create
> > my sp : (exec to  25 sotred procedures)
> > ALTER PROCEDURE [dbo].[Proc_Consultas]
> > -- Add the parameters for the stored procedure here
> > @fecha1  as datetime,
> > @fecha2  as datetime,
> > @codisec as char(2),
> > @codiest as char(2),
> > @coditra as char(2),
> > @codipat as char(3),
> > @funcion as char(12),
> > @combo as char(1),
> > @markmue as char(1)
> > AS
> > BEGIN
>
> > SET NOCOUNT ON;
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> > if @funcion = 'DOCCONSA'
> > EXEC PROC_DOCCONSA @fecha1,@fecha2,@coditra,@combo
> > ELSE if @funcion = 'DOCCONSRECA'
> > EXEC PROCDOCCONSRECA @fecha1,@fecha2,@combo
> > ELSE if @funcion = 'DOCCONSINCA'
> > EXEC PROCDOCCONSINCA @fecha1,@fecha2,@combo
>
> > ELSE if @funcion = 'DOCCONSA1'
> > EXEC PROCDOCCONSA1 @fecha1,@fecha2,@coditra,@combo
> > ELSE if @funcion = 'DOCCONSBA'
> > EXEC PROCDOCCONBA @fecha1,@fecha2
> > ELSE if @funcion = 'DOCCONSAU'
> > EXEC PROCDOCCONSAU @fecha1,@fecha2
> > etc,etc,etc
> > END
>
> > thanks
>
> > On 15 jun, 23:38, "Tony Rogerson" <tonyroger...(a)torver.net> wrote:
> >> Hi Jose,
>
> >> It will probably be a bad plan in procedure cache.
>
> >> add "WITH RECOMPILE" to the stored procedure definition...
>
> >> create proc blah
> >> with recompile
> >> as
>
> >> Does that fix the problem?
>
> >> If that fixes the problem then the original problem was caused by
> >> "parameter sniffing" and an original call to the stored procedure
> >> with a given value skewing the data causing a plan that is now being
> >> reused to be created - a plan that is grossly inefficient for the
> >> second query you are running.
>
> >> Tony.
>
> >> "Jose Nadim" <josena...(a)gmail.com> wrote in message
>
> news:00855529-3158-4bc0-8d60-89db707d8154(a)y11g2000yqm.googlegroups.com...
>
>
>
> >>> Hello,i have a stored procedure that is called from an ASP.net page
> >>> and it takes
> >>> about 2 minutes to execute. When I run it from QA it takes about 10
> >>> seconds. the procedure which is not recompiled and it is in
> >>> procedure cache.
> >>> Thanks for your help.
> >>> Jose Nadim- Ocultar texto de la cita -
>
> >> - Mostrar texto de la cita -
>
> --
> HTH,
> Bob Barrows- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

 |  Next  |  Last
Pages: 1 2
Prev: for xml auto and synonyms
Next: Is any group function which concatenates data?