get age function

[jepatte]

I have a table with 2.5 mil records and one field is their birthdate. I want to run queries based on their age. I have a GetAge function I am using that accepts their birthdate and another "as of" date parameter (the current date most of the time).

I have an index on this birthdate field and can search for ie. everyone with a birthdate of 1/1/1975 and the results come back within 10 seconds. But a search of "where dbo.getage(birthdate, getdate()) = 32" takes 10-15 minutes.

Here is the function....

//%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
CREATE FUNCTION [dbo].[GETAGE] ( @pDateOfBirth DATETIME, @pAsOfDate DATETIME )
RETURNS INT
AS
BEGIN

DECLARE @vAge INT

IF @pDateOfBirth >= @pAsOfDate
RETURN 0

SET @vAge = DATEDIFF(YY, @pDateOfBirth, @pAsOfDate)

IF MONTH(@pDateOfBirth) > MONTH(@pAsOfDate) OR
(MONTH(@pDateOfBirth) = MONTH(@pAsOfDate) AND
DAY(@pDateOfBirth) > DAY(@pAsOfDate))
SET @vAge = @vAge - 1

RETURN @vAge
END
//%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

 

[SQLDenis]

What did you expect? A function call in a WHERE clause is not sargable!
put the logic in the WHERE clause itself

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

So in order to answer the where clause it has to calculate the age for every single record in the table, how would you ever expect this to be fast?

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

As a bandaid patch, throw in a limiter clause which reduces the number of rows that have to have the exact age calculated:

WHERE
   dbo.getage(birthdate, getdate()) = 32
   AND birthdate BETWEEN DateAdd(yy, -33, getdate()) AND DateAdd(yy, -31, getdate())

This may not be the "best" solution...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]