February 12, 2003 at 11:41 am
The problem with memory table variables and parallelism.
FYI, have you been experiencing performance issues with table
variables and large data sets? This may be
due to the disabling of parallel query execution.
In our specific case, we had a query that inserted the results of a query that joined three 10,000,000 row tables (also including other WHERE criteria restrictions) into a table variable. The resulting @@rowcount was
45 records. The operation took 10-15 minutes.
The same operation took 4-10 seconds if the
results were inserted into a temp table rather than table variable. The execution plans of the two queries (inserting into variable table versus temp table) were quite
different. The execution plan of the query involving the temp table exhibited parallelism while the query involving the variable table did not.
We called Microsoft Support and received this response from them:
"I followed up with our developers this morning and found that we do not support parallelism for table variables because table variables do not support transactions. We need transactions for synchronization tasks with parallelism. One of the reasons for using table variables is to avoid the overhead of transaction processing/logging, so table variables are useful in some ways but not the best thing for large queries that would do better using parallelism."
A MS SQL Server product manager followed up with us and confirmed the above Microsoft Support response. Our development team and I find this table variable limitation very disappointing.
February 12, 2003 at 2:02 pm
This is very interesting, as it confirms something I saw and raised with Microsoft about a year ago. At the time, the UK support people claimed ignorance, however you've obviously spoken to people who know more. I had seen similar issues of performance but hadn't made the connection with parallelism. Have you tried populating the table variables from a view, compared to directly?
February 13, 2003 at 2:10 am
In addition to the transactional support you mention here I think you have to also be aware that if a memory table is going to process a large amount of data it will have to take some memory from another SQL process. This could mean that SQL will flush from its memory cache things such as cached data pages, query execution plans etc, hence causing performance issues with processes which are requested long after memory table has been used.
For this reason alone, I prefer to use temp tables for large data sets and memory table for small data sets.
Hope this helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply