SQL with string concatenation is slow-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

DECLARE @Person TABLE 
                (
                    ID int,
                    LastName varchar(50),
                    FirstName varchar(50),
                    HireDate datetime,
                    EnrollmentDate datetime,
                    Discriminator varchar (128)
                )

INSERT INTO @Person (ID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
    SELECT
        ID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator 
    FROM
        Person

INSERT INTO @Person (ID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator)
    SELECT 
        ID, LastName, FirstName, HireDate, EnrollmentDate, Discriminator 
    FROM
        PersonB
    WHERE 
        ID IN (SELECT a.ID 
               FROM PersonB a WITH (NOLOCK)
               INNER JOIN Person b WITH (NOLOCK) ON a.FirstName   '_'   a.LastName =  b.FirstName   '_'   b.LastName
               WHERE b.ID IS NOT NULL AND a.ID IS NOT NULL)

SELECT * FROM @Person

--me too still need that concat in uncommon name selection --just share