I am currently evaluating the performance of an application which relies heavily on cursors. Below is an code example. I am looking for some good performance gains, is this the best method of updating rows of data?
DECLARE @.PAlloc_TimeID as int
DECLARE @.PAlloc_UniqueID as int
DECLARE PAlloc CURSOR FOR
SELECT TimeID,
UniqueID
FROM #PMatch
GROUP BY TimeID, UniqueID
HAVING SUM(Active) > 1
OPEN PAlloc
FETCH NEXT FROM PAlloc INTO @.PAlloc_TimeID , @.PAlloc_UniqueID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
UPDATE P
SET Active = 0
FROM #PMatch P
WHERE P.TimeID = @.PAlloc_TimeID
AND P.UniqueID = @.PAlloc_UniqueID
AND P.ElementID > (
SELECT MIN(ElementID)
FROM #PMatch
WHERE TimeID = @.PAlloc_TimeID
AND UniqueID = @.PAlloc_UniqueID)
FETCH NEXT FROM PAlloc INTO @.PAlloc_TimeID , @.PAlloc_UniqueID
END
CLOSE PAlloc
DEALLOCATE PAlloc
Normally, using a CURSOR is NOT the best way to update data. (However, in a few situations, it may be necessary.)
A set based operation (UPDATE) is usually the 'best' way to update. Consider if your process above could be re-written as a single UPDATE statement, perhaps using one or more sub-queries. (It might be easier than you think.)
|||Yes... Arnie is right, It can be replace with single Update
Statement. But if the Database is gigantic then Updating in a single statement
is not preferred.
|||u'll be able to write this query in one (or more) update statement(s)...or use a case in update statement.... most of the times u wont need a sursor at all.... and i guess even for a large database in this is a better solution..., as cursor on a large table will block resources for long...
sql