tom preston tom preston - 3 months ago 16
SQL Question

Where in (sub query) or (list) performance issues

I'd like to make a list based on whether a field in the original table is in two lists. My code is thus:

SELECT *
FROM ListofPlaces
WHERE Property = 'MODERATE'
and (HOMELAND in (
SELECT distinct HOMELAND
FROM PLANS
WHERE left(plans.code, 1) = '1')
or HOMELAND in (
'PlaceA'
, 'PlaceB'
, 'PlaceC'
, 'PlaceD'
, 'PlaceE'))


The list and the sub query will work fine individually, taking 00:00:01.43 for the sub query and 00:00:00.13 for the list, however they take around a min once combined.

I have tried using a left join, but this leads to a more significant reduction in performance.

The table 'PLANS' is a larger table of 4M+ rows, whilst list of places is less than 1000.

My question is whether I'm using the and/or operators efficiently, and if so, is there a more efficient way to run this query?

Answer

Try rewriting this using UNION:

SELECT *
FROM ListofPlaces
WHERE Property = 'MODERATE' AND
      HOMELAND IN (SELECT HOMELAND
                   FROM PLANS
                   WHERE left(plans.code, 1) = '1'
                  ) 
UNION
SELECT *
FROM ListofPlaces
WHERE Property = 'MODERATE' AND
      HOMELAND in ('PlaceA', 'PlaceB', 'PlaceC', 'PlaceD', 'PlaceE');

The optimizer can sometimes be confused by ORs. UNION may be needed here instead of UNION ALL if the two lists contain similar elements. Otherwise, if you know they are disjoint, use UNION ALL.

Comments