BTW, can you explain the idea behind this query? It looks similar to running total problem for me, so the triangular join Borislav used is supposed to be slower than even cursor approach.
Check out instead
Lightning Fast Hybrid RUNNING TOTAL - Can you slow it down?>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