是否有可能改进此存储过程

Question

我有一个存储过程，虽然工作正常，但使用了大量的临时表，因此性能下降。是否有可能改进/重写它(可以使用CTE或其他方式来提高性能)

CREATE PROCEDURE [dbo].[PORT_GetFutureOpportunities] 
    -- Add the parameters for the stored procedure here
    (
        @SiebelAccId VARCHAR(50),
        @FromDate DATETIME,
        @ToDate DATETIME,
        @FilterCriteria INT,
        @AutoRenewalChk INT
    )
AS
BEGIN 

        DECLARE @FDate DATETIME, @TDate DATETIME 
        SET @FDate = DATEADD(day, DATEDIFF(day, 0, @FromDate), 0)
        SET @TDate = DATEADD(day, DATEDIFF(day, 0, @ToDate), 0)


        SELECT  DISTINCT e.nai_grant_number
        INTO #temp
        FROM smbecommerce..SalesItem si 
        INNER JOIN smbecommerce..sales s ON s.sales_id = si.sales_id
        INNER JOIN siebelextract..entitlement e ON SUBSTRING(e.nai_grant_number, 1,  NULLIF(CHARINDEX('-', e.nai_grant_number) - 1, -1)) = CAST (s.sales_order_id AS VARCHAR)        
        WHERE  si.auto_renewal_flag = (CASE WHEN @AutoRenewalChk = 1 THEN 1 END )
        OR si.auto_renewal_flag <= ( CASE WHEN @AutoRenewalChk = 0 THEN 1 END)

            --Creating Main query
           SELECT 
           e.nai_agreement_account_name AS [CompanyName]
          ,c.first_name  + ' '  + c.last_name AS [ContactName]
          ,c.work_phone AS [ContactPhone]
          ,c.email_address AS [EmailAddress]
          ,e.entitlement_end_date AS [ExpirationDate]
          ,e.nai_grant_number AS [GrantNumber]
          ,e.nai_quantity AS [Quantity]
          ,e.product_name AS [SkuDescription]              
          ,(CASE WHEN LEN(e.nai_superceded_id) > 0 THEN 1 ELSE 0 END)  AS [IsRenewed]

          INTO #temp1                  
          FROM siebelextract..entitlement e     
          INNER JOIN siebelextract..account a ON a.row_id = e.nai_reseller_id           
          INNER JOIN SiebelExtract.[dbo].contact c WITH (NOLOCK) ON e.nai_primary_contact_id = c.row_id 
          WHERE a.parent_account_id = @SiebelAccId
          AND LEN(E.nai_reason_code) = 0
          AND EXISTS (SELECT 1 FROM smbecommerce..renewalskus rs WHERE RS.sku = E.product_name)
          AND e.entitlement_end_date 
          BETWEEN @FDate         
          AND @TDate   

          IF (@AutoRenewalChk = 0 OR @AutoRenewalChk = 1)
          BEGIN 
                  CREATE TABLE #temp2(
                                       CompanyName VARCHAR(200)
                                      ,ContactName VARCHAR(200)
                                      ,ContactPhone VARCHAR(200)
                                      ,EmailAddress VARCHAR(200)
                                      ,ExpirationDate DATETIME
                                      ,GrantNumber VARCHAR(200)
                                      ,Quantity INT
                                      ,SkuDescription VARCHAR(200)
                                      ,IsRenewed INT
                                     ) 
                 IF @AutoRenewalChk = 0
                 BEGIN  
                 INSERT INTO #temp2 select #temp1.* FROM #temp1       
                 END
                 IF @AutoRenewalChk = 1
                 BEGIN     
                 INSERT INTO #temp2 SELECT * FROM #temp1  WHERE #temp1.[GrantNumber] NOT IN(SELECT t1.nai_grant_number FROM #temp t1)     
                 END                       
                 END    

                IF @FilterCriteria = 0
                BEGIN 
                    SELECT te.[CompanyName],te.[ContactName],te.[EmailAddress],te.[ContactPhone],te.[SkuDescription],te.[Quantity],te.[GrantNumber],te.[ExpirationDate],te.[IsRenewed] FROM #temp2 te      
                END

                IF @FilterCriteria = 1
                BEGIN 
                    SELECT te.[CompanyName],te.[ContactName],te.[EmailAddress],te.[ContactPhone],te.[SkuDescription],te.[Quantity],te.[GrantNumber],te.[ExpirationDate],te.[IsRenewed]   FROM #temp2 te
                    WHERE te.[IsRenewed] > 0
                END         

                IF @FilterCriteria = 2
                BEGIN 
                  SELECT te.[CompanyName],te.[ContactName],te.[EmailAddress],te.[ContactPhone],te.[SkuDescription],te.[Quantity],te.[GrantNumber],te.[ExpirationDate],te.[IsRenewed] FROM #temp2 te
                  WHERE te.[IsRenewed] = 0
                END
                DROP TABLE #temp2
                DROP TABLE #temp1
                DROP TABLE #temp

