Optimizar consultas

Tengo la siguiente 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; 

Llamo a esta function por esta consulta

 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 

enter image description here

El time de ejecución es demasiado largo, tengo un número de filas de V_distinctVente de aproximadamente 10 000 000. ¿Cómo puedo optimizarlo? ¿Cómo podría combinar la consulta principal con el código T-SQL del TVF en una consulta? Se ejecuta fila por fila en el consulta principal

Su function puede transformarse muy fácilmente en una TVF en línea . El antiguo TVF con BEGIN … END necesita una statement de table y es conocido por su muy mal performance.

Otro, el TVF en línea (o TVF ad-hoc ): está totalmente en línea y se comporta como una VISTA. Pruébalo así:

 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