CODEWITHSUNDEEP
sqlt-sqljoinssis
Kaptah
Kaptah

Reputation: 9891

Timeout because of multiple JOINs in SSIS

I have following SQL, which I'm trying to run in a SSIS package ADO.NET Source.

SQL:

select 
b.itemCode as nimike,
e.OrdCustNum as asiakas,
d.DlvCustNum as asiakas2,
b.qty as maara,
c.Price as m_hinta,
c.CostPrice as o_hinta,
d.SalesRep as kasittelija,
d.orderNum as tilausnro,
d.VatDate as tap_pvm,
c.stock as varasto,
'Delivery' as til_tyyppi
FROM 
"PUB"."DlvLine" b
JOIN "PUB"."slsLine" c
    ON 
        b.itemCode = c.itemCode 
        and b.OrderNum = c.orderNum 
JOIN "PUB"."invLine" d
    ON 
        b.itemCode = d.ItemCode
        and b.InvNum = d.InvNum
        and b.DlvNum = d.DlvNum
        and b.OrderNum  = d.orderNum
JOIN "PUB"."SlsOrder" e
ON
    c.OrderNum = e.OrderNum
WHERE 
d.VatDate > '2019-10-22'
and c.AllDelivered = '1'

Preview results in timeout after 30 seconds, error message: 

ERROR [HYT00] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Timeout expired.
ERROR [HY000] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Query aborted on user request (7495)

I wonder if it's about multiple JOINs, if I remove the second JOIN clause the query runs fine. What are my options?

Any help would be highly appreciated.

Upvotes: 1

Views: 139

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

lets filter the invoice line item first before joining to your main table. I'm sure you have multiple invoice line item per order number, since you are not displaying any amount or qty, lets add distinct invoice line instead.

select 
    b.itemCode as nimike,
    e.OrdCustNum as asiakas,
    d.DlvCustNum as asiakas2,
    b.qty as maara,
    c.Price as m_hinta,
    c.CostPrice as o_hinta,
    d.SalesRep as kasittelija,
    d.orderNum as tilausnro,
    d.VatDate as tap_pvm,
    c.stock as varasto,
    'Delivery' as til_tyyppi
FROM 
"PUB"."DlvLine" b
JOIN "PUB"."slsLine" c
    ON 
        b.itemCode = c.itemCode 
        and b.OrderNum = c.orderNum 
JOIN 
    (select distinct ItemCode, DlvCustNum, SalesRep, orderNum, VatDate, InvNum, DlvNum, orderNum
        from "PUB"."invLine" where VatDate > '2019-10-22') d
    ON 
        b.itemCode = d.ItemCode
        and b.InvNum = d.InvNum
        and b.DlvNum = d.DlvNum
        and b.OrderNum  = d.orderNum
JOIN "PUB"."SlsOrder" e ON
    c.OrderNum = e.OrderNum
and c.AllDelivered = '1'

Upvotes: 2

Related Questions