Union Query Efficieny

Question

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:

Retrieve parent/child if currentAttendance < maxAttendance (universal)
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
)

I see no condition that `currentAttendance = maxAttendance`. Are you sure this query returns the results you want? — Gordon Linoff, Jul 18 '14 at 13:53
Answer to A is "Is it running too slowly?". Answer to B is a little trickier. Could you add a few sample data rows with just the pertinent info. You could certainly use less code with a CTE but that won't improve runtime performance. A Temp table would possibly improve performance if you're selecting for more than 1 event — David Hayes, Jul 18 '14 at 13:54
Another alternative would be to replace the NOT IN with a LEFT JOIN, that might improve performance. You should also replace the UNION with a UNION ALL to remove the de-dupe step of the UNION — David Hayes, Jul 18 '14 at 13:56
@Gordon, I modified the example query slightly, but the condition still is the same. My example query still checks that condition, only in reverse. — justacoder, Jul 18 '14 at 13:59
Did you try a `UNION ALL` in place of a `UNION`, as a simple option? — Rowland Shaw, Jul 18 '14 at 15:32

score 2 · Answer 1 · edited May 23 '17 at 10:25

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
))

I now have the power of `with cte`, which will help me greatly. — justacoder, Jul 18 '14 at 14:22
I read your question a little more, and there might be a way to do this in a single CTE using recursion.. Take a look here and think about whether that could provide a more elegant solution: http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx — atom.gregg, Jul 18 '14 at 14:43
As a note: SQL Server does not materialize CTE's so the work is actually done multiple times. — Gordon Linoff, Jul 18 '14 at 14:47
Technically correct, but because we are using it to express the conditions, not to select from it, we do the same work as a sub-select would which is to seek/scan and then hash join back to the parent expression depending on the indexes present. — atom.gregg, Jul 18 '14 at 15:07

score 0 · Accepted Answer · answered Jul 18 '14 at 13:52

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.

Wonderful nesting. Is there any limit to how deep you can nest queries out of curiosity? — justacoder, Jul 18 '14 at 14:17
This is only nested one level. I'm sure there is a limit, but I have never run into it (actually, I'm not a big fan of nesting queries). — Gordon Linoff, Jul 18 '14 at 14:46

Union Query Efficieny

2 Answers2