July 19, 2010 at 3:09 am
Do date UDFs have an adverse effect on indexed tables?
July 19, 2010 at 3:16 am
Depends how they're used.
Can you be a little more expansive on the question please.
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
July 19, 2010 at 3:31 am
The function below is used within a query to get the last 10 days data. The query runs very slow even though there are indexes on the table. Any ideas?
CREATE FUNCTION [dbo].[fn_ConvertToBusinessDate] (@Date DATETIME) RETURNS DATETIME AS
BEGIN
DECLARE @RollOverTime DATETIME
SELECT @RollOverTime = [RollOverTime]
FROM [dbo].[ac_Estate]
SELECT @Date = CONVERT(VARCHAR(8), @Date-@RollOverTime, 112)
RETURN @Date
END
GO
July 19, 2010 at 3:48 am
Post the query?
If you're using that on a column of the table, you're preventing index seeks. That goes for UDFs and most built-in functions as well. Using a function on a column in a where clause predicate makes the predicate non-SARGable.
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
July 21, 2010 at 11:03 am
Joe Celko (7/19/2010)
>> The function below is used within a query to get the last 10 days data. The query runs very slow even though there are indexes on the table. Any ideas? <<Why not just write a query? SQL is a declarative language and you should almost never write a UDF in your career. They are evil like cursors and loops. Here is a skeleton:
CREATE PROCEDURE Last_Ten_Days (..)
AS
BEGIN
..
SELECT ..
FROM Something AS S
WHERE S.vague_date BETWEEN CURRENT_TIMESTAMP
AND DATE (dd, -10, CURRENT_TIMESTAMP)
..
END;
You might want to get a copy of THINKING IN SETS and see if it helps get you into a set-oriented mindset.
Wouldn't this:
SELECT ..
FROM
Something AS S
WHERE
S.vague_date BETWEEN CURRENT_TIMESTAMP AND DATE (dd, -10, CURRENT_TIMESTAMP)
..
END;
Be this in T-SQL:
SELECT ..
FROM
Something AS S
WHERE
S.vague_date BETWEEN DATEADD(dd, -10, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
..
END;
Last I checked BETWEEN in T-SQL actually depended on the lower bound being listed first.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply