I need to update table from a certain population.

The code:

@IdentityNumbers dbo.StringList readonly

update d
   set DeliveryNationalInsuranceBatchLogId = @BatchLogId,
  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?


Ultimately, to get to the bottom of this, you need to analyze the execution plan, make sure statistics are up to date and indexes on the necessary (join) columns are available and de-fragmented.

Before executing a query, SQL Server needs to have an execution plan in place. If query executes for the first time, SQL Server creates a new plan and saves it. On the other hand, if the same query executed before, SQL Server fetches and reuses the existing (saved) plan.

In this specific case, I believe SQL Server created an execution plan which was optimized for a much smaller number of rows (10k) and then when the number of rows increased to 500k, that execution plan became sub-optimal which lead to poor performance.

If the number of rows is changing like this regularly (between 10k and 500k), one way of dealing with this would be to add OPTION(RECOMPILE) at the end of the query to force SQL Server to regenerate execution plan before each execution: Improving query performance with OPTION (RECOMPILE), Constant Folding and avoiding Parameter Sniffing issues

| improve this answer | |
  • How am I need to do that? this a code? where write it? – Ayal May 8 '19 at 9:44
  • Add this hint at the end of your query. Follow the link I provided and you should find an example there. – knyazs May 8 '19 at 11:25