You should start by comparing execution plans for both cases.

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