Making SQL query with multiple where conditions and variables more efficient

Question

I have this query which does get the results I require but is incredibly slow and surely there must be a better way of doing this as I would need to run this regularly.

Each where clause has two variables which will always be the same number but I need to use this with 50+ BigIDs, the example shows 3 but I would do it for BigID 1 to 50+.

I am unsure how to make this shorter because of the two variables (one of which being in a subquery) and group by which is required. Any help or pointing in the right direction would be appreciated. Thanks.

 SELECT BigID,count(LittleID)
      FROM Table1
      where ( (BigID=1 and LittleID not in (SELECT LittleID FROM Table2 where BigID=1)) or
              (BigID=2 and LittleID not in (SELECT LittleID FROM Table2 where BigID=2)) or
              (BigID=3 and LittleID not in (SELECT LittleID FROM Table2 where BigID=3)) )
      group by BigID

Table definitions please. (So we know if LittleID and BigID exist in one or both tables...) — jarlh
– jarlh, Commented Jan 8, 2016 at 15:17

Gordon Linoff · Accepted Answer · 2016-01-08 15:17:03Z

2

One method is a correlated subquery:

  SELECT t1.BigID, count(t1.LittleID)
  FROM Table1 t1
  WHERE t1.BigID IN (1, 2, 3) and
        t1.LittleID not in (SELECT t2.LittleID
                            FROM Table2 t2
                            WHERE t2.BigID = t1.BigId
                           )
  GROUP BY t1.BigID

answered Jan 8, 2016 at 15:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

TT. Over a year ago

It is generally better to construct a query that uses NOT EXISTS than one with NOT IN because of possible side-effects regarding NULL values. That probably doesn't play in this instance, but I prefer to stick with the construction that has the least side-effects. Otherwise performance is usually identical.

Devart · Accepted Answer · 2016-01-08 15:16:40Z

1

SELECT t1.BigID, COUNT(t1.LittleID)
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.LittleID = t2.LittleID AND t1.BigID = t2.BigID
WHERE t1.BigID IN (1, 2, 3)
    AND t2.LittleID IS NULL
GROUP BY t1.BigID

answered Jan 8, 2016 at 15:16

Devart

120k23 gold badges172 silver badges189 bronze badges

2 Comments

TT. Over a year ago

It is generally more efficient to drop the LEFT JOIN, and use a NOT EXISTS(SELECT 1 FROM Table2 AS t2 WHERE t1.LittleID = t2.LittleID AND t1.BigID = t2.BigID) in the WHERE clause. Reference

Devart Over a year ago

@TT good note... in this case we can get SEMI LEFT JOIN instead of LEFT JOIN

Devart · Accepted Answer · 2016-01-08 15:19:41Z

1

SELECT Table1.BigID,
       COUNT(Table1.LittleID)
FROM Table1
LEFT JOIN Table2 ON Table1.LittleID = Table2.LittleID
    AND Table1.BigID = Table2.BigID
WHERE Table2.LittleID IS NULL
    AND Table1.BigID IN (1, 2, 3)
GROUP BY Table1.BigID

edited Jan 8, 2016 at 15:19

Devart

120k23 gold badges172 silver badges189 bronze badges

answered Jan 8, 2016 at 15:17

Greg Viers

3,5233 gold badges22 silver badges36 bronze badges

Collectives™ on Stack Overflow

Making SQL query with multiple where conditions and variables more efficient

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related