Using variable reasons for updating is very slow

Question

I have the following query.

UPDATE t SET UnitsSold = sub.UnitsSold, FROM dbo.table1 t JOIN (SELECT s.CustomerKey, s.WeekKey, s.ProductKey, Sum(s.UnitsSold) AS [UnitsSold], FROM dbo.table2 s WHERE WeekKey >= 335 GROUP BY s.WeekKey, s.CustomerKey, s.ProductKey) AS sub ON sub.WeekKey = t.WeekKey AND sub.CustomerKey = t.CustomerKey AND sub.ProductKey = t.ProductKey 

He works as a champion. About 2 seconds. Then, when you try to make it dynamic using the following.

  DECLARE @StartWeekKey AS INT SET @StartWeekKey = 335 UPDATE t SET UnitsSold = sub.UnitsSold, FROM dbo.table1 t JOIN (SELECT s.CustomerKey, s.WeekKey, s.ProductKey, Sum(s.UnitsSold) AS [UnitsSold], FROM dbo.table2 s WHERE WeekKey >= @StartWeekKey GROUP BY s.WeekKey, s.CustomerKey, s.ProductKey) AS sub ON sub.WeekKey = t.WeekKey AND sub.CustomerKey = t.CustomerKey AND sub.ProductKey = t.ProductKey 

Suddenly, it is very slow.

any good ideas?

EDIT: Probalby should have mentioned this, but this is contained in the stored procedure.

The @StartWeekKey parameter has been added as a parameter for proc, and it returns to launch in a few seconds.

Answer 1

This question seems to have been asked several times earlier, and the general answer is that it is related to statistics.

Try:

 UPDATE STATISTICS table_or_indexed_view_name 

to update statistics and see does not matter.

Answer 2

This is not unheard of when different parameters have very different distributions, thus different good plans. It may happen that the request is executed for a given value, and then this plan will be cached and reused incorrectly for another value.

If so (just a hunch - I can't run your query to check!), Try adding:

 OPTION (OPTIMIZE FOR (@StartWeekKey UNKNOWN)) 

until the end of the request.

---

Another thought: WeekKey is actually a int ? Is this some kind of mass type conversion problem?

---

I have no way to test them; if I'm miles away from the track let me know so that I can remove the useless answer.