Hi Moacyr,
Could it be parameter sniffing? See Parameter Sniffing
http://pratchev.blogspot.com/2007/08/parameter-sniffing.html and
http://www.databasejournal.com/features/mssql/article.php/3841271/T-SQL-Best-Practices--Parameter-Sniffing.htm>Hi
>
>I was testing the query Borislav lelp me to build in SSMS and the speed was OK, even better than I could expect - about 2s.
>Then I put the same statements into a stored procedure, run it, and the time increases to more than one minute, even slower than the routine using cursors I was testing before.
>
>Is that possible than the execution plan can change and explain this difference?
>Any ideas to solve this?
>
>TIA
>
>
>DECLARE @HistoricoProduto TABLE (id int, prodid int, caddate datetime)
>INSERT INTO @HistoricoProduto VALUES (714640, 99608,'2010-02-21 17:27:38')
>INSERT INTO @HistoricoProduto VALUES (714641,101899,'2010-02-22 17:27:38')
>INSERT INTO @HistoricoProduto VALUES (714638,101226,'2010-02-23 17:20:03')
>INSERT INTO @HistoricoProduto VALUES (714639,101234,'2010-02-24 17:20:03')
>
>DECLARE @Romaneio TABLE (id int, valor int, caddate datetime)
>INSERT INTO @Romaneio VALUES (769291,2459.06,'2010-02-21 16:57:14')
>INSERT INTO @Romaneio VALUES (769290,3055.14,'2010-02-22 16:34:23')
>INSERT INTO @Romaneio VALUES (769289,1064.73,'2010-02-23 15:34:03')
>INSERT INTO @Romaneio VALUES (769288,2055.85,'2010-02-24 15:29:17')
>
>SELECT Hist.ProdId AS ProdId,
> Hist.CadDate AS StartDate,
> SUM(Roma.Valor) AS Sales
>FROM (SELECT ProdId,
> MAX(CadDate) AS CadDate
> FROM @HistoricoProduto
> GROUP BY prodid) Hist
>INNER JOIN @Romaneio Roma ON Hist.CadDate <= Roma.CadDate
>GROUP BY Hist.ProdId,
> Hist.CadDate
>
>
>
If it's not broken, fix it until it is.
My Blog