Optimize Queries

I have the following function CREATE FUNCTION [dbo].[SuiviRupture] (@CodeArticle [NVARCHAR](13), @CodeSite [NVARCHAR](5), @CodeStructure [NVARCHAR](13)) RETURNS @ca

Solution 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:

CREATEFUNCTION[dbo].[SuiviRupture](@CodeArticle [NVARCHAR](13),@CodeSite [NVARCHAR](5) ,@CodeStructure [NVARCHAR](13))
RETURNSTABLEASRETURNWITHtempAS (
 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]= @CodeArticleAND [Code Site]=@CodeSiteAND [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

)
SELECTCASEWHEN 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