Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Performance of SP pathetic compare to the same query

Status
Not open for further replies.

Anitivirus

IS-IT--Management
Mar 4, 2008
14
US
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.
 
Clearly, the 2 functions are 'table-valued' functions. Does each function have a unique column that it is returning? If so, you could try declaring the unique column as a primary key, which will effectively put an index on the table variable.

Ex:

Code:
Create Function dbo.A
  (@P1 varchar(10), @P2 VarChar(10), etc...)
Returns Table (PK Int [!]Primary Key[/!], Col1 int, etc...)

While you're at it... do a google search on [google]parameter sniffing[/google]



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top