如何提高SQL脚本性能

debugcn 发表于 Dev

去做

以下脚本在运行时非常慢。

我不知道如何提高脚本的性能。即使有一个视图也要花费很多时间。有什么想法请分享给我。

SELECT DISTINCT
        ( id )
FROM    ( SELECT DISTINCT
                    ct.id AS id
          FROM      [Customer].[dbo].[Contact] ct
                    LEFT JOIN [Customer].[dbo].[Customer_ids] hnci ON ct.id = hnci.contact_id
          WHERE     hnci.customer_id IN (
                    SELECT DISTINCT
                            ( [Customer_ID] )
                    FROM    [Transactions].[dbo].[Transaction_Header]
                    WHERE   actual_transaction_date > '20120218' )
          UNION
          SELECT DISTINCT
                    contact_id AS id
          FROM      [Customer].[dbo].[Restaurant_Attendance]
          WHERE     ( created > '2012-02-18 00:00:00.000'
                      OR modified > '2012-02-18 00:00:00.000'
                    )
                    AND ( [Fifth_Floor_London] = 1
                          OR [Fourth_Floor_Leeds] = 1
                          OR [Second_Floor_Bristol] = 1
                        )
          UNION
          SELECT DISTINCT
                    ( ct.id )
          FROM      [Customer].[dbo].[Contact] ct
                    INNER JOIN [Customer].[dbo].[Wifinity_Devices] wfd ON ct.wifinity_uniqueID = wfd.[CustomerUniqueID]
                                                              AND startconnection > '2012-02-17'
          UNION
          SELECT DISTINCT
                    comdt.id AS id
          FROM      [Customer].[dbo].[Complete_dataset] comdt
                    LEFT JOIN [Customer].[dbo].[Aggregate_Spend_Counts] agsc ON comdt.id = agsc.contact_id
          WHERE     agsc.contact_id IS NULL
                    AND ( opt_out_Mail <> 1
                          OR opt_out_email <> 1
                          OR opt_out_SMS <> 1
                          OR opt_out_Mail IS NULL
                          OR opt_out_email IS NULL
                          OR opt_out_SMS IS NULL
                        )
                    AND ( address_1 IS NOT NULL
                          OR email IS NOT NULL
                          OR mobile IS NOT NULL
                        )
          UNION
          SELECT DISTINCT
                    ( contact_id ) AS id
          FROM      [Customer].[dbo].[VIP_Card_Holders]
          WHERE     VIP_Card_number IS NOT NULL
        ) AS tbl

瓦西里·伊沃扎（Vasily Ivoyzha）

试试这个，临时表应该可以帮助您：

    IF OBJECT_ID('Tempdb..#Temp1') IS NOT NULL 
        DROP TABLE #Temp1

    --Low perfomance because of using "WHERE  hnci.customer_id IN ( .... ) " - loop join must be
    --and this "where" condition will apply to two tables after left join, 
    --so result will be same as with two inner joints but with bad perfomance

    --SELECT DISTINCT
    --        ct.id AS id
    --INTO    #temp1
    --FROM    [Customer].[dbo].[Contact] ct
    --        LEFT JOIN [Customer].[dbo].[Customer_ids] hnci ON ct.id = hnci.contact_id
    --WHERE   hnci.customer_id IN (
    --        SELECT DISTINCT
    --                ( [Customer_ID] )
    --        FROM    [Transactions].[dbo].[Transaction_Header]
    --        WHERE   actual_transaction_date > '20120218' )    
    --------------------------------------------------------------------------------
    --this will give the same result but with better perfomance then previouse one
    --------------------------------------------------------------------------------
    SELECT DISTINCT
            ct.id AS id
    INTO    #temp1
    FROM    [Customer].[dbo].[Contact] ct
            JOIN [Customer].[dbo].[Customer_ids] hnci ON ct.id = hnci.contact_id
            JOIN ( SELECT DISTINCT
                            ( [Customer_ID] )
                   FROM     [Transactions].[dbo].[Transaction_Header]
                   WHERE    actual_transaction_date > '20120218'
                 ) T ON hnci.customer_id = T.[Customer_ID]
    --------------------------------------------------------------------------------
    --------------------------------------------------------------------------------              
    INSERT  INTO #temp1
            ( id
            )
            SELECT DISTINCT
                    contact_id AS id
            FROM    [Customer].[dbo].[Restaurant_Attendance]
            WHERE   ( created > '2012-02-18 00:00:00.000'
                      OR modified > '2012-02-18 00:00:00.000'
                    )
                    AND ( [Fifth_Floor_London] = 1
                          OR [Fourth_Floor_Leeds] = 1
                          OR [Second_Floor_Bristol] = 1
                        )
    INSERT  INTO #temp1
            ( id
            )
            SELECT DISTINCT
                    ( ct.id )
            FROM    [Customer].[dbo].[Contact] ct
                    INNER JOIN [Customer].[dbo].[Wifinity_Devices] wfd ON ct.wifinity_uniqueID = wfd.[CustomerUniqueID]
                                                                  AND startconnection > '2012-02-17'
    INSERT  INTO #temp1
            ( id
            )
            SELECT DISTINCT
                    comdt.id AS id
            FROM    [Customer].[dbo].[Complete_dataset] comdt
                    LEFT JOIN [Customer].[dbo].[Aggregate_Spend_Counts] agsc ON comdt.id = agsc.contact_id
            WHERE   agsc.contact_id IS NULL
                    AND ( opt_out_Mail <> 1
                          OR opt_out_email <> 1
                          OR opt_out_SMS <> 1
                          OR opt_out_Mail IS NULL
                          OR opt_out_email IS NULL
                          OR opt_out_SMS IS NULL
                        )
                    AND ( address_1 IS NOT NULL
                          OR email IS NOT NULL
                          OR mobile IS NOT NULL
                        )
    INSERT  INTO #temp1
            ( id
            )
            SELECT DISTINCT
                    ( contact_id ) AS id
            FROM    [Customer].[dbo].[VIP_Card_Holders]
            WHERE   VIP_Card_number IS NOT NULL

    SELECT DISTINCT
            id
    FROM    #temp1 AS T

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。