Today a consultant came to me telling that when he did an update statement yesterday on a table based on a table variable it took less then 1 minute and today it took more then 15 minutes.
He used this in a stored procedure. The first thing I did was replacing the table variables with real #temporay tables . Immediately the time was back to less then 1 minute.
Why was the use of a table variable that much slower?
When looking at waiting tasks or waitstats there was not much to show, the query seems to be running without really needing to wait for other resources.
One thing triggerd me there was only one query thread running and that is causing the slowness of the DDL query based on the table variable.
Table variable can't make use of parallel query processing!
Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Queries that read table variables without modifying them can still be parallelized.
Hope you enjoy this blog! When you have comments, better solution and recommendation I like to hear from you.
Dynamic Intelligence | LinkedIn | Blog | Twitter