Need help tuning a SQL statement
- by jeffself
I've got a table that has two fields (custno and custno2) that need to be searched from a query. I didn't design this table, so don't scream at me. :-)
I need to find all records where either the custno or custno2 matches the value returned from a query on the same table based on a titleno.
In other words, the user types in 1234 for the titleno. My query searches the table to find the custno associated with the titleno. It also looks for the custno2 for that titleno. Then it needs to do a search on the same table for all other records that have either the custno or custno2 returned in the previous search in the custno or custno2 fields for those other records.
Here is what I've come up with:
SELECT BILLYR, BILLNO, TITLENO, VINID, TAXPAID, DUEDATE, DATEPIF, PROPDESC
FROM TRCDBA.BILLSPAID
WHERE CUSTNO IN
(select custno from trcdba.billspaid where titleno = '1234'
union select custno2 from trcdba.billspaid where titleno = '1234' and custno2 != '')
OR CUSTNO2 IN
(select custno from trcdba.billspaid where titleno = '1234'
union select custno2 from trcdba.billspaid where titleno = '1234' and custno2 != '')
The query takes about 5-10 seconds to return data. Can it be rewritten to work faster?
