Farrell Keough (1/5/2009)
We have one last cursor used for an unusual "counting" routine we must follow. I attempted to replace it with this method, but it is slower than using the cursor.
OK, if I understand what this is trying to do, then the following routine is approximately 10x faster:
declare @dat datetime; set @dat = getdate()
print 'start: '+convert(varchar(23), @dat, 121)
update c
set count = 0
From (Select id, , alpha, beta, gamma, [type], [count]
, ROW_NUMBER() OVER(Partition By [count],
Order By [Type], [Alpha], [Beta], [Gamma], [id])
AS KeyRowCount
From ccount) c
Where KeyRowCount > 1
-- And [COUNT] <> 0--this MAY make it faster...
print 'done: '+convert(varchar(23), getdate(), 121)
+', ms:' + cast(datediff(ms,getdate(),@dat) as varchar)
It appears to produce the correct results on my DB, but you should check it to be sure.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]