Eradicating Cursors - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Eradicating Cursors

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2004-01-13 : 07:37:01

I have the following situation:
Timber trucks diliver timber to the mill. Most times along the way they are stopped by the traffic police and are weighed at a weighbridge. Some time later these trucks then arrive at th mill and weighbridge information is then captured again. Trucks can do multiple deliveries per day.

The traffic weighbridge info that is captured is: RecID, Date, WeighBridge, VehicleRegistration - (Table: TmpRoad)

The mill Weighbridge info is similar: RecID, WBDate, Mill, VehicleRegistration - (Table: weighbridge)

Currently I use the following cursor to match the data. (ie: Which police weighbridge record matches a mill delivery)


DECLARE tmp CURSOR
   FOR SELECT RecID, WDateTime, WeighBridge, VehReg FROM TmpRoad
OPEN tmp
FETCH NEXT FROM tmp into @RecID, @WDT, @WB, @VehReg
while @@FETCH_STATUS = 0 
BEGIN
	SELECT @tDT = null
	SELECT @tDT = min(WBDate) from weighbridge where WBDate > @WDt And VehReg  = @VehReg
	
	SELECT @TMill = null
  	SELECT @TMill = MillID, @MillRecID = RecID 
		from Weighbridge where WBDate = @TDt And VehReg  = @VehReg
	
	Update TmpRoad Set AriveDate = @TDt, NextMill = @TMill, MillRecID = @MillRecID where RecID = @RecID
	
	FETCH NEXT FROM tmp into @RecID, @WDT, @WB, @VehReg
END 
CLOSE tmp
DEALLOCATE tmp

This cursor takes a very long time. Any advice on how to get rid of it and improve performance?

Thanks
Scott

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-13 : 08:26:24

Something like

update TmpRoad
set AriveDate = w.WBDate, NextMill = w.MillID, MillRecID = w.RecID
from TmpRoad r, Weighbridge w
where w.RecID = (select top 1 w.RecID from Weighbridge w2 where w2.VehReg = r.VehReg and w2.WBDate > r.WDateTime order by w2.WBDate)

If they are in w.RecID order then try

update TmpRoad
set AriveDate = w.WBDate, NextMill = w.MillID, MillRecID = w.RecID
from TmpRoad r, Weighbridge w
where w.RecID = (select min(w.RecID) from Weighbridge w2 where w2.VehReg = r.VehReg and w2.WBDate > r.WDateTime)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Scott
Posting Yak Master

145 Posts

Posted - 2004-01-14 : 01:13:51

Thanks for that. It's just a change of mindset to get rid of most cursors, no longer procedural bet set based.

I have a problem when I run it though:
The TmpRoad table has about 14,000 records and the Weighbridge table about 135,000 records. After running this statement for about 30min ny tempdb grows from just over 1MB to over 3.5GB and the statement terminates because I run out of space on my tempdb drive.

1) Please explain what is the tempdb being used for in this case?
2) Why when the source data is only a few MB's is the tempdb growing to a few GB's with this statement?

Thanks
Scott

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources