Anitivirus
IS-IT--Management
Hi,
I am tying to optize a stored procedure which is taking two function with 6 parameters and producing the output.
The issue is ,when I am running only the query its taking around 2 and half minites but if I am taking the same query into a stored procedure the SP is taking more than 10 minutes to execute. I have checked the execution plan where I saw the difference between two(SP , QUERY) is in (Row Count Spool) , this step is showing most of the CPU usage in case of SP but in case of the query the CPU usage is 0%.
All the Sp optimizatinon tecchnique had been applied with NOCOUNT ON , imposing NOLOCK hint on tables.
Here I am giving the sample code for the SP
CREATE Procedure Proc_test(@p1 varchar10,@p2 varchar10,@p3 varchar10,@P4 varchar10,@p5 varchar10,@p6 varchar10)
AS
select a.x , a.y , a.z , b.x,b.y,b.z from
a(@p1,@p2,@p3,@P4,@p5,@p6) inner join
b(@p1,@p2,@p3,@P4,@p5,@p6)
on a.PK = b.FK
Now when I am executing the SP by assigning value , its takes a huge time to execute.
but here is the query which is taking 1/4 time of the SP.
Declare @p1 varchar10,@p2 varchar10,@p3 varchar10,@P4 varchar10,@p5 varchar10,@p6 varchar10
set @p1 = 'abc'
set @p2 = 'xyz'
set @p3 = 12
set @p4 = mn
set @p5 = 76
set @p6 = 'JKL'
select a.x , a.y , a.z , b.x,b.y,b.z from
a(@p1,@p2,@p3,@P4,@p5,@p6) inner join
b(@p1,@p2,@p3,@P4,@p5,@p6)
on a.PK = b.FK
Any help will be highly appreciated.
I am tying to optize a stored procedure which is taking two function with 6 parameters and producing the output.
The issue is ,when I am running only the query its taking around 2 and half minites but if I am taking the same query into a stored procedure the SP is taking more than 10 minutes to execute. I have checked the execution plan where I saw the difference between two(SP , QUERY) is in (Row Count Spool) , this step is showing most of the CPU usage in case of SP but in case of the query the CPU usage is 0%.
All the Sp optimizatinon tecchnique had been applied with NOCOUNT ON , imposing NOLOCK hint on tables.
Here I am giving the sample code for the SP
CREATE Procedure Proc_test(@p1 varchar10,@p2 varchar10,@p3 varchar10,@P4 varchar10,@p5 varchar10,@p6 varchar10)
AS
select a.x , a.y , a.z , b.x,b.y,b.z from
a(@p1,@p2,@p3,@P4,@p5,@p6) inner join
b(@p1,@p2,@p3,@P4,@p5,@p6)
on a.PK = b.FK
Now when I am executing the SP by assigning value , its takes a huge time to execute.
but here is the query which is taking 1/4 time of the SP.
Declare @p1 varchar10,@p2 varchar10,@p3 varchar10,@P4 varchar10,@p5 varchar10,@p6 varchar10
set @p1 = 'abc'
set @p2 = 'xyz'
set @p3 = 12
set @p4 = mn
set @p5 = 76
set @p6 = 'JKL'
select a.x , a.y , a.z , b.x,b.y,b.z from
a(@p1,@p2,@p3,@P4,@p5,@p6) inner join
b(@p1,@p2,@p3,@P4,@p5,@p6)
on a.PK = b.FK
Any help will be highly appreciated.