SQL代码如下,数据不多。但执行一次要30多秒该怎样优化?
SELECT A.sPoNo ,
B.sSupplierName ,
C.sCurrencyName ,
A.sContractNo ,
D.sUserName ,
A.dCreateDate ,
A.dApprovedDate ,
E.sBillStatusName ,
A.sSupplierPoNo ,
A.iID
FROM poBillMst A
LEFT JOIN cuSupplier B ON A.iSupplierId = B.iId
LEFT JOIN cuCurrency C ON A.iCurrencyId = C.iId
LEFT JOIN smUser D ON A.sCreator = D.sUserID
LEFT JOIN vwpbBillStatus E ON A.iStatus = E.sBillStatusCode
WHERE A.sOperateMode = 'BYAPPLYORDER'
AND A.sMaterialType = 'F'
AND A.sBillName = '04'
--AND 1 = 1
AND ( sPoNo LIKE '%62940%'
OR sSupplierPoNo LIKE '%62940%'
OR EXISTS ( SELECT TOP 1
1
FROM poBillDtl pbd
WHERE ipoBillMstId = A.iId
AND EXISTS ( SELECT TOP 1
1
FROM vwsdOrderInfoByStyleSet
WHERE pbd.sOrderNo = sOrderNo
AND sLotNo LIKE '%62940%'
OR sStyleNo LIKE '%62940%' ) )
)
SELECT A.sPoNo ,
B.sSupplierName ,
C.sCurrencyName ,
A.sContractNo ,
D.sUserName ,
A.dCreateDate ,
A.dApprovedDate ,
E.sBillStatusName ,
A.sSupplierPoNo ,
A.iID
FROM poBillMst A
LEFT JOIN cuSupplier B ON A.iSupplierId = B.iId
LEFT JOIN cuCurrency C ON A.iCurrencyId = C.iId
LEFT JOIN smUser D ON A.sCreator = D.sUserID
LEFT JOIN vwpbBillStatus E ON A.iStatus = E.sBillStatusCode
WHERE A.sOperateMode = 'BYAPPLYORDER'
AND A.sMaterialType = 'F'
AND A.sBillName = '04'
--AND 1 = 1
AND ( sPoNo LIKE '%62940%'
OR sSupplierPoNo LIKE '%62940%'
OR EXISTS ( SELECT TOP 1
1
FROM poBillDtl pbd
WHERE ipoBillMstId = A.iId
AND EXISTS ( SELECT TOP 1
1
FROM vwsdOrderInfoByStyleSet
WHERE pbd.sOrderNo = sOrderNo
AND sLotNo LIKE '%62940%'
OR sStyleNo LIKE '%62940%' ) )
)
B.sSupplierName ,
C.sCurrencyName ,
A.sContractNo ,
D.sUserName ,
A.dCreateDate ,
A.dApprovedDate ,
E.sBillStatusName ,
A.sSupplierPoNo ,
A.iID
FROM poBillMst A
LEFT JOIN cuSupplier B ON A.iSupplierId = B.iId
LEFT JOIN cuCurrency C ON A.iCurrencyId = C.iId
LEFT JOIN smUser D ON A.sCreator = D.sUserID
LEFT JOIN vwpbBillStatus E ON A.iStatus = E.sBillStatusCode
left join poBillDtl f on a.iid=f.ipoBillMstId
left join vwsdOrderInfoByStyleSet g on f.sOrderNo=g.sOrderNo
WHERE A.sOperateMode = 'BYAPPLYORDER'
AND A.sMaterialType = 'F'
AND A.sBillName = '04'
AND ( sPoNo LIKE '%62940%'
OR sSupplierPoNo LIKE '%62940%')
and (g.sLotNo in not null or g.sStyleNo is not null)试一下。
B.sSupplierName,
C.sCurrencyName,
A.sContractNo,
D.sUserName,
A.dCreateDate,
A.dApprovedDate,
E.sBillStatusName,
A.sSupplierPoNo,
A.iID
FROM poBillMst A
LEFT JOIN cuSupplier B
ON A.iSupplierId = B.iId
LEFT JOIN cuCurrency C
ON A.iCurrencyId = C.iId
LEFT JOIN smUser D
ON A.sCreator = D.sUserID
LEFT JOIN vwpbBillStatus E
ON A.iStatus = E.sBillStatusCode
WHERE A.sOperateMode = 'BYAPPLYORDER'
AND A.sMaterialType = 'F'
AND A.sBillName = '04'
--AND 1 = 1
AND ( A.sPoNo LIKE '%62940%'
OR A.sSupplierPoNo LIKE '%62940%'
OR EXISTS (SELECT TOP 1 1
FROM poBillDtl pbd,
vwsdOrderInfoByStyleSet oss
WHERE pbd.ipoBillMstId = A.iId
AND pbd.sOrderNo = oss.sOrderNo
AND ( oss.sLotNo LIKE '%62940%'
OR oss.sStyleNo LIKE '%62940%' )))
--#1.只看到最后的SQL能合并
--#2.加索引来优化吧^_^
vwsdOrderInfoByStyleSet表是订单明细视图,poBillMst表是采购单主表。所以左连接会冗余记录。to: (Shawn)(永生 )
是对sOrderNo, sLotNo加索引吗?
vwsdOrderInfoByStyleSet是视图,怎么加索引?