Table variables slow in JOIN's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table variables slow in JOIN's

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-04 : 07:45:29

Hi all,

I'm not sure how to phrase my question so I'm just going to dive in with some sample code:


DECLARE @empList TABLE (
    [employeeId] INT PRIMARY KEY
    )

INSERT @empList ( [employeeId] )
      SELECT 1
UNION SELECT 123
UNION SELECT 1111

DELETE ep
FROM
    @empList el
    JOIN employeePension ep ON ep.[employeeId] = el.[employeeId]

DELETE e
FROM
    employee e
    JOIN @empList el ON el.[employeeID] = e.[Id]

Say I have 3000 employees in my database -- each can have many employeePension entries. My table variable only holds 3 [employeeId]'s (the surrogate and primary key on the employee record -- also on the employeePension table) -- my actual script does a lot more deletes than this but all follow that pattern of JOINing the table variable to narrow down the employees

The code above works but takes a *lot* longer than using a temp table. (replacing the table variable with this)


CREATE TABLE #empList (
    [employeeId] INT PRIMARY KEY
    )

I don't mind using a temp table at all but am curious as to why there is such a vast difference in time (using a temp table the script takes about 2 mins on 3000 employees) using the table variable it takes over 50. Does this indicate a misconfiguration of my server?

The table variable never will hold more than say 100 employee-Id's at once. I thought that a table variable was the more efficient option for small datasets like this.

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

heavymind
Posting Yak Master

115 Posts

Posted - 2009-02-04 : 08:16:05

As far as I know table variables always join using nested loops. I don't know how always it is but your example corroborates this thought.
Compare the execution plans of the queries. I bet that the script using temp table joins with merge or hash join while table variable join with nested loops

Thanks, Vadym
MCITP DBA 2005/2008
Chief DBA at http://www.db-staff.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources