重写SQL查询以简化逻辑

debugcn 发表于 Dev

米歇尔·桑托斯（Michelle Santos）

我是优化查询方面的新手。这将是我第一次处理其他人的查询并对其进行优化以提高性能。能否请您就查询的哪一部分进行简化以提高其性能提供建议...。

CREATE FUNCTION SALES  
   (@SUPPLIERCODE VARCHAR(15),  
    @BATCHID VARCHAR(50))  
RETURNS  
    @STOCKDETAILS TABLE([ID] CHAR(1),  
                        [BATCH RECEIVEDATE] DATETIME,  
                        SUPPLIERCODE VARCHAR(15),  
                        [NOW - RECEVEDATE] INT,  
                        [DUEDATE] VARCHAR(50)  
                       )  
AS  
BEGIN  
    DECLARE @RECEIVEDATE DATETIME,  
            @SUPPLIERCODE1 VARCHAR(15)  

    SELECT TOP 1  
        @RECEIVEDATE = O.ReceivedDate,  
        @SUPPLIERCODE1 = A.SUPPLIERCODE  
    FROM 
        TRANSACT.dbo.FIELDS A WITH(NOLOCK)  
    INNER JOIN 
        TRANSACT.dbo.DELIV O WITH(NOLOCK) ON O.BATCHID = A.BATCHID  

    DECLARE @ID1 TABLE(SUPPLIERCODE VARCHAR(50))  

    INSERT INTO @ID1  
        SELECT P.SUPPLIERCODE  
        FROM  
            (SELECT  
                 [SUPPLIERCODE] = SUPPLIERCODE,  
                 [TOTAL] = ISNULL(SUM(ITEMPRICE + (ITEMPRICE * .12)), 0)  
             FROM TRANSACT.dbo.ProviderDiscount WITH(NOLOCK)  
             WHERE ACQUIREDDATE <> '1900-01-01 00:00:00.000'  
               AND SUPPLIERCODE = @SUPPLIERCODE1  
             GROUP BY SUPPLIERCODE) P  
        WHERE P.TOTAL <> 0  

    DECLARE @ID TABLE ([BATCH RECEIVEDATE] DATETIME,  
                       SUPPLIERCODE VARCHAR(15),  
                       ACQUIREDDATE DATETIME,  
                       Coverage VARCHAR(20),  
                       CoverageItem VARCHAR(10),  
                       [NOW - RECEVEDATE] INT,  
                       DiscTerm1 INT, DiscTerm2 INT,
                       DiscTerm3 INT, DiscTerm4 INT,
                       DiscTerm5 INT,  
                       [NEW ACQUIREDDATE] VARCHAR(50)  
                      )  

    INSERT INTO @ID  
        SELECT DISTINCT  
            [BATCH RECEIVEDATE] = @RECEIVEDATE,  
            B.SUPPLIERCODE,  
            B.ACQUIREDDATE,  
            B.Coverage,  
            B.CoverageItem,  
            [NOW - RECEVEDATE] = DATEDIFF(DAY,@RECEIVEDATE,GETDATE()),  
            B.DiscTerm1, B.DiscTerm2, B.DiscTerm3,
            B.DiscTerm4, B.DiscTerm5,  
            [NEW ACQUIREDDATE] = TRANSACT.dbo.fxnGetIDNewACQUIREDDATE(B.DiscTerm1, B.DiscTerm2, B.DiscTerm3, B.DiscTerm4, B.DiscTerm5, @RECEIVEDATE)  
        FROM 
            TRANSACT.dbo.ProviderDiscount B WITH(NOLOCK)  
        INNER JOIN  
            (SELECT  
                 [ACQUIREDDATE] = MAX(ACQUIREDDATE),  
                 [REOD] = MAX(REOD)  
             FROM 
                 TRANSACT.dbo.ProviderDiscount B2 WITH(NOLOCK)  
             INNER JOIN 
                 @ID1 B1 ON B1.SUPPLIERCODE = B2.SUPPLIERCODE  
             WHERE 
                 B2.Coverage = @CLAIMTYPE  
                 AND B2.ACQUIREDDATE < @RECEIVEDATE) B3 ON B3.REOD = B.REOD  

       INSERT INTO @STOCKDETAILS  
           SELECT DISTINCT  
               [ID] = 'Y',  
               [BATCH RECEIVEDATE],  
               SUPPLIERCODE,  
               [NOW - RECEVEDATE],  
               [DUEDATE] = MIN([NEW ACQUIREDDATE])  
           FROM 
               @ID  
           WHERE 
               ISNULL([NEW ACQUIREDDATE],'NONE') <> 'NONE'  
           GROUP BY 
               [BATCH RECEIVEDATE], SUPPLIERCODE, [NOW - RECEVEDATE]  

    RETURN  
END

卡尔·基宁格

好吧，这是您可以做的一件事。

 DECLARE @ID1 TABLE(SUPPLIERCODE VARCHAR(50))  
 INSERT INTO @ID1  
 SELECT P.SUPPLIERCODE  
 FROM  
 (  
  SELECT  
   [SUPPLIERCODE] = SUPPLIERCODE,  
   [TOTAL] = ISNULL(SUM(ITEMPRICE+(ITEMPRICE*.12)),0)  
  FROM TRANSACT.dbo.ProviderDiscount WITH(NOLOCK)  
  WHERE ACQUIREDDATE <> '1900-01-01 00:00:00.000'  
   AND SUPPLIERCODE = @SUPPLIERCODE1  
  GROUP BY SUPPLIERCODE  
 ) P  
 WHERE P.TOTAL <> 0

可以重写，去除很多额外的东西。甚至比我在这里做的还要多：

 DECLARE @ID1 TABLE(SUPPLIERCODE VARCHAR(50))  

 INSERT INTO @ID1  
 SELECT SUPPLIERCODE 
   FROM TRANSACT.dbo.ProviderDiscount WITH(NOLOCK)  
  WHERE ACQUIREDDATE <> '1900-01-01 00:00:00.000'  
    AND SUPPLIERCODE = @SUPPLIERCODE1  
  GROUP BY SUPPLIERCODE  
 HAVING ISNULL(SUM(ITEMPRICE),0) <> 0

这几乎就像是重构SQL的测试问题。

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

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