sql server - TSQL while loop vs. merge -

i'm pretty new sql , databases in general. i'm using sql server 2008 mgmt studio.

i've read using set-based operations better rbar (just learned today!).

shortly i'll show 2 equivalent (i think) queries , i'm trying see more efficient.

the first attempt:

declare @persid int declare @mag    float declare @temp table (pid int primary key)  insert @temp select persid person  while (select count(pid) @temp) > 0 begin    select top 1 @persid = pid @temp        select @mag = sqrt(sum(value*value)) personword        personword.persid = @persid     update person    set magnitude = @mag       persid  = @persid    delete @temp pid = @persid end 

the second attempt:

declare @temp table (pid int primary key, mag float)  insert @temp    select persid, sqrt(sum(value*value)) personword       group persid  merge person p using @temp t    on p.persid = t.pid when matched update    set magnitude = t.mag 

these saved stored procedures , estimated execution plan when running:

exec firstattempt exec secondattempt 

shows 32% of batch firstattempt , 68% secondattempt

the personword table contains 41 million records...the person table contains 170,000

any thoughts/advice welcome. taking time, know how frustrating newb questions can (used math on yahoo).

edit::

running on personword having 1.3 million records , person having 3000... version merge took ~1.3 seconds execute. version while loop 6 minutes in , had completed ~15% of job.

for sort of thing, set-based not rbar!

never use table variable number of records. meant small data sets. use temp table instead , index it. way limit number of records updating. not sure why using merge anyway since simple update. code below should work.

update p set   set magnitude = t.mag person p join  #temp t    on p.persid = t.pid magnitude <> t.mag 

and depending on how going this, try store calculation needs happen once each record (and use rtigger keep updated if values change): sqrt(sum(value*value))

btw poor practice float math calculations ars introduce rounding errors since not exact.