Search code examples
sql-serversubqueryexists

SQL Server: finding if a client exists (and the account they own) in MULTIPLE columns of the accounts table

I'm trying to find the list of customers that own (or co-own) an account and the account number.

Our CLIENT table contains our client data, identified with a clientId (table also contains demographics and contact details).

Our ACCOUNTS table contains account information where a clientId can exist in 1 or 5 columns (one account can have up to 5 co-owners).

My code looks like this, but is agonizingly slow. Is this the correct approach? Are there other approaches that are less expensive?

SELECT
    c.*, aa.accountNo 
FROM
    client AS c, accounts AS aa
WHERE
    EXISTS (SELECT 1
            FROM accounts AS a
            WHERE CAST(a.Account_Date AS Date) >= '2010-11-15' 
              AND CAST(a.Account_Date AS Date) <= '2017-04-24'
              AND c.clientId IN (a.Owner1, a.Owner2, a.Owner3, a.Owner4, a.Owner5))
Solution

  • The query you have explodes the data set and scans accounts twice. Also we shouldn't have to cast the column side to fit into a date range, there's now way that's beneficial (it might not always cause a scan but it's still not great). Try:

    SELECT c.*, a.accountNo
FROM dbo.accounts AS a
CROSS APPLY
(
  VALUES(Owner1),(Owner2),(Owner3),(Owner4),(Owner5)
) AS ac(clientId)
INNER JOIN dbo.client AS c
ON c.clientId = ac.clientId
WHERE a.Account_Date >= '20101115'
  AND a.Account_Date <  '20170425';