Nsryjdtyk

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 <= 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 > 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 <= MONTH(GETDATE())  OR birth_month IS NULL then (YEAR(GETDATE()) - birth_year)

                        when birth_month > MONTH(GETDATE()) then ( YEAR(GETDATE()) - birth_year - 1 )

                        else null

                     end

FROM    table_name

As per my understanding, you are looking for current age for each records. But for that, you should go ahead with datedifference function by year. no need to calculate this much operation.

Make sure you should have index on below columns.

birth_month , birth_year

After all, please check and let us know the execution time readings.