END

GO

谢谢

Answer 1

我已经压缩了你的存储过程。当然，我没有你的数据库，所以我的SSMS看起来像是一场血河之战，但根据你提供的代码，这应该是可行的。看一看，请让我知道，无论是哪种方式。

我将首先粘贴代码，然后解释我做了什么。

CREATE PROCEDURE dbo.PORT_GetFutureOpportunities (
    @SiebelAccId VARCHAR(50),
    @FromDate DATETIME,
    @ToDate DATETIME,
    @FilterCriteria INT,
    @AutoRenewalChk INT
)
AS
BEGIN 
    DECLARE @FDate DATETIME, @TDate DATETIME 
    SET @FDate = CAST(@FromDate AS DATE)
    SET @TDate = CAST(@ToDate AS DATE)

    CREATE TABLE #temp(
        CompanyName VARCHAR(200),
        ContactName VARCHAR(200),
        ContactPhone VARCHAR(200),
        EmailAddress VARCHAR(200),
        ExpirationDate DATETIME,
        GrantNumber VARCHAR(200),
        Quantity INT,
        SkuDescription VARCHAR(200),
        IsRenewed INT
    ) 

    WITH cte AS (SELECT DISTINCT e.nai_grant_number    FROM smbecommerce..SalesItem si 
            INNER JOIN smbecommerce..sales s ON s.sales_id = si.sales_id
            INNER JOIN siebelextract..entitlement e ON SUBSTRING(e.nai_grant_number, 1,  NULLIF(CHARINDEX('-', e.nai_grant_number) - 1, -1)) = CAST (s.sales_order_id AS VARCHAR)        
        WHERE  si.auto_renewal_flag = (CASE WHEN @AutoRenewalChk = 1 THEN 1 END)
            OR si.auto_renewal_flag <= (CASE WHEN @AutoRenewalChk = 0 THEN 1 END)),
        rs AS (SELECT DISTINCT sku FROM smbecommerce..renewalskus)
    INSERT INTO #temp
    SELECT e.nai_agreement_account_name, c.first_name  + ' '  + c.last_name, c.work_phone, c.email_address, e.entitlement_end_date,
        e.nai_grant_number, e.nai_quantity, e.product_name, (CASE WHEN LEN(e.nai_superceded_id) > 0 THEN 1 ELSE 0 END)
    FROM siebelextract..entitlement e     
        INNER JOIN siebelextract..account a ON a.row_id = e.nai_reseller_id           
        INNER JOIN SiebelExtract.dbo.contact c WITH (NOLOCK) ON e.nai_primary_contact_id = c.row_id 
        INNER JOIN rs ON rs.sku = e.product_name
        LEFT JOIN cte ON e.nai_grant_number = cte.nai_grant_number
    WHERE a.parent_account_id = @SiebelAccId
    AND LEN(E.nai_reason_code) = 0
    AND e.entitlement_end_date BETWEEN @FDate AND @TDate   
    AND (@AutoRenewalChk = 0 OR cte.nai_grant_number IS NOT NULL)

    SELECT CompanyName, ContactName, EmailAddress, ContactPhone, SkuDescription, Quantity, GrantNumber, ExpirationDate, IsRenewed 
    FROM #temp
    WHERE (@FilterCriteria = 0)
    OR IsRenewed = (1 - (@FilterCriteria -1))

    DROP TABLE #temp
END

1. 由于您使用的是SQL2008，因此可以使用DATE类型。到目前为止的转换让你的意图比DATEADD(DATEDIFF)方法(我喜欢-一种剥离时间的新颖方式)更清晰。
2. 将#Temp2定义移到了顶部，并将其改为#Temp。此过程不需要多个临时表。
3. 将原来的#Temp更改为单列CTE (名为cte)。还有一个子SELECT会消耗您的sku表的性能，所以它也被移到了一个名为rs的cte中。
4. 将SELECT更改为#temp1以插入到#Temp中。对rs公用表表达式的内部联接以与以前相同的方式过滤结果，但速度会快得多。查询中的左联接和statement.
5. The @
6. 检查的最后一行已被压缩并包括在最终的IF (@AutoRenewalChk = 0 OR @AutoRenewalChk = 1)语句中。根据您的代码，如果@FilterCriteria为0，则返回所有行。否则，将返回IsRenewed = 1和@FilterCriteria =1的行，或者返回IsRenewed =0和@FilterCriteria =2的行。

实际上，由于我们在这里所做的只是将数据插入到表中，然后选择它，我们可以将其进一步压缩到一条select语句中：

CREATE PROCEDURE dbo.PORT_GetFutureOpportunities (
    @SiebelAccId VARCHAR(50),
    @FromDate DATETIME,
    @ToDate DATETIME,
    @FilterCriteria INT,
    @AutoRenewalChk INT
)
AS
BEGIN 
    DECLARE @FDate DATETIME, @TDate DATETIME 
    SET @FDate = CAST(@FromDate AS DATE)
    SET @TDate = CAST(@ToDate AS DATE)

    WITH cte AS (SELECT DISTINCT e.nai_grant_number    FROM smbecommerce..SalesItem si 
            INNER JOIN smbecommerce..sales s ON s.sales_id = si.sales_id
            INNER JOIN siebelextract..entitlement e ON SUBSTRING(e.nai_grant_number, 1,  NULLIF(CHARINDEX('-', e.nai_grant_number) - 1, -1)) = CAST (s.sales_order_id AS VARCHAR)        
        WHERE  si.auto_renewal_flag = (CASE WHEN @AutoRenewalChk = 1 THEN 1 END)
            OR si.auto_renewal_flag <= (CASE WHEN @AutoRenewalChk = 0 THEN 1 END)),
        rs AS (SELECT DISTINCT sku FROM smbecommerce..renewalskus)
    SELECT e.nai_agreement_account_name AS [CompanyName], c.first_name  + ' '  + c.last_name AS [ContactName],
        c.work_phone AS [ContactPhone], c.email_address AS [EmailAddress], e.entitlement_end_date AS [ExpirationDate],
        e.nai_grant_number AS [GrantNumber], e.nai_quantity AS [Quantity], e.product_name AS [SkuDescription],
        (CASE WHEN LEN(e.nai_superceded_id) > 0 THEN 1 ELSE 0 END)  AS [IsRenewed]
    FROM siebelextract..entitlement e     
        INNER JOIN siebelextract..account a ON a.row_id = e.nai_reseller_id           
        INNER JOIN SiebelExtract.dbo.contact c WITH (NOLOCK) ON e.nai_primary_contact_id = c.row_id 
        INNER JOIN rs ON rs.sku = e.product_name
        LEFT JOIN cte ON e.nai_grant_number = cte.nai_grant_number
    WHERE a.parent_account_id = @SiebelAccId
    AND LEN(E.nai_reason_code) = 0
    AND e.entitlement_end_date BETWEEN @FDate AND @TDate   
    AND (@AutoRenewalChk = 0 OR cte.nai_grant_number IS NOT NULL)
    AND ((@FilterCriteria = 0) OR CASE WHEN LEN(e.nai_superceded_id) > 0 THEN 1 ELSE 0 END = (1 - (@FilterCriteria -1)))
END

Answer 2

• 首先识别代码。这是一个混乱的understand.
• too许多临时表。加载temp、加载temp1、使用temp1的副本加载temp2，而不是使用更多的temp1行加载temp2。你需要找到一种连接这些表的方法。
• 也有一个很大的bug。如果@AutoRenewalChk为2，则不会创建临时2，但将通过查询访问。这将失败。
• 最后，这可能不会提高性能，但会生成更干净的代码，而不是对@FilterCriteria的每个可能性都有一个IF，而是运行查询并在where子句

上添加一个CASE