Replacing birth year column values with calculated age value I have a stored procedure that is taking more than 2 hours to complete and I noticed the below SQL is taking close to 25 mins for updating a million+ values.  There are multiple steps like this and all added up takes around 2+ hours to finish.

All it is doing is taking a column value i.e. birth_year and replacing it with age i.e. getdate() - birth_year.

SELECT     id,
           birth_month,
           birth_year
INTO       #birth_date
FROM       table_name
WHERE      birth_year IS NOT NULL

UPDATE     table_name
SET        birth_year = ( YEAR(GETDATE()) - bd.birth_year )
FROM       #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE      bd.birth_month &lt;= MONTH(GETDATE())
           OR bd.birth_month IS NULL

UPDATE     table_name
SET        birth_year = ( YEAR(GETDATE()) - bd.birth_year - 1 )
FROM       #birth_date bd
INNER JOIN table_name ap ON ap.id= bd.id
WHERE      bd.birth_month &gt; MONTH(GETDATE())


I replaced the above SQL with the below SQL and now instead of 25 mins, the below SQL takes less than a minute. I've verified the results and all seems good.  Is there anything that I am missing? Is there a better or alternate way?

UPDATE  table_name
SET     birth_year = case
                        when birth_month &lt;= MONTH(GETDATE())  OR birth_month IS NULL then (YEAR(GETDATE()) - birth_year)
                        when birth_month &gt; MONTH(GETDATE()) then ( YEAR(GETDATE()) - birth_year - 1 )
                        else null
                     end
FROM    table_name
First, as dnoeth commented, why are you doing this job in mass rather than calculating on the fly? You might have a good reason, but you never clarified.  Of course the best way to lighten the load would be not to do this task at all.

Second, as 200_success commented, you are damaging your database design by writing age data into a birth_year column.

If you MUST run this procedure and you MUST overwrite the birth_year, then I have some advice for your single-query approach:


You should consistently use all caps when writing mysql keywords and functions for readability.
You should try to minimize the number function calls and overall length of your query.
Because, I assume, you have covered all potential outcomes with your table data in your CASE conditions, there should be no logical requirement for the null outcome.  Furthermore, because there are only 2 outcomes, I would opt for an IF statement rather than the more verbose CASE statement.
Depending on the possibility of a null value in birth_year, you may need to include another IF or COALESCE call to mitigate that data challenge.


SQL (D.R.Y.):

UPDATE table_name
SET birth_year = YEAR(GETDATE()) - birth_year - IF(birth_month IS NULL OR birth_month &lt;= MONTH(GETDATE()), 0, 1) 
FROM table_name