Rajib Bahar's Blog

Rajib blogs here about topics of his interest.

Is a table variable always the answer?

August 14
by Rajib Bahar 14. August 2012 00:27

I usually got good results with table variables over cursors or derived queries. I wanted to take advantage of available memories. In one of my previous project, I tried to implement an alternative to cursor via table variable. Unfortunately, it caused the SSIS process to run out of memory after running for 8+ hours. I was surprised to see it perform slower than a derived query version of that same query. It was doing a very basic data retrieval function such as selecting the data. After working with our DBA, we discovered that the server didn't have enough memory available during the time of execution (due to high user traffic). The query would slow down after processing few thousands of rows of data. It took some time to work with our colleagues to figure out the most optimal # rows the table variable can handle at any given time. Anyways, we resorted to the derived queries approach for bigger datasets.

Tags: ,

SQL