Improving performance in select

Transact-SQL
1

Hi,
I have this select that works ok although the performance is not good.
Basically the select looks for precio=0 and updates this row with the price from the previous date so long as this is not cero either. All this grouped by codigo.
Can anyone help?
thanks

CREATE TABLE #f(
      codigo int,
      fecha  datetime,
      precio money
      )
INSERT INTO #f VALUES (1,'2010-10-01',1.10912)
INSERT INTO #f VALUES (1,'2010-10-02',0)
INSERT INTO #f VALUES (1,'2010-10-03',0)
INSERT INTO #f VALUES (1,'2011-10-04',0.34591)
INSERT INTO #f VALUES (31,'2011-02-08',1.73468)
INSERT INTO #f VALUES (31,'2011-02-09',0)
INSERT INTO #f VALUES (31,'2011-02-11',0.87270)
INSERT INTO #f VALUES (31,'2011-02-10',1.51555)


;WITH CTE_cero
 AS
 (   select * from #f where precio=0 
 )

UPDATE cte_cero
    SET precio = (select top 1 #f.precio
                    from #f 
                    where CTE_cero.codigo=#f.codigo  and CTE_cero.fecha >#f.fecha order by codigo, fecha
                    )
2

Is that table clustered on ( codigo, fecha )? Or do you have index keyed on ( codigo, fecha ) that includes precio?

3

Hi Scott, I dont use anything like that. Should I?

4

Yes, you have to for any decent performance on this.

If the table is not currently clustered at all, then cluster it on ( codigo, fecha ).

5

great. many thanks

6

It may also be worth looking at a windowed function:

WITH MaxPreceeding
AS
(
	SELECT *
		,MAX(precio) OVER (PARTITION BY codigo ORDER BY fecha ROWS UNBOUNDED PRECEDING) AS MaxPrecio
	FROM #f
)
UPDATE MaxPreceeding
SET precio = MaxPrecio
WHERE precio = 0;
7

I have just realized that, while it works with your test data, my previous example does not quite do what you want.

Using this test data:

CREATE TABLE #f
(
	codigo int NOT NULL
	,fecha  datetime NOT NULL
	,precio money NOT NULL
);
INSERT INTO #f
VALUES (1,'20100930',2.10912)
,(1,'20101001',1.10912)
,(1,'20101002',0)
,(1,'20101003',0)
,(1,'20111004',0.34591)
,(31,'20110207',2.73468)
,(31,'20110208',1.73468)
,(31,'20110209',0)
,(31,'20110211',0.87270)
,(31,'20110210',1.51555);

With reference to solution 2 at:

try:

WITH MaxPreceeding
AS
(
	SELECT F.codigo, F.fecha, F.precio, A.binstr
		,CAST(
			SUBSTRING
			(
				MAX(A.binstr) OVER (PARTITION BY F.codigo ORDER BY fecha ROWS UNBOUNDED PRECEDING )
				,9, 8
			)
			AS money
		) AS MaxPrecio
	FROM #f F
		CROSS APPLY (VALUES( CAST(fecha AS BINARY(8)) + CAST(NULLIF(precio, 0) AS BINARY(8)) ) ) AS A (binstr)
)
UPDATE MaxPreceeding
SET precio = MaxPrecio
WHERE precio = 0;
8

Thanks Ifor. Actually I tried your suggestion and as far as I can see it workss well. What do you see as a problem? cheers

9

If you try my two solutions against the test data I posted you will see the difference.

10

Now I get it. Thanks!