dinsdag 5 juli 2011

Slow running updates (DDL) on table variable

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!

http://msdn.microsoft.com/en-us/library/ms175010.aspx
Important 
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

2 opmerkingen:

Ronald Kraijesteijn zei

Hi Arjan,

I had the same situation running here. We moved to a new server with our Datawarehouse. We used a stored procedure for updating statistics in a large fact table. This procedure used table variables to calculate running totals (see my blog: http://www.sqlblog.nl/Artikelen/T-SQL/running-totals-cumulatieven-berekenen.html

After we moved, this procedure took 12 hours to complete!! I didn't understand why this was happening.

After doing some research, I found your article. I removed the table variables and replaced them by a real temporary table (#TableName). After this change, the procedure runs fine. I don't understand exactly why this is better /faster but it works :-)

Thanks for this. Greets. Ronald

Arjan Fraaij zei

Ronald,

The short explanation is that if you start an update statement on a regular table, it is often split into parallel processing threads because the query optimizer detects that parallel processing is possible. The query optimizer does not work on table variable.

Parallel Query Processing:
http://msdn.microsoft.com/en-us/library/ms178065.aspx

Greetings.
Arjan