Why is this UPDATE statement on varbinary(max) on SQL Server so slow?

Question

I have a table S with only 500 rows, and a table F with 120000 rows. Both use GUID primary keys and table F holds a foreign key to table S. Table F contains a varbinary(max) column F.Data with about 100 KB per row (total database size is about 10 GB). Filestream is turned on. I'm using SQL Server 2014 Express.

When I make the following UPDATE statement (in SQL Server Management Studio), which affects approximately 100000 rows

UPDATE F
SET F.Data = 0
FROM F
INNER JOIN S
ON S.SID = F.SID
WHERE S.BITFIELD = 1 AND S.Date < DATEADD(DAY,-90,GETDATE())

the query takes around 30 minutes. That's rather unacceptable but I don't know enough about SQL to know why or how to make this query more efficient. Any gurus out there who can help?

FYI, the equivalent SELECT statement takes only a few seconds. I've searched around Stackoverflow and elsewhere, and haven't found anything particularly helpful (given my limited knowledge of SQL).

Answer 1

Have you tried creating a temp table with just one field,( S.SID ) and all the records that match with WHERE S.Date < DATEADD(DAY,-90,GETDATE()) then join to it in your UPDATE, rather than computing in the where clause during the update?

Also, Index on GUID may not be as good as using index on INT. read this GUID vs INT IDENTITY Good luck.

Something like this :

CREATE TABLE [#TEMPTBL1]([SID] uniqueidentifier);
CREATE CLUSTERED INDEX IDX_TEMPTBL1_SID ON [#TEMPTBL1]([SID]);  
INSERT INTO [#TEMPTBL1]([SID])
            SELECT ([SID]) FROM S 
            WHERE S.BITFIELD = 1 
            AND S.Date < DATEADD(DAY,-90,GETDATE());


UPDATE F
SET F.Data = 0
FROM F
INNER JOIN #TEMPTBL1 TMP ON F.SID = TMP.SID

DROP TABLE #TEMPTBL1;

---------- code update with counter --------

DECLARE @updtCounter int = 0;

CREATE TABLE [#TEMPTBL1]([SID] uniqueidentifier);
CREATE CLUSTERED INDEX IDX_TEMPTBL1_SID ON [#TEMPTBL1]([SID]);  
INSERT INTO [#TEMPTBL1]([SID])
            SELECT ([SID]) FROM S 
            WHERE S.BITFIELD = 1 
            AND S.Date < DATEADD(DAY,-90,GETDATE());

SELECT @updtCounter = count(*) FROM F
INNER JOIN #TEMPTBL1 TMP ON F.SID = TMP.SID

UPDATE TOP (@updtCounter) F
SET F.Data = 0
FROM F
INNER JOIN #TEMPTBL1 TMP ON F.SID = TMP.SID

DROP TABLE #TEMPTBL1;