SQL with string concatenation is slow

Question

I have a stored procedure which is taking 15 seconds to execute. The database is SQL Server 2008 R2.

The SQL query is as follows,

SELECT * FROM EmployeeSetA
    UNION 
SELECT * FROM EmployeeSetB
WHERE
Name+''+Id NOT IN (SELECT Name+''+Id FROM EmployeeSetA) 

The query is trying to union EmployeeSetA and EmployeeSetB and also ensures that Name and Id in EmployeeSetB is not in EmployeeSetA before performing Union.

When I verified, the string concatenation is causing the SQL to run slowly. Is there any better approach? Any suggestion will be greatly appreciated.

Answer 1

You could stack the two tables first and then use except to get rid of unwanted records. Feel free to change union all to union if that's what you actually want. Having said that, not exists is the ideal solution

select * from EmployeeSetA
union all
select * from EmployeeSetB
except 
select b.* from EmployeeSetB b
join EmployeeSetA a on a.name=b.name and a.id=b.id;

Or more directly,

select * from EmployeeSetA
union all
select b.* from EmployeeSetB b
left join EmployeeSetA a on a.name=b.name and a.id=b.id
where a.name is null or a.id is null;