Cursor or not ?

SQL Server Cursor is a long existing function Microsoft provides. However, its performance is always being discussed (most discussion is poor performance in Cursor). If that’s the case, it’s about time to prove it and see if there is an alternative.

I have a sample table (no PK) with prepared data (180 rows). All I want is to process all the data one by one (if that’s the case). You can see below (left is cursor, right is an alternative solution).

Cursor_or_not

From the execution plan, you can tell the difference. The cursor way did 180 identical queries – Clustered Index Scan -> Compute ->Insert -> Fetch; As for the alternative, except the first 2 queries to prepare the temp table, the rest 180 queries are all Table Scan -> Select

Choose wisely !

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s