Simple yet problematic update query

Question

I have a rather simple update/query, that has been causing me a lot of grief over the years.

in simplest form it is :

update VillageSemaphore
set TimeStamp = getdate() 
        where VillageID in (@X, @Y)

However, in some stored procs, the query also include this "OR VillageID in (...)" subquery

update VillageSemaphore
set TimeStamp = getdate() 
        where VillageID in (@X, @Y)

        OR VillageID in  ( -- this subquery can return many rows, many different VillageIDs
        select VSU.SupportingVillageID 
        from VillageSupportUnits VSU
        where SupportedVillageID = @Z       
            and VSU.UnitCount <> 0
            )

Note that this OR, can return many villageIDs, not just one, @Z. This version of the query, sometimes runs a very long time. No index rebuild, stats rebuild helps. It runs slowly when the contents of Villages table is deleted and repopulated. In this case, the row count would be just a few hundred rows. I never figured out why this is, and always just lived with it.

However, recently I was looking at the query plan:

It seems that estimated number of rows (4000) is huge in comparison with the actual number of rows (2).

I created this statistic but it does not help

CREATE STATISTICS [stat_x] ON [VillageSU]([UnitCount], [VillageID])

SO MY QUESTION : any suggestions why this could be and what I could do to improve this ?

for reference, the table looks like this :

CREATE TABLE VillageSemaphore(
    VillageID    int         NOT NULL,
    TimeStamp    datetime    NOT NULL,
    CONSTRAINT PK97 PRIMARY KEY CLUSTERED (VillageID)
)

UPDATE: Trying out this version of the query as suggested by srutzky

CREATE TABLE #VillagesToLock (VillageID INT NOT NULL);
insert into #VillagesToLock values (@X)
insert into #VillagesToLock values (@Y)
insert into #VillagesToLock select VSU.SupportingVillageID 
        from VillageSupportUnits VSU
        where SupportedVillageID = @Z       
            and VSU.UnitCount <> 0

update VillageSemaphore set TimeStamp = getdate() 
    where VillageID in (select VillageID from #VillagesToLock)

this is the result so far : http://screencast.com/t/96KafTPoNGM - query plan does look better.

The cost of the query went down from 3% to 1% also, so that seems good. 3% may not seem like much, but this is a 2500 line stored proc!

QUESTION: I cannot make #VillagesToLock.VillageID a PK since it is not unique. And I expect the #VillagesToLock to be typically no more than 2-10 rows. VillageSemaphore could be many thousands of rows. Is it worth putting an index on #VillagesToLock in this case ?

UPDATE NOV 24 I have implemented this alternative

The query plan does look a lot nicer

Thank you to all who too the time to help me out!

Answer 1

Alternatively, you could also just create a local temporary table such that the UPDATE would be using an INNER JOIN:

CREATE TABLE #VillageIDsToUpdate (VillageID INT NOT NULL PRIMARY KEY);

INSERT INTO #VillageIDsToUpdate (VillageID) VALUES (@X);
INSERT INTO #VillageIDsToUpdate (VillageID) VALUES (@Y);
IF (@Z IS NOT NULL)
BEGIN
  INSERT INTO #VillageIDsToUpdate (VillageID)
    SELECT SUVillageID
    FROM   VillageSU
    WHERE  VillageID = @Z;
END;

UPDATE vs
SET    vs.TimeStamp = GETDATE()
FROM   VillageSemaphore vs
INNER JOIN #VillageIDsToUpdate ids
        ON ids.VillageID = vs.VillageID;

UPDATE:

I just thought of something that might help with making the filtering of duplicates more efficient: how about using the IGNORE_DUP_KEY setting on the PK? For example:

CREATE TABLE #VillageIDsToUpdate (VillageID INT NOT NULL PRIMARY KEY
                                            WITH (IGNORE_DUP_KEY = ON));

If you do that, then the following works as desired:

INSERT INTO #VillageIDsToUpdate (VillageID) VALUES (1);
INSERT INTO #VillageIDsToUpdate (VillageID)
  SELECT tmp.val
  FROM   (VALUES (1), (2), (3), (3)) tmp(val);

SELECT * FROM #VillageIDsToUpdate;

Returns:

VillageID
---------
1
2
3

And that means that you can do the INSERT statements as I have suggested above without needing to add the DISTINCT or do any secondary query to remove duplicates :-).

Answer 2

While I'm not convinced this is a problem with the query itself (did you check for blocking when it runs slow? did you check the wait type(s) occurring while it was running), IN and OR can be a problematic pattern to optimize for. Have you considered breaking this into multiple statements?

UPDATE dbo.VillageSemaphoreset 
  SET [TimeStamp] = GETDATE() -- TimeStamp is a terrible column name btw 
  WHERE VillageID = @X;

UPDATE dbo.VillageSemaphoreset 
  SET [TimeStamp] = GETDATE()
  WHERE VillageID = @Y;

IF (whatever condition leads you to "sometimes add this OR")
BEGIN
  UPDATE v 
    SET [TimeStamp] = GETDATE()
    FROM dbo.VillageSemaphoreset AS v
    WHERE VillageID = @Z
    AND EXISTS 
    (
      SELECT 1 FROM dbo.VillageSU AS vs
      WHERE vs.VillageID = v.VillageID
    );
END

This may solve the estimation problem, but I agree with Max, a statistic with a leading column of UnitCount won't help the estimates for these queries anyway.

Answer 3

Do you have a key/index on SUVillageID in VillageSU? If not, you'll want to add that. Also, have you tried this yet:

with ctesuv as
    (select SUVillageID as VillageID
        from VillageSU 
        where VillageID = @Z -- if @Z itself is a list of values, you want an `IN` here
    ),
update VillageSemiphore
    set [TimeStamp] = getdate() 
        where VillageID in (@X, @Y, (select * from ctesuv))

note: TimeStamp is a reserved keyword in Access and may be in SQL server too.

or

update VillageSemiphore
SET [TimeStamp] = getdate()
   WHERE VillageID in (@X,@Y) OR 
        EXISTS (SELECT 1 FROM (
            select SUVillageID from VillageSU 
            where VillageID = @Z)
        )

how about

update VillageSemiphore as VS
SET VS.[TimeStamp] = getdate()
    WHERE EXISTS (SELECT 1 FROM (
        SELECT TOP 1
            VS.VillageID, SU.SUVillageID
        FROM VillageSU as SU
        WHERE
            (SU.VillageID = @Z AND SU.SUVillageID = VS.VillageID)
            OR (VS.VillageID IN (@X,@Y)) -- this would cross-join to all rows in SU...the top1 may limit that, but you may need some alternate logic here. I'll have to think a bit more about this
    )