Optimize queries

Question

I have the following function

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

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

Answer 1

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.

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