CODEWITHSUNDEEP
sqlsql-serverunionsubquery
justacoder
justacoder

Reputation: 2704

Union Query Efficieny

I am retrieving all records from a table based on two exclusive conditions. Each record is duplicated, with the duplicate record having a separate flag isWaitList = 1, and the parent storing the primary ID of its child stored in field waitListProdID; child records will have a 0 in their waitListProdID field since they cannot have their own children (i.e. child elements only go one level deep).

The table's relevant rows are conferenceID (int), [description] (varchar), maxAttendance (int), currentAttendance (int), isWaitlist (bit), waitListProdID (int).

The conditions are:

  1. Retrieve parent/child if currentAttendance < maxAttendance (universal)
  2. Retrieve child ONLY if its parent row satisfies currentAttendance >= maxAttendance

This union does the trick, though I'm wondering if A) I have written it efficiently enough, and B) if this would better served as a single (if more complex) query? 

SELECT conferenceID, [description]
FROM tblEventConferences
WHERE currentAttendance < maxAttendance
AND isWaitList = 0
AND eventID = 624

UNION

SELECT conferenceID, [description]
FROM tblEventConferences
WHERE isWaitlist = 1
AND currentAttendance < maxAttendance
AND conferenceID NOT IN (
      SELECT waitlistProdID
      FROM tblEventConferences
      WHERE isWaitList = 0
      AND eventID = 624
      AND currentAttendance < maxAttendance
)

Upvotes: 0

Views: 55

Answers (2)

atom.gregg
atom.gregg

Reputation: 1007

Try using a CTE to define your core logic (eventID and attendance counts).

Then wrap that all into one query to ensure the duplicate removal cost no longer exists.

Personal preference, but I use NOT EXISTS instead of NOT INS, see here for some reasons: What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?

Code:

;with cte as (
    SELECT conferenceID, waitlistProdID, [description], isWaitlist, currentAttendance, maxAttendance
    FROM tblEventConferences
    WHERE currentAttendance < maxAttendance
    AND eventID = 624
)
select c.conferenceID, c.[description]
from cte c
where c.isWaitlist = 1
or (c.isWaitList = 0 and not exists (
      select top 1 1
      from cte ne
      where ne.waitlistProdID = c.conferenceID
))

EDIT

Just read a comment that mentioned the query didn't match the intention. i.e. that the attendance comparison of parents/childs is different.

If that's the case, then the following adjustment is necessary:

;with cte as (
    SELECT conferenceID, waitlistProdID, [description], isWaitlist, currentAttendance, maxAttendance
    FROM tblEventConferences
    WHERE eventID = 624
)
select c.conferenceID, c.[description]
from cte c
where (c.isWaitlist = 1 and c.currentAttendance < c.maxAttendance)
or (c.isWaitList = 0 and not exists (
    select top 1 1
    from cte ne
    where ne.waitlistProdID = c.conferenceID
    and ne.currentAttendance >= ne.maxAttendance
))

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269623

UNION incurs overhead in removing duplicate. I'm thinking one query would be better than a union:

SELECT conferenceID, [description]
FROM tblEventConferences
WHERE currentAttendance < maxAttendance AND
      ((isWaitList = 0 AND eventID = 624) OR
       (isWaitlist = 1 AND
        conferenceID NOT IN (SELECT waitlistProdID
                             FROM tblEventConferences
                             WHERE isWaitList = 0 AND eventID = 624 AND
                                   currentAttendance < maxAttendance
                            )
       )
      );

One downside is that SQL Server may be easier to optimize each of the subqueries. So this is worth a try but no guarantees.

Upvotes: 0

Related Questions