Table Variable Uses – Cursors
I shared a list of uses for Table Variables in which I said they were great for holding content against which to define cursors.
Rather than get into the controversy of cursors being good or bad, let me just say that SQL performs best as a set language. However, there are times when breaking your code up a little bit may optimize performance, and that can sometimes be done through a cursor.
I have used cursors when building large data warehouse mart tables. Sometimes there is so much data that may be aggregated or de-normalized in some fashion that generating the data in a single SQL statement utilizes all memory on the server, and performance creeps due to paging to virtual memory.
In this kind of scenario it is nice to use a table memory variable. I can then break up the work into smaller batches, stored in a temp table variable. I can then create a cursor against the temp table variable. By using a temp table, the cursor does not hold any locks on any production data tables, assuring I won’t be blocking myself or other users.
Why would I use a temp table over variables? I use a temp table in those situations where I need a list of actual production data in order to break down the work into smaller units. I might use something such as CustomerID, RegionID, etc.
Why would I use a temp variable over a temp table? Because when using a cursor in this fashion you are not joining or filtering the specific data contained in the temp variable. I don’t need statistics to increase join or seek performance. I am simply walking through the contents one row at a time. A temp table variable will most likely be stored in memory and perform in a table scan in the most efficient way possible. If the number of rows is too large, I may consider using a temp table instead.
Well, there are some thoughts about how to take advantage of two SQL features most often avoided, and an instance where performance can be greatly increased by using them.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
SQL Server 2012 Clustering Optimization Tips
In this article, you can find some useful tips to work with SQL Server 2012 clustering.
Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)