SQL: Nested Select vs Double JOIN by Performance

I have report based on a stored procedures and I need to add User name into output.

In my system TWO users can be owners of transaction.

Also, in my system User information stored in different database.

So, I see only two ways to add User name:

CASE 1

SELECT 
    E.*,
    CASE WHEN (E.UserDetailsId IS NULL AND E.UserDetailsId2 IS NULL)
            THEN 'N/A'
        ELSE
        (SELECT CONCAT(FirstName, ' ', LastName) AS UserName 
            FROM UserDb.dbo.UserDetails WHERE (
            (UserDetailsId IS NOT NULL AND UserDetailsId = E.UserDetailsId)
            OR 
            (UserDetailsId2 IS NOT NULL AND UserDetailsId2 = E.UserDetailsId)
            )
    END AS UserName,
FROM 
    TransactionDetail E
WHERE 
    E.TransactionDetailTypeId = @TypeId

CASE 2

SELECT 
    E.*,
    CASE WHEN (E.UserDetailsId IS NULL AND E.UserDetailsId2 IS NULL)
            THEN 'N/A'
        ELSE
    CASE WHEN (E.UserDetailsId IS NOT NULL)
        THEN CONCAT(UD.FirstName, ' ', UD.LastName)
    CASE WHEN (E.UserDetailsId2 IS NOT NULL)
        THEN CONCAT(UD2.FirstName, ' ', UD2.LastName)
    END AS UserName
FROM 
    TransactionDetail E
    JOIN UserDb.dbo.UserDetails UD ON UD.UserDetailsId = E.UserDetailsId
    JOIN UserDb.dbo.UserDetails UD2 ON UD2.UserDetailsId = E.UserDetailsId
WHERE 
    E.TransactionDetailTypeId = @TypeId

I’ve tried to do performance test on my local machine with 1 million of test transactions and 10 thousand of users, but results were equal.

What is the best way to do it from performance point of view and why?

The real database contains about 20 million transactions divided by 10 groups and 100 thousand of users.

Answer

Can you please try with this following script? This should return the same result your query is returning.

Please test the output is same as your query or not. If mismatch, just ignore this solution.

SELECT E.*,
CASE 
    WHEN (E.UserDetailsId IS NULL AND E.UserDetailsId2 IS NULL) THEN 'N/A'
    ELSE  CONCAT(FirstName, '  ', LastName) 
END AS UserName 
FROM TransactionDetail E
LEFT JOIN UserDetails UD  
    ON (E.UserDetailsId = UD.UserDetailsId) 
    OR (E.UserDetailsId2 = UD.UserDetailsId)
WHERE E.TransactionDetailTypeId = @TypeId

Advertisement

Answer