Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
4  
yes, it is well known. search for scalar valued functions. It's RBAR... – Mitch Wheat Dec 3 '12 at 5:40
What version of SQL Server are you on? Maybe a CAST(@pInputDate AS DATE) would be much more efficient (which would work on 2008 or newer) ... – marc_s Dec 3 '12 at 6:50
Unfortunately I am stuck on 2005 at the moment, so this isn't an option. – Molloch Dec 3 '12 at 11:18
Thanks Mitch. Looks like I have some redesigning to do... – Molloch Dec 3 '12 at 11:19

1 Answer

up vote 0 down vote

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.