Optimizing SQL Query with Subquery

Question

I have a table with Addresses and Organization Names. There are instances where the same address can be associated with multiple organizations, and other instances where there are multiple records for the same Address-Organization pair. To wit:

Address    Orgname
Address1   Orgname1
Address2   Orgname2
Address2   Orgname2
Address3   Orgname3
Address3   Orgname4

I would like to run a select query which outputs 1 row for each Address, and if there is a 1:1 Address:Orgname relationship, the orgname, otherwise the word 'Multiple'. To wit:

Address    Orgname
Address1   Orgname1
Address2   Orgname2
Address3   Multiple

I wrote the following to accomplish this, but it runs extremely slow and I would like to know how to optimize it. Subquery X returns the distinct Match Address:Orgname. Subquery Y counts the remaining and returns the addresses where there is a 1:many relationship. These run fast on their own. The outer query then goes back to the original table and returns the distinct Address and Orgname if the address is not in the subquery of addresses with 1:many relationship, or 'Multiple' if it is.

SELECT DISTINCT [Match Address], 
CASE WHEN [Match Address] in 
    (SELECT y.[Match Address] from 
        (SELECT x.[Match Address], count(x.ORGNAME) as [Count] from
            (SELECT DISTINCT [Match Address], ORGNAME
            FROM Table) x
        GROUP BY [Match Address]
        HAVING count(x.ORGNAME) > 1) y)
THEN 'Multiple' ELSE ORGNAME END as ORGNAME
FROM Table

I suspect that instead of putting the subquery in memory and treating it as a table for the outer query, it's using a nested loop and re-running the subquery for each record in the table. I'm just not experienced enough to know how to prevent this from happening.

fyi, you might have better luck posting this on dba.stackexchange.com instead. — Peter B
– Peter B, Commented Apr 20, 2018 at 16:33

Gordon Linoff · Accepted Answer · 2018-04-20 16:33:13Z

4

I think it is easier to write the query as:

select address,
       (case when min(orgname) = max(orgname) then min(orgname)
             else 'Multiple'
        end) as orgname
from t
group by address;

answered Apr 20, 2018 at 16:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Decker Over a year ago

This is the one. <2 seconds on 68k rows. Good logic.

Collectives™ on Stack Overflow

Optimizing SQL Query with Subquery

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related