Need help tuning a SQL statement

Question

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?

Answer 1

A simple quick boost might be to change your 'union' to 'union all' (I am a sql server guy - does this apply to other dbs?)

Otherwise, it may require some db-specific tricks - which db are you using?

Answer 2

Also, in your proposed solution, you filter the BILLSPAID table against titleno = 1234 four times. Depending on your DB platform, the number of items with duplicate titleno, and whether titleno is indexed, you might gain significantly by using a temp table. Again, depending on your platform, that could look something like:

SELECT * FROM TRCDBA.BILLSPAID INTO MyTempTable 
WHERE titleno = '1234';

SELECT BILLYR, BILLNO, TITLENO, VINID, TAXPAID, DUEDATE, DATEPIF, PROPDESC 
FROM MyTempTable
WHERE CUSTNO IN 
 (select custno from MyTempTable 
  union all select custno2 from MyTempTable where custno2 != '') 
OR CUSTNO2 IN 
 (select custno from MyTempTable 
  union all select custno2 from MyTempTable where custno2 != '') 

Answer 3

This cries out for proper normalization. If you have multiple customers for the same bill,they should be in a related table. When you have fields like custno, custno2, you generally have a design problem that needs to be fixed.