This question already has an answer here:
I need to update table from a certain population.
@IdentityNumbers dbo.StringList readonly update d set DeliveryNationalInsuranceBatchLogId = @BatchLogId, LastUpdateDate=getdate(), LastUpdateUserId=-999 from MortgageDetails d join MortgageReturnParticipationBase b on b.Id = d.MortgageReturnParticipationBaseId join @IdentityNumbers newRow on (newRow.item = b.IdentityNumber or newRow.item = b.MateIdentityNumber) where (DATEADD(YEAR,70,BirthDate) <= getdate() or (MateBirthDate is null or DATEADD(YEAR,70,MateBirthDate) <= getdate())) and b.CheckWithNationalInsurance=1 and d.IsActive=1 and b.MortgageReturnParticipationStatusId=1
I did it and it takes for a population of about 10,000 board size in just a few seconds to a minute, and for a population of about 500,000 it takes over an hour ...
I think the reason is that this StringList is what slows down the pace ...
Could this really be the problem? Is there a solution?