June 17, 2012 at 3:48 pm
Hello,
I have a table valued function that that looks returns data compared with historical data. The problem is that execution time of functions differs greatly depending on how it is called. If it is called like this:
DECLARE @param1 varchar(50) = 'ABC';
DECLARE @param2 datetime = '2012-06-13';
DECLARE @param3 datetime = '2012-06-12';
SELECT * FROM dbo.SomeTVS(@param1, @param2, @param3)
[where @param1 is varchar(50), @param2 and @param3 is datetime] the execution time is about 25:00 minutes. When it is called like this:
SELECT * FROM dbo.SomeTVS('ABC', '2012-06-13', '2012-06-12')
the execution time is about 1:30. The thing is that this function is called by some stored procedure and should be called using parameters. My question is what causes such a big difference in execution time and how this could be avoided. I would appreciate any explanation or at least pointing out to some resources.
Thanks,
June 17, 2012 at 4:49 pm
What kind of function is that?
Can you post the two execution plans please?
Have you tried it with parameters as opposed to declared variables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2012 at 12:44 am
Hi Gail,
This is inline table valued function. It looks pretty much like this:
CREATE FUNCTION [dbo].[SomeTVF]
( @param1 varchar(50)
, @param2 datetime
, @param3 datetime
) RETURNS TABLE AS
RETURN
(
WITH cte as
(
..
)
SELECT cols
FROM dbo.SomeTVF2(@param1, @param2) t1
LEFT JOIN
( SELECT cols
FROM dbo.SomeTVF2(@param1, @param3)
WHERE ...
) t2 ON t1.PK = t2.PK
There is no special magic but a lot of underlying joins. I cannot show you execution plans as they are rather big (XML plan has 66MB).
I tried executing function within stored procedure using parametrs, but it still takes too much time. I thought that could be a lot of joins to blame, but executing it with values instead of variables/parametrs boost it more than 10 times.
Thanks,
June 18, 2012 at 1:48 am
All I can suggest then is simplify the query (break it up, use temp tables, etc). Sounds like it's got too complex to be handleable quickly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2012 at 2:05 am
In general I agree with you. However, what amazed me the most is the difference between calling function with variables and direct values. Current workaround is to use dynamic sql and sp_executeSql. I am not a fan of this but it works over ten time faster. I thought that this could be due to parameter sniffing, but wrapping it with extra stored procedure did not help.
June 18, 2012 at 2:41 am
I suspect it is related to parameter sniffing. Seriously, simplify the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 18, 2012 at 9:17 am
If you can't rewrite the query easily, a quick fix that worked for me with a slow function suffering from parameter sniffing was to declare variables within the function, then assign the parameter values to those variables. So the first bit of your function would be something like:
DECLARE @param_in_function1 varchar(50)
DECLARE @param_in_function2 datetime
DECLARE @param_in_function3 datetime
SET @param_in_function1 = @param1
SET @param_in_function1 = @param2
SET @param_in_function1 = @param3
You would then use the internal variables in place of the parameters within your query in the function. This gets around parameter sniffing, and as I say, worked for me.
Duncan
June 18, 2012 at 9:26 am
kharol (6/18/2012)
Hi Gail,This is inline table valued function. It looks pretty much like this:
CREATE FUNCTION [dbo].[SomeTVF]
( @param1 varchar(50)
, @param2 datetime
, @param3 datetime
) RETURNS TABLE AS
RETURN
(
WITH cte as
(
..
)
SELECT cols
FROM dbo.SomeTVF2(@param1, @param2) t1
LEFT JOIN
( SELECT cols
FROM dbo.SomeTVF2(@param1, @param3)
WHERE ...
) t2 ON t1.PK = t2.PK
There is no special magic but a lot of underlying joins. I cannot show you execution plans as they are rather big (XML plan has 66MB).
I tried executing function within stored procedure using parametrs, but it still takes too much time. I thought that could be a lot of joins to blame, but executing it with values instead of variables/parametrs boost it more than 10 times.
Thanks,
Let's see, you have an inline table valued function calling other table valued functions (possibly also inline table valued functions, but can't tell from the incomplete code provided). You also haven't provided the DDL for any of the talbes that may be queried by these inline table valued functions, nor have you posted the actual execution plan.
So, do you want help or just some wild shots in the dark?
June 18, 2012 at 10:32 am
Duncan:
I thought about parameter sniffing, but all resource I found about it was related to stored procedures. Nevertheless, I tried wrapping it in stored procedure that would do exactly what you wrote and it did not help.
Lynn:
I am not sure if I make it clear - I am quite far away from tunning the function. I cannot provide you
neither code for functions and tables nor execution plans (execution plans are simply too big, as I said one was 66MB big) and, to be honest, I do not think they are crucial for this discussion. My initial question was:
why in SSMS in the same batch in the same connection (so ANSI_NULL and other settings are exactly the same) calling function with values (for an instance 'ABC') is more than ten times faster than calling function with variables (which are set to 'ABC' a line before)?
Today I used IO STATISTICS and noted that call with variables do a lot more table scans (on some tables several thousands more) - I suppose it uses different execution plans, but I do not know why.
Current workaround (that I am not proud of) is something like this:
DECLARE @statement varchar(500);
SET @statement = 'SELECT * FROM [dbo].[SomeTVF](''' + @param1 + ''', ''' + CONVERT(varchar(20), @param2, 120) + ''', ''' + CONVERT(varchar(20), @param3, 120) + ''')'
sp_executeSql @statement
This change lower execution time from 22-26 minutes to 1,5-2 minutes. Going back to your question: I would appreciate a help, but I ran out of ideas so wild shot in the dark would be nice too.
June 18, 2012 at 11:01 am
This is kind of like taking your car to the mechanic and telling them it doesn't run well. Imagine your response at the car repair shop with this conversation.
You - "My car isn't working well."
Mechanic - "what is is doing?"
You - "Running slow?"
Mechanic - "All the time or just when it is hot/cold outside?"
You - "I can't tell you that."
Mechanic - "Does it make any strange noises?"
You - "I can't tell you that."
Mechanic - "Does this happen all the time or just sometimes?"
You - "I can't tell you that."
Mechanic - "Does this happen more frequently on hills or flat ground?"
You - "I can't tell you that."
That is pretty much what is happening here. You have two incredibly talented people trying to help you and you are saying you can't give even the most basic information to help you. Nobody can help you with performance issues if you can't provide details to help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 18, 2012 at 1:53 pm
kharol (6/18/2012)
Duncan:I thought about parameter sniffing, but all resource I found about it was related to stored procedures. Nevertheless, I tried wrapping it in stored procedure that would do exactly what you wrote and it did not help.
I'm not talking about wrapping the function, I'm talking about modifying the function code to use variables to hold the values of the parameters passed to the function. You may not have read about it, but it is real and helped me get a(badly written) query that was running over 30 seconds down to a few tens of milliseconds. You can read about the phenomenon - I think Erland Sommarskog mentions it in one of his excellent articles.
That's my stab in the dark given the information you've provided.
Duncan
June 18, 2012 at 2:00 pm
Duncan Pryde (6/18/2012)
kharol (6/18/2012)
Duncan:I thought about parameter sniffing, but all resource I found about it was related to stored procedures. Nevertheless, I tried wrapping it in stored procedure that would do exactly what you wrote and it did not help.
I'm not talking about wrapping the function, I'm talking about modifying the function code to use variables to hold the values of the parameters passed to the function. You may not have read about it, but it is real and helped me get a(badly written) query that was running over 30 seconds down to a few tens of milliseconds. You can read about the phenomenon - I think Erland Sommarskog mentions it in one of his excellent articles.
That's my stab in the dark given the information you've provided.
Duncan
Curious, how would you declare variables internal to an inline table valued function? Seems to me you would have to convert it to a multi-statement table valued function to do that, and that would make the function slower.
June 18, 2012 at 3:36 pm
Lynn Pettis (6/18/2012)
Duncan Pryde (6/18/2012)
kharol (6/18/2012)
Duncan:I thought about parameter sniffing, but all resource I found about it was related to stored procedures. Nevertheless, I tried wrapping it in stored procedure that would do exactly what you wrote and it did not help.
I'm not talking about wrapping the function, I'm talking about modifying the function code to use variables to hold the values of the parameters passed to the function. You may not have read about it, but it is real and helped me get a(badly written) query that was running over 30 seconds down to a few tens of milliseconds. You can read about the phenomenon - I think Erland Sommarskog mentions it in one of his excellent articles.
That's my stab in the dark given the information you've provided.
Duncan
Curious, how would you declare variables internal to an inline table valued function? Seems to me you would have to convert it to a multi-statement table valued function to do that, and that would make the function slower.
Oops, misread the question there - must've thought it was a multi-statement tvf. I'll get my coat.
June 18, 2012 at 3:51 pm
kharol (6/18/2012)
Duncan:I thought about parameter sniffing, but all resource I found about it was related to stored procedures. Nevertheless, I tried wrapping it in stored procedure that would do exactly what you wrote and it did not help.
Unfortunately the wrong approach, you need to deal with the sniff at each level to avoid the compilation concerns. However, this:
I am not sure if I make it clear - I am quite far away from tunning the function. I cannot provide you neither code for functions and tables nor execution plans (execution plans are simply too big, as I said one was 66MB big) and, to be honest, I do not think they are crucial for this discussion.
Implies that you don't have rights to all the pieces you need to actually correct this issue. Also, if it's saved as a .sqlplan XML, while large it could still be utilized by us, we're used to looking at them. They can help indicate particular places where you need to look that the sniffing broke down so you can start isolating particular pieces. Comparisons between the two (with and without direct values) would be best.
why in SSMS in the same batch in the same connection (so ANSI_NULL and other settings are exactly the same) calling function with values (for an instance 'ABC') is more than ten times faster than calling function with variables (which are set to 'ABC' a line before)?
Today I used IO STATISTICS and noted that call with variables do a lot more table scans (on some tables several thousands more) - I suppose it uses different execution plans, but I do not know why.
Because the compiler will compile differently for direct values instead of creating a common plan for all expected possibilities. What you end up with are two compiled plans in the cache, one that's optimized for particular values, one that's optimized for generic anything. An alternative to the dynamic SQL is to use the OPTIMIZE FOR clause against the Select statement, giving it a 'good set' of hard coded values to work with as its optimization.
This change lower execution time from 22-26 minutes to 1,5-2 minutes. Going back to your question: I would appreciate a help, but I ran out of ideas so wild shot in the dark would be nice too.
One of the problems with shots in the dark is it makes us look like floundering idiots asking a million questions to work around the simple 3 piece rule that makes it very easy to help you. What's the schema, the code (and all called sub-components), and the exec plan? Direct answers to that saves us time (and we're volunteers), everyone frustration, and you from having to flail around with whatever we come up with until it's proven right or wrong. In the worst case scenario even if it doesn't answer the question, it will help rule out a mighty large portion of the wrong answers.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 19, 2012 at 10:58 am
Thank you all of you for your answers. I am sorry I am not able to provide code or exec plans, but I thought they were not relevant for my main question (the different behavior of inline function depending on how it is called). Thanks!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy