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

Question

I have the following SQL query that requires some significant speed. The problem is that I need to search in two fields, where each of them brings up several subsamples. Is there a way to combine the two fields together, so I call 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 inline subqueries.

Answer 2

The same thing here, using JOIN instead of subqueries.

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 OR in JOIN gives you performance problems, you can always try using 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

:

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

. -

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

. , , , , , , r.controlno = 1234567, , , :

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

:

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

EXISTS IN ( IN ).

UNION OR ( ), UNION ALL.