优化查询 [英] Optimize queries

问题描述

我有以下功能

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;

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

I call this function by this query

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代码结合在一个查询中?主查询中的行

The execution time is too long, I have a number of rows of V_distinctVente about 10 000 000. How to optimize it?How could Icombine main query with the T-SQL code of the TVF in one query?it's executed row by row in the main query

推荐答案

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

Your function can very easily be transformed into an inline TVF. The old fashioned TVF with BEGIN...END needs a table's declaration and is known for very bad performance.

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

Other the inline TVF (or ad-hoc TVF): This is fully inlined and behaves like a VIEW. Try it like this:

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

这篇关于优化查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！