I have an application in which I updated a status flag based on whether
items in the table have been invoiced. I was using an UPDATE statement
similar to the following:
UPDATE MyData SET StatCode = 'I' WHERE MyDataID IN
(SELECT DISTINCT MyDataID FROM Invoiced WHERE InvStatus = 'Y')
Due to the sizes of the tables involved I was having problems with timeouts,
so I decided to try breaking the subselect out into an explicit temporary
table. After all, that's more or less what SQL Server is doing anyway.
I now have something similar to the following:
SELECT DISTINCT MyDataID INTO #temptbl FROM Invoiced WHERE InvStatus = 'Y'
UPDATE MyData SET StatCode = 'I'
FROM MyData m, #temptbl tpt
WHERE m.MyDataID = tpt.MyDataID
DROP TABLE #temptbl
I'm seeing a marked improvement (i.e., no time-outs) but I wondered if there
were any pitfalls I might be overlooking.
I'll appreciate any feedback on this subject. The approach looks so simple
and reasonable that I can't help looking for the catch.
Richard A. Polunsky