SQL Server Slow query using subqueries

Question

I have a very slooowwwww query.

It selects customer records, with combined criteria, kinda like this:

A table has Customers, another table has CustomerCars, another table has CustomerMotorcycles.

A customer can have one or more cars. If the customer is a commercial business and any one of the customer's cars is a Ford, then we want to exclude that customer from our selection.

A customer can also have one or more motorcycles, and if the customer is a retail business and any one of its motorcycles is a Harley, then we want to exclude that customer.

So I have a statement like:

SELECT * 
FROM CUST
WHERE 
    (CUST.CUSTTYPE = 'COMM' 
     AND CUST.CUSTID NOT IN (SELECT CUSTCARS.CUSTID 
                             FROM CUSTCARS 
                             WHERE CUSTCARS.CAR = 'FORD'))
    OR
    (CUST.CUSTTYPE = 'RETAIL' 
     AND CUST.CUSTID NOT IN (SELECT CUSTCYCLES.CUSTID 
                             FROM CUSTCYCLES 
                             WHERE CUSTCYCLES.CYCLE = 'HARLEY'))

This runs crazy slow.

This is currently being run as a bunch of separate queries that dump data into temporary tables, then several other queries are run to delete the records we don't want, but it's quite clumsy.

Any suggestions? Thanks for any help!

We'll need details of your tables and indexes, and the query plan for the slow query before we can offer much in the way of accurate advice. — Matt Gibson
– Matt Gibson, Commented Nov 30, 2017 at 19:18
The ex is simplified, it would take a lot to write out everything...the fields are nvarchars and are indexed. I'm wondering if there's a better syntax; would a JOIN be better than WHERE clauses? And I tried a CASE statement, like CASE WHEN TYPE = 'COMM' AND CAR = 'FORD' THEN 'N' CASE WHEN TYPE = 'RETAIL' AND CYCLE = 'HARLEY' THEN 'N' ELSE 'Y' END AS KEEP And then WHERE KEEP = 'Y' But that wasn't excluding the right records; if a customer had a Ford and a Chevy he got selected because one record was not a Ford...but I don't want anyone who has a Ford, no matter what other cars. — Brian Battles
– Brian Battles, Commented Nov 30, 2017 at 19:31

Paul Maxwell · Accepted Answer · 2017-12-01 05:57:44Z

1

Try "left excluding joins", where we left join the data that matches the conditions we do not want, and then exclude the matching rows through the where clause:

SELECT
      CUST.*
FROM CUST
LEFT JOIN CUSTCARS ON CUST.CUSTID = CUSTCARS.CUSTID
      AND CUSTCARS.CAR = 'FORD'
      AND CUST.CUSTTYPE = 'COMM'
LEFT JOIN CUSTCYCLES ON CUST.CUSTID = CUSTCYCLES.CUSTID
      AND  CUSTCYCLES.CYCLE = 'HARLEY'
      AND CUST.CUSTTYPE = 'RETAIL'
WHERE CUSTCARS.CUSTID IS NULL
OR CUSTCYCLES.CUSTID IS NULL
;

Whilst I'm here, it might be the OR in your existing query that causes excessive slowness (maybe) so perhaps combining the 2 subqueries to one list would help:

SELECT
      *
FROM CUST
WHERE CUSTID NOT IN (
      SELECT
            CUSTCARS.CUSTID
      FROM CUSTCARS
      INNER JOIN CUST ON CUSTCARS.CUSTID = CUST.CUSTID
      WHERE CUSTCARS.CAR = 'FORD'
      AND CUST.CUSTTYPE = 'COMM'
      UNION ALL
      SELECT
            CUSTCYCLES.CUSTID
      FROM CUSTCYCLES
      INNER JOIN CUST ON CUSTCYCLES.CUSTID = CUST.CUSTID
      WHERE CUSTCYCLES.CYCLE = 'HARLEY'
      AND CUST.CUSTTYPE = 'RETAIL'
      )
;

answered Dec 1, 2017 at 5:57

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

2 Comments

Brian Battles Over a year ago

Thanks, I'll try your suggestions!

Paul Maxwell Over a year ago

Thanks. Which of the two options did you use?

Nate Reynolds · Accepted Answer · 2017-11-30 19:26:46Z

0

Two things come to mind. First, make sure there are indexes for CUSTCARS.CAR and CUSTCYCLES.CYCLE. Second, you might try NOT EXISTS instead of NOT IN.

SELECT * FROM CUST
WHERE 
(CUST.CUSTTYPE = 'COMM' 
 AND NOT EXISTS(SELECT 1 FROM CUSTCARS WHERE CUSTCARS.CAR = 'FORD' 
                AND CUSTCARS.CUSTID=CUST.CUSTID))

OR

(CUST.CUSTTYPE = 'RETAIL' 
 AND NOT EXISTS(SELECT 1 FROM CUSTCYCLES WHERE CUSTCYCLES.CYCLE = 'HARLEY'
                AND CUSTCYCLES.CUSTID=CUST.CUSTID))

answered Nov 30, 2017 at 19:26

Nate Reynolds

1372 silver badges7 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2017-12-01 01:02:44Z

0

I would suggest starting with not exists:

select c.*
from cust c
where not (c.custtype = 'COMM' and
           exists (select 1
                   from custcars cc
                   where cc.custid =c.custid and cc.car = 'FORD'
                  )
          ) and
      not (c.custtype = 'RETAIL' and
           exists (select 1
                   from custcycles cc
                   where cc.custid = c.custid and cc.cycle = 'HARLEY'
                  )
          ) ;

Then, you want to be sure you have indexes on custcar(custid, car) and custcycles(custid, cycle).

edited Dec 1, 2017 at 1:02

answered Nov 30, 2017 at 19:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Brian Battles Over a year ago

Hi Gordon! I tried this, but it's not excluding the right Customers. I need it to exclude a customer if any of his cars are Ford, but it seems like if there are, say, 3 records for CustomerID 111: 111 Ford 111 Chevy 111 Toyota It's not excluding CustomerID 111 If I try SELECT 1 FROM CUSTCARS CC WHERE CC.CUSTID = 111 AND CC.CAR = FORD I get 2 records (of value 1)

Gordon Linoff Over a year ago

@BrianBattles . . . I see exactly what you mean. I fixed the logic.

Collectives™ on Stack Overflow

SQL Server Slow query using subqueries

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related