[步骤]
SELECT alias.[ID],alias.[CustomerID],alias.[SellerId],alias.[ServerUrl],alias.[FinancesReportID],alias.[OrderSourceID],alias.[MarketplaceName],alias.[AmazonOrderId],alias.[SellerOrderId],alias.[FinancialEventType],alias.[EventItemType],alias.[ShipmentItemType],alias.[EventDetailType],alias.[CurrencyCode],alias.[CurrencyAmount],alias.[SellerSKU],alias.[FnSKU],alias.[ASIN],alias.[OrderItemId],alias.[OrderAdjustmentItemId],alias.[QuantityShipped],alias.[PromotionId],alias.[PromotionType],alias.[PointsGrantedCurrencyCode],alias.[PointsGrantedCurrencyAmount],alias.[PointsReturnedCurrencyCode],alias.[PointsReturnedCurrencyAmount],alias.[PostedDate],alias.[AddTime],alias.[IsBuildFee],alias.[BuildFeeTime],alias.[ManualRefresh],alias.[DataServiceID],alias.[FinancePayoutGUID],alias.[IsRealTime],alias.[Remark]
FROM RB_Amazon_ListFinancialEvents AS alias WITH (NOLOCK)
WHERE ISNULL(alias.IsBuildFee,0)=0
0
= '2024/3/10 9:32:47'
AND alias.PostedDate
AND FinancialEventType NOT IN ( 'DebtRecoveryEvents', 'LoanServicingEvents' )
AND NOT (ISNULL(alias.FinancialEventType,'')='ServiceFeeEvents' AND ISNULL(alias.EventItemType,'')='FeeList' AND ISNULL(alias.EventDetailType,'')='FBAPerUnitFulfillmentFee')
AND
(
AmazonOrderId IS NULL
OR AmazonOrderId LIKE 'S%'
OR (AmazonOrderId!='' AND OrderItemId IS NULL AND OrderAdjustmentItemId IS NULL)
)
AND EventDetailType NOT IN ( 'TotalAmount', 'ReserveDebit', 'ReserveCredit', 'FailedDisbursement' )
AND
(
alias.AmazonOrderId NOT IN
(
SELECT roi.ClientOrderCode
FROM RB_OrderInfo roi WITH (NOLOCK)
WHERE roi.CustomerID = alias.CustomerID
AND roi.OrderSourceID = alias.OrderSourceID
AND roi.ClientOrderCode = alias.AmazonOrderId
AND roi.DownLoadUpdateTime != '2000-01-01 00:00:00.000'
AND roi.IsStockOrder = 0
)
OR
(
AmazonOrderId != ''
AND OrderItemId IS NULL
AND OrderAdjustmentItemId IS NULL
)
)
该查询语句 主表 RB_Amazon_ListFinancialEvents 使用了 大量的OR 条件，没有一个 唯一 有效的，导致SQL 索引 无法选择，而选择全表扫描
[结果]
[期望]
建议加入有效的条件，让查询语句走索引 减少 磁盘的读取