Need to speed up the results of this SQL statement. Any advice?

Question

I've got the following SQL Statement that needs some major speed up. The problem is I need to search on two fields, where each of them is calling several sub-selects. Is there a way to join the two fields together so I call the sub-selects only once?

SELECT billyr, billno, propacct, vinid, taxpaid, duedate, datepif, propdesc
FROM trcdba.billspaid
WHERE date(datepif) > '01/06/2009'
AND date(datepif) <= '01/06/2010'
AND custno in
 (select custno from cwdba.txpytaxid where taxpayerno in
  (select taxpayerno from cwdba.txpyaccts where accountno in
   (select accountno from rtadba.reasacct where controlno = 1234567)))
OR custno2 in
 (select custno from cwdba.txpytaxid where taxpayerno in
  (select taxpayerno from cwdba.txpyaccts where accountno in
   (select accountno from rtadba.reasacct where controlno = 1234567)))

Answer 1

I would use joins instead of the embedded sub-queries.

Answer 2

Here's the same thing using JOIN instead of sub queries.

SELECT billyr, billno, propacct, vinid, taxpaid, duedate, datepif, propdesc
FROM billspaid
INNER JOIN txpytaxid
  ON txpytaxid.custno = billspaid.custno OR txpytaxid.custno = billspaid.custno2
INNER JOIN txpyaccts
  ON txpyaccts.taxpayerno = txpytaxid.taxpayerno
INNER JOIN reasacct
  ON reasacct.accountno = txpyaccts.accountno AND reasacct.controlno = 1234567
WHERE date(datepif) > '01/06/2009'
  AND date(datepif) <= '01/06/2010'

However, if the OR in the JOIN is giving you performance problems, you can always try using a union:

(SELECT billyr, billno, propacct, vinid, taxpaid, duedate, datepif, propdesc
FROM billspaid
INNER JOIN txpytaxid
  ON txpytaxid.custno = billspaid.custno
INNER JOIN txpyaccts
  ON txpyaccts.taxpayerno = txpytaxid.taxpayerno
INNER JOIN reasacct
  ON reasacct.accountno = txpyaccts.accountno AND reasacct.controlno = 1234567
WHERE date(datepif) > '01/06/2009'
  AND date(datepif) <= '01/06/2010')
UNION
(SELECT billyr, billno, propacct, vinid, taxpaid, duedate, datepif, propdesc
FROM billspaid
INNER JOIN txpytaxid
  ON txpytaxid.custno = billspaid.custno2
INNER JOIN txpyaccts
  ON txpyaccts.taxpayerno = txpytaxid.taxpayerno
INNER JOIN reasacct
  ON reasacct.accountno = txpyaccts.accountno AND reasacct.controlno = 1234567
WHERE date(datepif) > '01/06/2009'
  AND date(datepif) <= '01/06/2010')

Answer 3

when you use a function on the column:

date(datepif) > '01/06/2009'
AND date(datepif) <= '01/06/2010'

an index will NOT be used. Try something like this

datepif > someconversionhere('01/06/2009')
AND datepif <= someconversionhere('01/06/2010')

Use inner joins too. There isn't any info in the question to indicate table size or if there is an index or not, so this is a guess and should work best if there are many more rows in billspaid for the date range vs rows that match the joining tables for r.controlno = 1234567, which I suspect is the case:

SELECT 
    COALESCE(b1.billyr,b2.billyr)           AS billyr
        ,COALESCE(b1.billno,b2.billno)      AS billno
        ,COALESCE(b1.propacct,b2.propacct)  AS propacct
        ,COALESCE(b1.vinid,b2.vinid)        AS vinid
        ,COALESCE(b1.taxpaid,b2.taxpaid)    AS taxpaid
        ,COALESCE(b1.duedate,b2.duedate)    AS duedate
        ,COALESCE(b1.datepif,b2.datepif)    AS datepif
        ,COALESCE(b1.propdesc,b2.propdesc)  AS propdesc
    FROM rtadba.reasacct                  r
        INNER JOIN cwdba.txpyaccts        a ON r.accountno=t.accountno
        INNER JOIN cwdba.txpytaxid        t ON a.taxpayerno=t.taxpayerno
        LEFT OUTER JOIN trcdba.billspaid b1 ON t.custno=b1.custno AND b1.datepif > someconversionhere('01/06/2009') AND b1.datepif <= someconversionhere('01/06/2010')
        LEFT OUTER JOIN trcdba.billspaid b2 ON t.custno2=b2.custno AND b2.datepif > someconversionhere('01/06/2009') AND b2.datepif <= someconversionhere('01/06/2010')
    WHERE r.controlno = 1234567
      AND COALESCE(b1.custno,b2.custno) IS NOT NULL

create an index for each of these:

rtadba.reasacct.controlno and cover on accountno
cwdba.txpyaccts.accountno and cover on taxpayerno
cwdba.txpytaxid.taxpayerno and cover on custno
trcdba.billspaid.custno +datepif
trcdba.billspaid.custno2 +datepif

Answer 4

Use EXISTS instead of IN ( unless the result set of the IN subquery is very small).

If you do UNION instead of OR ( which should be functionally equivalent ) use UNION ALL instead.