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 most of my work attention is focused on T-SQL code. And making optimizations to the performance of T-SQL code. I have both SQL Ninja and DB Council titles at work, not by accident, you know. And I want to show you how to replace cursors with CTE in a good way. Cursors, in general, are bad and work very slowly, and the only purpose of having cursors in T-SQL code is to invoke stored procedures or functions inside the cursor loop. But also it is better to make stored procedure or function with the option to read from a temporary table set of arguments, and it is always possible to eliminate the cursor, sometimes the 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 the show you comparison between the cursor and CTE and good technique to eliminate them.

-- you may run this code on a different db, I am running it on tempdb
use tempdb
go
-- count of rows in tables in the experiment
declare @count int = 1000000
-- create tables for the 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, the cursor is extremely slow when comparing the duration of executions and CPU usage.

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

There is one more thing. I put the “if exists (cursor query)” code around CTE, and it is essential to have it. If you do not have rows, you should not invoke any code, especially if you collect data for a temporary table to pass arguments to the 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.