Optimising SQL Query with multiple joins, reducing query speed-CodePudding

enter image description here

i am quite new to creating sql queries and would like some advise on how i can optimise the code below. I have attached the relationship of table above, any feedback or direction you could point me to will be appreciated. the current query seems to be taking quite long to process.

SELECT CUSTINVOICETRANS.INVENTTRANSID, CUSTINVOICETRANS.INVOICEDATE, CUSTINVOICETRANS.INVOICEID, CUSTINVOICETRANS.ITEMID, CUSTINVOICETRANS.LINEAMOUNT, CUSTINVOICETRANS.LINEAMOUNTTAX, CUSTINVOICETRANS.ORIGSALESID, CUSTINVOICETRANS.QTY, CUSTINVOICETRANS.SUMLINEDISC, CUSTINVOICEJOUR.CUSTGROUP, CUSTINVOICEJOUR.INVOICEACCOUNT, CUSTINVOICEJOUR.SALESID, SALESTABLE.VCORDERMODE, SALESTABLE.VCORDERRT, VCSALESTABLEINFO.RCVDATE, VCSALESTABLEINFO.SHPCSTMCD, VCSALESTABLEINFO.VCORIGINALINVOICEDATE, SALESLINE.DATAAREADID, SALESLINE.INVENTTRANSID, VCSALESLINEINFO.RLLINEID

FROM CUSTINVOICETRANS

INNER JOIN CUSTINVOICEJOUR
ON CUSTINVOICETRANS.INVOICEID = CUSTINVOICEJOUR.INVOICEID
AND CUSTINVOICETRANS.INVOICEDATE = CUSTINVOICEJOUR.INVOICEDATE
AND CUSTINVOICETRANS.INVOICEDATE>=DATEADD(DAY,-7,GETDATE())

INNER JOIN SALESTABLE
ON CUSTINVOICETRANS.ORIGSALESID = SALESTABLE.SALESID
AND CUSTINVOICETRANS.INVOICEDATE>=DATEADD(DAY,-7,GETDATE())

INNER JOIN VCSALESTABLEINFO
ON CUSTINVOICETRANS.ORIGSALESID = VCSALESTABLEINFO.SALESID
AND CUSTINVOICETRANS.INVOICEDATE>=DATEADD(DAY,-7,GETDATE())

INNER JOIN SALESLINE
ON CUSTINVOICETRANS.ORIGSALESID = SALESLINE.SALESID
AND CUSTINVOICETRANS.INVOICEDATE>=DATEADD(DAY,-7,GETDATE())

INNER JOIN VCSALESLINEINFO
ON SALESLINE.INVENTTRANSID = VCSALESLINEINFO.INVENTTRANSID```

CodePudding user response：

You can use cte to filter first your customer invoice transactions before joining other tables.

WITH cte as
    (
        SELECT INVENTTRANSID, INVOICEDATE, INVOICEID
            , ITEMID, LINEAMOUNT, LINEAMOUNTTAX
            , ORIGSALESID, QTY, SUMLINEDISC  
        FROM CUSTINVOICETRANS WHERE INVOICEDATE>=DATEADD(DAY,-7,GETDATE())
    )
SELECT t1.*, t2.CUSTGROUP, t2.INVOICEACCOUNT, t2.SALESID
    , t3.VCORDERMODE, t3.VCORDERRT
    , t4.RCVDATE, t4.SHPCSTMCD, t4.VCORIGINALINVOICEDATE
    , t5.DATAAREADID, t5.INVENTTRANSID
    , t6.RLLINEID
FROM cte t1
INNER JOIN CUSTINVOICEJOUR t2 on t2.INVOICEID = t1.INVOICEID and t1.INVOICEDATE = t2.INVOICEDATE
INNER JOIN SALESTABLE t3 on t3.SALESID = t1.ORIGSALESID  
INNER JOIN VCSALESTABLEINFO t4 on t4.SALESID = t1.ORIGSALESID
INNER JOIN SALESLINE t5 on t5.SALESID = t1.ORIGSALESID
INNER JOIN VCSALESLINEINFO t6 on t6.INVENTTRANSID = t5.INVENTTRANSID

CodePudding user response：

Pull the first INNER JOIN outcome to a dataframe / temp table t1.
Join the same with second INNER Join dataset.
pull the data into another temp table / data frame.
Drop the temp table t1 at the end.
Follow the step for for subsequesnt joins.

Regards