SQL Ninja replace for Cursor with CTE

image Hi, today I want to share with you results on nice experiment and its code. I like C#, but currently most of my work attention is focused on T-SQL code. And making optimizations of performance of T-SQL code. I have both SQL Ninja and DB Council titles at work not by the accident, you know. And I want to show you how to replace cursors with CTE in good way. Cursors in general are bad and working very slow, and only propose to have cursors in T-SQL code is invocation of stored procedure or function inside cursor loop. But also it is better to made stored procedure or function with option to read from temporary table set of arguments, and always it is possible to eliminate cursor, sometimes only reason for left cursors is very complicated logic inside the loop, but still, you can always replace cursors and you should do that with CTE.

This is code of my experiment for show you comparison between cursor and CTE and good technique to eliminate them.

-- you may run this code on different db, I am running it on tempdb
use tempdb
go

-- count of rows in tables in experiment
declare @count int = 1000000

-- create tables for experiment
if object_id (N'CursorExperiment1', N'U') is not null
drop table CursorExperiment1 
create table CursorExperiment1 (
	ID int not null identity(1,1) primary key,
	ValueStr varchar(100) null,
	ValueDate datetime null
)
if object_id (N'CursorExperiment2', N'U') is not null
drop table CursorExperiment2
create table CursorExperiment2 (
	ID int not null identity(1,1) primary key,
	ValueStr varchar(100) null,
	ValueDate datetime null
)

-- generate rows in tables
;with n(n) as (
	select 1 n
	union ALL
	select n+1 n FROM n where n < @count
)
insert into CursorExperiment1 (ValueDate)
select getdate()
from n
option (maxrecursion 0)

;with n(n) as (
	select 1 n
	union ALL
	select n+1 n FROM n where n < @count
)
insert into CursorExperiment2 (ValueDate)
select getdate()
from n
option (maxrecursion 0)

-- clean dmv
dbcc dropcleanbuffers
dbcc freeproccache

-- cursor processing begin
declare @c_tB datetime = getdate();

-- update column using cursor (slow)
declare @id int
declare c cursor fast_forward for
select ID from CursorExperiment1 where ID % 2 = 0
open c
fetch next from c into @id
while @@fetch_status = 0
begin
update ce
set ce.ValueStr = cast(ce.ID as varchar(10)) + '|' + cast(ce.ValueDate as varchar(40))
from CursorExperiment1 ce where ce.ID = @id
fetch next from c into @id
end 
close c
deallocate c

-- cursor processing end
declare @c_tE datetime = getdate();

-- collect statistics
/*
select
'courosr' test,
--t.text query,
qs.execution_count [count],
qs.total_worker_time cpu,
qs.last_elapsed_time dueation,
qs.total_logical_reads reads,
qs.total_logical_writes writes,
qs.total_physical_reads p_reads
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) t
*/

-- clean dmv
dbcc dropcleanbuffers
dbcc freeproccache

-- batch processing begin
declare @b_tB datetime = getdate()

-- update column using cte (fast)
if exists(select ID from CursorExperiment2 where ID % 2 = 0)
begin
;with c as (
select ID from CursorExperiment2 where ID % 2 = 0
)
update ce
set ce.ValueStr = cast(ce.ID as varchar(10)) + '|' + cast(ce.ValueDate as varchar(40))
from CursorExperiment2 ce
inner join c on ce.ID = c.ID
end
-- batch processing end
declare @b_tE datetime = getdate()

-- collect statistics
/*
select
'cte' test,
--t.text query,
qs.execution_count [count],
qs.total_worker_time cpu,
qs.last_elapsed_time dueation,
qs.total_logical_reads reads,
qs.total_logical_writes writes,
qs.total_physical_reads p_reads
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) t
*/

-- results
select cast(@c_tE - @c_tB as time) [coursor time], cast(@b_tE - @b_tB as time) [cte time]

And here you have results for this execution. As you can see cursor is extremely slow when you compare duration of executions and also CPU usage.

cursor time cte time
00:00:16.2630000 00:00:03.5870000

There is one more thing. I put “if exists (cursor query)” code around CTE, and it is very important to have it, because if you do not have rows you should not invoke any code, especially if you collect data for temporary table to pass arguments to stored procedure. and “if exists” statement is very fast and it is a friend of all SQL Ninja.

p ;)

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.