Improve CASE select performance

I have the following query which is taking up to 2 seconds to run on a data set of less than 100K rows.

My SQL is very rusty but this just looks like it's doing a ton more work than it should. Can anyone offer some pointers on where to look to speed this up?

SELECT
    a.AddressID,
    IsPrincipal = CASE
    WHEN EXISTS(SELECT TOP 1 1 FROM dbo.Setting s WHERE s.SettingValue = a.AddressID AND s.SettingDefinitionID = 3 AND s.ProfileID = 1)
                THEN 1
                ELSE 0
            END,
    IsPickUp = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 2)
                THEN 1
                ELSE 0
            END,
    IsSender = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 3)
                THEN 1
                ELSE 0
            END,
            IsDelivery = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 4)
                THEN 1
                ELSE 0
            END,
    IsReceiver = CASE
                WHEN EXISTS(SELECT TOP 1 1 FROM dbo.AddressRole ar WHERE ar.AddressID = a.AddressID AND ar.[AddressRoleTypeID] = 5)
                THEN 1
                ELSE 0
            END
        FROM dbo.[Address] AS a
        WHERE a.MFTID = '12345'

Instead of doing a lookup like that for each row, you could join the AddressRole and Setting tables like this:

SELECT
    a.AddressID,
    IsPrincipal = Max(iif(s.SettingValue Is Not Null, 1, 0)),
    IsPickUp = Max(iif(ar.AddressRoleTypeID = 2, 1, 0)),
    IsSender = Max(iif(ar.AddressRoleTypeID = 3, 1, 0)),
    IsDelivery = Max(iif(ar.AddressRoleTypeID = 4, 1, 0)),
    IsReceiver = Max(iif(ar.AddressRoleTypeID = 5, 1, 0))
  FROM dbo.[Address] AS a
  Left Join dbo.Setting As s
    On a.AddressID = s.SettingValue
    And s.SettingDefinitionID = 3
    And s.ProfileID = 1
  Left Join dbo.AddressRole As ar
    On a.AddressID = ar.AddressID
  WHERE a.MFTID = '12345'
  Group By a.AddressID;

This should reduce the number of joins performed, and not change the cardinality of the resultset.

I added the MAX so that I could do a GROUP BY, since I fully expect that there might be more than one row in the AddressRole table for each row in the Address table. This way we maintain the cardinality as being the same as the Address table, but we can see if any of the rows in the AddressRole contained the value of AddressRoleTypeID that we were looking for.

The MAX and GROUP BY aren't needed if the subqueries of your original query without the TOP return a single row.

StackExchange Database Administrators Q#154420, answer score: 7