由于SSIS中的多个JOIN导致超时

debugcn 发表于 Dev

卡帕塔

我有以下SQL，我试图在SSIS包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'

30秒后预览结果超时，错误消息：

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)

我想知道它是否涉及多个JOIN，如果我删除第二个JOIN子句，则查询运行良好。我有什么选择？

任何帮助将不胜感激。

骄傲的埃德

让我们invoice line在加入主表之前先过滤项目。我确定您每个人都有多个发票行项目，order number,因为您没有显示任何amount或qty，distinct而是添加发票行。

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'

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。