November 18, 2003 at 5:31 pm
Considering this, i have 2 tables A and B
first Method, using a cursor on table A,
i start updating records on table b. If table A has 100K records, the cursor will be holding 100K rows, how sql server manages it, i am particularly concern, i am aware of the overhead
second method, using a while loop on table A
i update the records on table b, if A has 100K records, the sql statement will execute 100K times.
In the end which method will yield the fastest performance and in the same time uses the least resources?
Thanks. I need another help, is there anyway while running the query, any indicator on performance monitor i cna use to estimate the performance?
November 19, 2003 at 12:03 am
The cursor will hold the record set in tempdb...
if you are looping on the table using a identity or some such column - this should reduce the overhead of storing the recordset..hence be better than the cursor
if you have the option - you should try a set based approach
November 19, 2003 at 12:28 am
ok thanks. Btw, how do i go abt doing the set based approach?
November 19, 2003 at 1:01 am
depends on what you want to do.. if you could paste the DDL and some data - we can have a look
November 19, 2003 at 6:28 am
Simple example
UPDATE b
SET b.col = a.col
FROM tableb b
INNER JOIN tablea a
ON a.rowid = b.rowid
Far away is close at hand in the images of elsewhere.
Anon.
November 19, 2003 at 12:13 pm
In my opinion, only use cursor as the last resort because it takes more time and more resource.
you could also parse out your update such as
declare @a int, @i int
set @i = 1
select @a = count(*) from a
while @i < @a
begin
UPDATE b SET b.col = a.col
FROM tableb b
INNER JOIN tablea a ON a.rowid = b.rowid
and a.rowid <= @i + 1000
set @i = @i+1000
end
mom
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply