March 3, 2008 at 12:55 pm
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%.
Any help will be highly appreciated.
March 3, 2008 at 7:41 pm
Not sure what you mean by:
two function with 6 parameters and producing the output.
First make sure the beginning of your sp has SET NOCOUNT ON.
How are you using the parameters? What are the functions you are using?
When you are running the query are you providing direct values or are you running the exact same query?
Some details and some code would help us help you.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2008 at 7:59 am
Thanks Jack, 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
I hope I have made it clear.
March 4, 2008 at 8:18 am
Clear as mud. All your processing is being done in the functions so without knowing what is happening in the functions there is no way to suggest optimizations.
The other question I have is are you running the query first or the SP first. If you run the SP, then immediately after run the query the data is all in memory so the query will be very fast and it shoul dwork in reverse as well. So between runs, if on a development machine, you should run DBCC DropCleanBuffers to ensure you are accessing the data the same way in both cases.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2008 at 1:41 pm
Also - you need to look up "parameter sniffing". It looks like you're falling victim to it.
I'm not sure what you're using the functions for - but I'd recommend removing them if at all possible. Joining the results from two TVF's is the same as doing that on two tables with no indexes whatsoever: makes for some very messy and slow results.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 4, 2008 at 1:56 pm
You can put a primary key on a TVF as long as it isn't an inline TVF. We would need to see the DDL for the functions to be able to help you with the problem. As previously noted, it could also be a case of parameter sniffing.
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply