August 29, 2008 at 11:48 am
Hi everyone,
I was wondering has anyone competed CURSOR against INNER JOIN for bulk UPDATEs:
QUERY 1:
=======
UPDATE table1
SET A.status= B.status
FROM table1 AS A JOIN #TEMP_resultset AS B
ON A.ID = B.ID
QUERY 2:
=======
declare @ID int
declare @status int
DECLARE crs_UPDATE_STATUS CURSOR FOR
(select * FROM #TEMP_resultset )
OPEN crs_UPDATE_STATUS
FETCH NEXT FROM crs_UPDATE_STATUS
INTO @ID, status
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE table1
SET status=@status
where ID = @ID
FETCH NEXT FROM crs_UPDATE_STATUS
INTO @ID, status
END
CLOSE crs_UPDATE_STATUS
DEALLOCATE crs_UPDATE_STATUS
August 29, 2008 at 2:18 pm
Yes. All other things being equal, the join version is immensely faster. The only time you might want to break it up (and you still wouldn't use a row-by-row cursor), is if the target table is huge, and the update will cause the transaction log to grow too much. Even then, you break it up into smaller sets, you don't use a row-by-row cursor.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 30, 2008 at 9:54 am
I've gotta go with Gus on this one... even the seemingly unsurmountable task of doing a running total can be done at extremely high speeds, even in SQL Server 2000, without using a cursor. For example...
http://qa.sqlservercentral.com/articles/Advanced+Querying/61716/
As Gus suggests, the only time you should actually consider using a Cursor is to control a process what affects many result sets... not single row or "RBAR" processing.
--Jeff Moden
August 30, 2008 at 11:04 am
day (8/29/2008)
Hi everyone,I was wondering has anyone competed CURSOR against INNER JOIN for bulk UPDATEs:
Some reason you can't test it yourself?
I'll bite...
Tested on SQL 2008 RTM on a desktop.
CREATE TABLE [dbo].[LargeTable2](
[ID] [int] identity NOT NULL,
[AGuid] [uniqueidentifier] NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]
insert into LargeTable2 (AGuid)
select top 4000000 NULL from
master..spt_values a cross join master..spt_values b
where a.name is null and b.name is null
select ID, NEWID() as TheGuid INTO #tempResultSet from [LargeTable2]
create clustered index idx_Testing on #tempResultSet (ID)
-- Setup complete
SET NOCOUNT ON
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE()
UPDATE A
SET A.AGuid= B.TheGuid
FROM LargeTable2 AS A JOIN #tempResultSet AS B
ON A.ID = B.ID
SELECT DATEDIFF (s, @StartTime, getdate()) AS TimeUpdateTook
SET @StartTime = GETDATE()
declare @ID int
declare @SomeGuid uniqueidentifier
DECLARE crs_UPDATE_STATUS CURSOR FOR
(select ID, TheGuid FROM #tempResultSet )
OPEN crs_UPDATE_STATUS
FETCH NEXT FROM crs_UPDATE_STATUS
INTO @ID, @SomeGuid
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE LargeTable2
SET AGuid =@SomeGuid
where ID = @ID
FETCH NEXT FROM crs_UPDATE_STATUS
INTO @ID, @SomeGuid
END
CLOSE crs_UPDATE_STATUS
DEALLOCATE crs_UPDATE_STATUS
SELECT DATEDIFF (s, @StartTime, getdate()) AS TimeCursorTook
drop table #tempResultSet
drop table LargeTable2
Result
Update: 45 sec
Cursor: 194 sec
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2008 at 12:15 pm
Tested on SQL 2005 sp2 on a laptop.
Result
Update: 129 sec
Cursor: 490 sec
It was very CPU-bound. I think that Gail's desktop is a lot better than my laptop. 🙂
[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]
August 30, 2008 at 12:36 pm
rbarryyoung (8/30/2008)
It was very CPU-bound. I think that Gail's desktop is a lot better than my laptop. 🙂
Probably. I've got a fairly new quad core with 4 GB of memory.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2008 at 12:47 pm
GilaMonster (8/30/2008)
Probably. I've got a fairly new quad core with 4 GB of memory.
Hmm, I don't think that the quad-cores can help a Cursor, can they? Aren't Cursor's inherently single-threaded? Must be the clock speed.
[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]
August 30, 2008 at 1:12 pm
Even the update ran on only one thread. Both the cursor and the update maxed one of my processors for the duration.
Probably a mixture of clock speed and that possibly SQL got more of the processor in mine, since there are others for the OS and assorted other apps to use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 30, 2008 at 3:23 pm
Here's the times on my box
P5 1.8 GHZ, twin 80GB IDE hard drives, 1GB Ram
208 Update
629 Cursor
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply