Reputation: 2381
This sort of follows on from a previous question:
SQL Server Query time out depending on Where Clause
In which a query would run extremely slowly depending on a where clause. I rewrote that query use CTE and avoided the problem nicely, but never found an answer.
Another, similar, query was tweaked recently to add another field, and suddenly it's performance dropped from taking about ~30 seconds to run and return 10,000 rows, to taking over 10 hours (and eventually returning the same result set). Today I started to troubleshoot this one and found something weird.
I am constantly needing to extract the date only part from datetime values, and so I wrote the logic into a function:
CREATE FUNCTION [dbo].[cDate] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)
END
GO
I found in this new, inefficient query that if I replaced that function with the CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME)
inline in the query, the speed of the query execution dropped from ~10 hours, to just under 2 seconds. I can see no difference in the estimated execution plan. By the way, this was not the field that was added. I assume adding the other field somehow caused the execution plan to change and amplified the above condition.
My question is, is this normal? I make use of functions for repetitive processes, as above, as they are easier to maintain, remember and to update if you find a more efficient way of doing something. Should I be doing something to my functions to improve their performance?
Upvotes: 7
Views: 9660
Reputation: 453287
If you must encapsulate this in a function see Scalar functions, inlining, and performance: An entertaining title for a boring post
Rewrite it as follows
CREATE FUNCTION dbo.cDate_Inline
(
@pInputDate DATETIME
)
RETURNS TABLE
AS
RETURN
(
SELECT DATEADD(day, DATEDIFF(Day, 0, @pInputDate), 0) AS [Date]
)
Then instead of
SELECT *,
[dbo].[cDate](modify_date) AS modified
FROM sys.objects
Use
SELECT *,
ca.Date AS modified
FROM sys.objects
CROSS APPLY dbo.cDate_Inline(modify_date) ca
This will be inlined into the plan by the query optimiser.
Upvotes: 8
Reputation: 13242
I would try possibly adding another variable to do the declaration and return. The thought process for this could be that your cast and converting to alter the datetime for your input may be the bottleneck. This may not be the case but often if you set in the scope a new variable, do your conditioning and bind it to that variable and return that, that may help out with speed. I tend to stay away from scalar functions when possible though, they have lots of performance issues when you start using them with larger data sets. Just a suggestion it may or may not help but it would isolate the return to a new object independent of the input:
CREATE FUNCTION [dbo].[cDate] ( @pInputDate DATETIME )
RETURNS DATETIME
BEGIN
Declare @Output datetime = CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME) -- 2008 method and newer
Declare @Output datetime;
Select @Output = CAST(CONVERT(VARCHAR(10), @pInputDate, 111) AS DATETIME) -- 2005 and prior method
return @Output
END
GO
Upvotes: 0