Help to improve this update statment that takes 5:43 hours

amr massoud 2016-04-26 15:07:28

SQLkiwi 2016-04-26 20:04:44
My first pieces of advice would be:

  1. Avoid NOT IN where the searched column is nullable
  2. Always use aliases for your tables
  3. 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 🙂

amr massoud 2016-04-27 10:53:07
THANKS ALOT SQLKIWI my query time down to 6.5x more fast your rewrite query and index dbo_FactBilling__Serivce_Key_Service_Date makes query better with execution thanks thanks thanks my optimizer friend 🙂
April 26, 2016
No Comments