CODEWITHSUNDEEP
t-sql
Dayton Brown
Dayton Brown

Reputation: 1240

use of variable causes update to run very slow

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

It runs like a champ. About 2 seconds. Then when try and make it dynamic via 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

All of a sudden, it's super slow.

any good ideas?

EDIT: Probalby should have mentioned this, but this is contained in a stored proc.

Added the @StartWeekKey as a parameter to the proc and it goes back to running in a few seconds.

Upvotes: 4

Views: 97

Answers (2)

Jeremy Goodell
Jeremy Goodell

Reputation: 18962

This question seems to have been asked several times before and the general answer is that it has to do with statistics.

Try:

UPDATE STATISTICS table_or_indexed_view_name

to get your statistics up to date and see if that makes a difference.

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1062895

That isn't unheard of when different parameters have very different distributions, thus different good plans. What can happen is that the query gets executed for a given value, and then that plan gets cached and re-used inappropriately for a different value.

If this is the case (just a guess - I can't run your query to check!) then try adding:

OPTION (OPTIMIZE FOR (@StartWeekKey UNKNOWN))

to the end of the query.

Another thought: is WeekKey actually an int ? is this some kind of mass type conversion issue?

I have no way of checking these; if I'm miles off the track, let me know so I can remove an unhelpful answer.

Upvotes: 0

Related Questions