Help to improve this update statment that takes 5:43 hours
- Avoid NOT IN where the searched column is nullable
- Always use aliases for your tables
- Always use the table aliases on column references
Point #1 is very important. It is the primary reason for the poor performance.
Try the following:
DECLARE @Date1 datetime = DATEADD(SECOND, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) -1, 0)); DECLARE @Date2 datetime = DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @Date1), 0)), @Date3 datetime = DATEADD(MONTH, DATEDIFF(MONTH, -1, @Date1) - 1, -1); SELECT @Date1, @Date2, @Date3; UPDATE FB WITH (TABLOCKX) SET NewServiceFlag = 1 WHERE FB.NewServiceFlag IS NULL AND FB.Service_Date >= @Date1 AND NOT EXISTS ( SELECT 1 FROM dbo.FactBilling AS e WHERE e.Service_Key = FB.Serice_Key AND e.Service_Date >= @Date2 AND e.Service_Date < @Date3 ) AND EXISTS ( SELECT 1 FROM dbo.FactBilling AS e WHERE e.Service_Key = FB.Serice_Key AND e.Service_Date < @Date2 ) FROM DWH.dbo.FactBilling AS FB OPTION (RECOMPILE);
You can also try reordering the EXISTS and NOT EXISTS clauses there, depending on your data distribution, one may be better than the other.
Your (partitioned) table should have an index on:
CREATE INDEX dbo_FactBilling__Serivce_Key_Service_Date ON dbo.FactBilling (Serivce_Key, Service_Date);
It seems likely you already do (index Service_Key), but it is worth checking. This index should also be partitioned (doesn't seem to be currently). The order of the index keys is important.
You may also benefit from a filtered index:
CREATE INDEX give_me_a_proper_name ON dbo.FactBilling (Service_Date) INCLUDE ( Episode_Key, NewServiceFlag, Organization_Key, Service_Key ) WHERE NewServiceFlag IS NULL;
Again, partitioned. Check the logic of my query is correct – I make no promises without a database to work on 🙂