sql - 优化查询

Question

我有以下功能

CREATE FUNCTION [dbo].[SuiviRupture]
    (@CodeArticle [NVARCHAR](13),
     @CodeSite [NVARCHAR](5), 
     @CodeStructure [NVARCHAR](13))
RETURNS @calcul TABLE (CAMOY FLOAT, QTEMOY FLOAT)
AS 
BEGIN
    WITH temp AS 
    (
        SELECT 
            t1.[datecol], t1.[Prix de vente TTC],
            t1.Quantité 
        FROM 
            [V_VentePromo] t1
        INNER JOIN 
            (SELECT DISTINCT 
                 [datecol], [Code Article], [Code Structure],
                 [Code Site], 
                 ROW_NUMBER() OVER(PARTITION BY [Code Article], [Code Structure], [Code Site] 
                                   ORDER BY [datecol] DESC) AS rn
             FROM 
                 (SELECT DISTINCT  
                      [datecol], [Code Article], [Code Structure], [Code Site]
                  FROM 
                      [V_VentePromo] t2
                  WHERE 
                      [Code Article] = @CodeArticle 
                      AND [Code Site] = @CodeSite 
                      AND [Code Structure] = @CodeStructure) g
          ) a ON a.datecol = t1.datecol
                 AND t1.[Code Article] = a.[Code Article]
                 AND t1.[Code Structure] = a.[Code Structure]
                 AND t1.[Code Site] = a.[Code Site]
        WHERE 
            t1.[Code Article] = @CodeArticle 
            AND t1.[Code Site] = @CodeSite 
            AND t1.[Code Structure] = @CodeStructure
            AND rn <= 28
    )
    INSERT @calcul 
        SELECT
            CASE WHEN COUNT(distinct [datecol]) = 0 
                    THEN 0 
                    ELSE SUM(convert(float, Quantité)) / count(distinct [datecol]) 
            END as QTEMOY,
            CASE WHEN COUNT(distinct [datecol]) = 0 
                    THEN 0 
                    ELSE SUM(convert(float, [Prix de vente TTC])) / count(distinct [datecol]) 
            END AS CAMOY
        FROM 
            temp
     RETURN;
END;

我通过这个查询调用这个函数

SELECT  
    t1.[Code Article], t1.[Code Site], t1.[Code Structure], 
    u.QTEMOY, u.CAMOY
FROM 
    V_distinctVente t1
CROSS APPLY 
    dbo.[SuiviRupture](t1.[Code Article], t1.[Code Site], t1.[Code Structure]) u

执行时间太长，我的V_distinctVente行数大约10 000 000行。如何优化它？我如何将主查询与TVF的T-SQL代码合并在一个查询中？它被执行行主查询中的行

Answer 1

您的函数可以很容易地转换为内联 TVF。 带有 BEGIN...END 的老式 TVF 需要一个表的声明并且以非常糟糕的性能而闻名。

其他内联 TVF(或 ad-hoc TVF):这是完全内联的，其行为类似于 VIEW。像这样尝试:

CREATE FUNCTION [dbo].[SuiviRupture](@CodeArticle [NVARCHAR](13),@CodeSite [NVARCHAR](5) ,@CodeStructure [NVARCHAR](13))
RETURNS TABLE 
AS 
RETURN
WITH temp AS (
 SELECT t1.[datecol],t1.[Prix de vente TTC],t1.Quantité 
      FROM [V_VentePromo] t1
      INNER JOIN (SELECT DISTINCT [datecol],[Code Article],[Code Structure],[Code Site],
      ROW_NUMBER() OVER(PARTITION BY  [Code Article],[Code Structure],[Code Site] ORDER BY [datecol]desc ) AS rn

      FROM (SELECT DISTINCT  [datecol],[Code Article],[Code Structure],[Code Site]
           FROM [V_VentePromo] t2
            WHERE [Code Article]= @CodeArticle AND [Code Site]=@CodeSite AND [Code Structure]=@CodeStructure
    )g
          ) a
           ON a.datecol=t1.datecol
            AND t1.[Code Article] = a.[Code Article]
            AND t1.[Code Structure]=a.[Code Structure]
            AND t1.[Code Site]=a.[Code Site]
            WHERE   t1.[Code Article]= @CodeArticle AND t1.[Code Site]=@CodeSite AND t1.[Code Structure]=@CodeStructure
            AND  rn <= 28

)
SELECT 

    CASE WHEN COUNT(DISTINCT [datecol])  =0 THEN 0 ELSE   SUM(CONVERT(FLOAT,Quantité))/COUNT(DISTINCT [datecol]) END  AS QTEMOY
    ,  CASE WHEN COUNT(DISTINCT [datecol])  =0 THEN 0 ELSE  SUM(CONVERT(FLOAT,[Prix de vente TTC])) / COUNT(DISTINCT [datecol])END AS CAMOY
FROM temp
;
GO