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 😉