A Trick For Dealing With Table Variable Modification Performance

Don’t Want None

One of the many current downsides of @table variables is that modifying them inhibits parallelism, which is a problem #temp tables don’t have.

While updating and deleting from @table variables is fairly rare (I’ve seen it, but not too often), you at minimum need an insert to put some data in there.

No matter how big, bad, ugly, or costly your insert statement is, SQL Server can’t parallelize it.

Dimmo

Here’s our select statement.

This goes parallel and runs for about 3 seconds.

Now my heart is full~

But if we try to insert that into a @table variable, the plan will no longer go parallel, and will run for ~6 seconds.

Boy Racer

If we hit F4 to get the properties of the INSERT, well…

Chocolate Nonpareils Reason?

Let’s Say For Some Crazy Reason

You need to keep using a table variable.

Let’s say, I dunno, the crappy 1 row estimate gets you a better plan.

Or like… I dunno. Temp tables recompile too much.

I’m reaching, I know. But hey, that’s what consultants do. Have you read any blog posts lately?

If we change our insert to this, we get parallelism back:

Glamorous Glue

The dynamic SQL executes in a separate context, and the insert happens in… idk some magickal place.

But THAT’S PRETTY COOL, HUH?

This would also work if we put the query inside a stored procedure, or stuck the statement inside a variable and used sp_executesql.

In either case, the INSERT…EXEC pattern is your friend here.

Of course, you could just use a #temp table.

Sigh.

2 thoughts on “A Trick For Dealing With Table Variable Modification Performance”

Leave a Reply

Your email address will not be published. Required fields are marked *