A Trick For Dealing With Table Variable Modification Performance Problems In SQL Server

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.

SELECT DISTINCT
       u.Id
FROM   dbo.Users AS u
JOIN   dbo.Posts AS p
    ON p.OwnerUserId = u.Id
JOIN   dbo.Comments AS c
    ON  c.PostId = p.Id
    AND c.UserId = u.Id
WHERE  c.Score >= 5;

This goes parallel and runs for about 3 seconds.

SQL Server Query Plan
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.

DECLARE @icko TABLE (id INT);
INSERT @icko ( id )
SELECT DISTINCT
       u.Id
FROM   dbo.Users AS u
JOIN   dbo.Posts AS p
    ON p.OwnerUserId = u.Id
JOIN   dbo.Comments AS c
    ON  c.PostId = p.Id
    AND c.UserId = u.Id
WHERE  c.Score >= 5;
SQL Server Query Plan
Boy Racer

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

SQL Server Query Plan Properties
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:

DECLARE @icko TABLE (id INT);
INSERT @icko ( id )
EXEC(N'SELECT DISTINCT
              u.Id
       FROM   dbo.Users AS u
       JOIN   dbo.Posts AS p
           ON p.OwnerUserId = u.Id
       JOIN   dbo.Comments AS c
           ON  c.PostId = p.Id
           AND c.UserId = u.Id
       WHERE  c.Score >= 5;')
SQL Server Query Plan
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.

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



2 thoughts on “A Trick For Dealing With Table Variable Modification Performance Problems In SQL Server

Comments are closed.