Thanks Nevyn. That help me figure out what I did wrong.

Learned something new today. Didn't know what a "six degrees of kevin bacon" was. Interesting readying.

Anyway, the WHILE LOOP was still faster when executed against a table with 2,789,799 records.

I got the below script from some article on SSC some time ago and use it frequently on db's I have limited access on.

You can see the results of recursive CTE and the WHILE LOOP statement. SQL 2008 R2 on an i7 HP laptop.

The recursive CTE took almost one minute and the WHILE LOOP about 10 seconds or less. Did not clear cache between runs.

Anyway, I was hoping to clean up some code, since it being used more frequently, but the original developer may have had this right. Not giving up but it has to be shelved for right now.

Set NoCount On;

Declare @cpu_ int;

Declare @lreads_ int;

Declare @eMsec_ int;

Select

@cpu_ = cpu_time,

@lreads_ = logical_reads,

@eMsec_ = total_elapsed_time

From

sys.dm_exec_requests

Where

session_id = @@spid;

--====== Paste Code Below ============

--WHILE LOOP - three runs

----CpuMsLogRdsElapsed

----2596641465099

----2719641565540

----2841641465366

--Recursive CTE - two runs

----CpuMsLogRdsElapsed

----57360160042057833

----57687160041459077

--====== Paste Code Above =============

Select

cpu_time-@cpu_ as CpuMs,

logical_reads- @lreads_ as LogRds,

total_elapsed_time - @eMsec_ as Elapsed

From

sys.dm_exec_requests

Where

session_id = @@spid

GO