1. 问题描述，分析
Amazon listing管理搜索上架方式功能
此sql超时，17小时执行不完
分析可知，条件查询使用了函数，in
and vtb.AddTime >='2022-06-02' --优化后的
--AND DATEDIFF(day,'2022-06-02',vtb.AddTime)>=0 -- 原语句
and vtb.AddTime <='2022-06-06' --优化后的
--AND DATEDIFF(day,'2022-06-06',vtb.AddTime)<=0 -- 原语句
and vtb.IsOffShelf in(0,1,2,4,5) --优化后的
--and isnull(IsOffShelf,0)<>3 --原语句
2.解决方案
将where 条件里的 DATEDIFF函数替换，isnull(IsOffShelf,0)<>3 替换，优化后从几小时到3秒执行完成，
and vtb.AddTime >='2022-06-02'
--AND DATEDIFF(day,'2022-06-02',vtb.AddTime)>=0
and vtb.AddTime <='2022-06-06'
--AND DATEDIFF(day,'2022-06-06',vtb.AddTime)<=0
and vtb.IsOffShelf in(0,1,2,4,5)
--and isnull(IsOffShelf,0)<>3
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY StopAndNullStockProduct desc, OrderSourceSKUID desc ) AS [PageIndex] ,*
FROM ( select vtb.ID,
vtb.ParentID,
vtb.IsVariation,
vtb.IsFirstLevelProduct,
vtb.IsSaleProduct,
vtb.isEmergencyStock,
vtb.ExpectedArrivalDays,
vtb.DeliveryDay
from [RB_Amazon_Product_Base] (nolock) vtb
LEFT JOIN RB_StockClearDetailed scd (nolock) ON(vtb.OrderSourceSKUID=scd.ListId AND vtb.CustomerID=scd.CustomerID AND (clearType=1 OR clearType=3))
LEFT JOIN RB_StockClearStrategy scs (nolock) ON(scd.scsID=scs.ID AND scd.CustomerID=scs.CustomerID)
LEFT JOIN (SELECT sum(GoodNum) AS Inventory,WareHouseID,SKU,CustomerID FROM RB_WareHouse_Product (nolock) GROUP BY WareHouseID,SKU,CustomerID) AS tbWHN ON(vtb.WID=tbWHN.WareHouseID AND vtb.SKU=tbWHN.SKU AND vtb.CustomerID=tbWHN.CustomerID)
LEFT JOIN RB_WareHouse w (nolock) ON w.CustomerID=vtb.CustomerID and w.ID=vtb.DefaultWarehouseID
LEFT JOIN RB_OrderSource o (nolock) on vtb.CustomerID=o.CustomerID and vtb.OrderSourceID = o.ID
Left join RB_Product_Admin pa(nolock) on vtb.CustomerID=pa.CustomerID and vtb.SKU = pa.SKU
left join RB_Product p (nolock) on p.CustomerID=vtb.CustomerID and vtb.SKU = p.SKU
left join RB_WareHouse wh(nolock) on wh.CustomerID=p.CustomerID and wh.ID=p.DefaultLocalWarehouse
left join RB_WareHouse swh(nolock) on swh.CustomerID=vtb.CustomerID and swh.ID=vtb.TheWareHouseID
left join RB_Amazon_NotToFbaReason ntf (nolock) on vtb.CustomerID=ntf.CustomerID and vtb.NotToFbaReasonId=ntf.ID
WHERE vtb.CustomerID=1 AND vtb.IsDelete=0
and vtb.AddTime >='2022-06-02'
--AND DATEDIFF(day,'2022-06-02',vtb.AddTime)>=0
and vtb.AddTime <='2022-06-06'
--AND DATEDIFF(day,'2022-06-06',vtb.AddTime)<=0
and vtb.IsOffShelf in(0,1,2,4,5)
--and isnull(IsOffShelf,0)<>3
and vtb.OrderSourceSKUID>0
and vtb.OnShelfMode in (SELECT CONVERT(INT, item) FROM Charsplitforin(9, ','))
) AS TempTable
) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 20
--PARAMS:@CustomerID = 1 ,@UserID = 7172 ,@InputTimeB = 2022-06-02 ,@InputTimeE = 2022-06-06 ,@Count1 = ,@Count2 = ,@OnShelfMode = 9 ,
--MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
select top 1 * from [RB_Amazon_Product_Base] nolock
3.原sql
SQL:
SELECT * FROM (
SELECT
ROW_NUMBER() OVER (ORDER BY StopAndNullStockProduct desc, OrderSourceSKUID desc ) AS [PageIndex] ,* FROM ( select vtb.ID,
vtb.ParentID,
vtb.IsVariation,
pa.RecommendValue,
vtb.IsFirstLevelProduct,
vtb.IsSaleProduct,
vtb.isEmergencyStock,
vtb.ExpectedArrivalDays,
vtb.DeliveryDay,
Isnull(BuyBoxNum1, 0) BuyBoxNum1,
Isnull(BuyBoxNum2, 0) BuyBoxNum2,
Isnull(AllBuyBoxNum1, 0) AllBuyBoxNum1,
Isnull(BuyBoxNum3, 0) BuyBoxNum3,
Isnull(AllBuyBoxNum2, 0) AllBuyBoxNum2,
Isnull(AllBuyBoxNum3, 0) AllBuyBoxNum3,
Isnull(OrdinaryBuyBoxNum1, 0) OrdinaryBuyBoxNum1,
Isnull(OrdinaryAllBuyBoxNum1, 0) OrdinaryAllBuyBoxNum1,
Isnull(OrdinaryBuyBoxNum2, 0) OrdinaryBuyBoxNum2,
Isnull(OrdinaryAllBuyBoxNum2, 0) OrdinaryAllBuyBoxNum2,
Isnull(OrdinaryBuyBoxNum3, 0) OrdinaryBuyBoxNum3,
Isnull(OrdinaryAllBuyBoxNum3, 0) OrdinaryAllBuyBoxNum3,
AUID,
vtb.ImageUrl,
vtb.ClientSKU,
vtb.ProductName,
vtb.ProductNameCN,
PID,
vtb.SalePrice,
vtb.LastBuyPrice,
vtb.LastSupplierPrice,
vtb.GrossWeight,
vtb.PackWeight,
vtb.IsGroup,
vtb.ProductProperty,
o.OrderSourceCountry,
o.OrderSourceName,
cgzNum,
ystzNum,
ProductNum,
ReadyLibraryNum,
todaysaleNum,
zsaleNum,
qsaleNum,
sqsaleNum,
WeekSaleNum,
TwoWeekSaleNum,
vtb.APIID,
DevIDSellerId,
Cycle,
vtb.SafetyDays,
WPID,
LastUPdateTime,
vtb.AddCycle,
vtb.HaulageTime,
vtb.EstimatedSaleNum,
vtb.BuyTime,
IsBuyBox,
RobBuyBox,
Kl,
vtb.AvgDailySales,
ToBuyBoxProduct,
IsTiming,
vtb.WID,
vtb.CustomerID,
vtb.BasePrice,
vtb.OrderSourceID,
vtb.UpdateTime,
vtb.FNSKU,
vtb.LockNum,
IsFBA,
vtb.OrderSourceSKU,
vtb.WareHouseName,
ASINorItemID,
SellerType,
vtb.SKU,
vtb.BusinessTeam,
vtb.BusinessTeamName,
vtb.OrderSourceSKUID,
KykcNum,
CnKyNum,
CnWPID,
vtb.WareHouseType,
msaleNum,
lastmsaleNum,
zhanyong,
vtb.IsActive,
vtb.AddTime,
daysNum,
BuyBoxUpdateTime,
SyncLastUpdateTime,
isBeiKu,
LastUpTiming,
LastDownTiming,
rankNum,
rankZTNum,
rankQTNum,
rankSQNum,
rankClassName,
bhnum,
IsOffShelf,
ListingPrice,
ListingSoldNum,
StopProduct,
StopAndNullStockProduct,
BusinessAdminID,
BusinessUserName,
vtb.Currency,
ExchangeRMB,
FBASeller_Num,
ThreeMonthSaleNum,
RobBuyBoxType,
Attention,
Isnull(LowestPriceBuyBoxNum1, 0) LowestPriceBuyBoxNum1,
Isnull(LowestPriceAllBuyBoxNum1, 0) LowestPriceAllBuyBoxNum1,
Isnull(LowestPriceBuyBoxNum2, 0) LowestPriceBuyBoxNum2,
Isnull(LowestPriceAllBuyBoxNum2, 0) LowestPriceAllBuyBoxNum2,
Isnull(LowestPriceBuyBoxNum3, 0) LowestPriceBuyBoxNum3,
Isnull(LowestPriceAllBuyBoxNum3, 0) LowestPriceAllBuyBoxNum3,
ProductVitalityType,
ProductNewType,
UnsalableDayNum,
AmazonShelvesNum,
EbayShelvesNum,
SMTShelvesNum,
WishShelvesNum,
OtherShelvesNum,
TotalShelvesNum,
vtb.FirstLegShippingType,
IsRemind,
(SELECT TOP 1 ID FROM RB_Listing_Sync_GoodNum sync (nolock) WHERE sync.IsActive = 1 AND sync.ListingType = 2 AND sync.ListingID = vtb.OrderSourceSKUID AND sync.CustomerID = vtb.CustomerID) IsSet,
Isnull(vtb.BrandType, 0) BrandType,
Isnull(scd.ID, 0) AS IsClear,
scs.Name,
scd.WarehouseNum,
( WarehouseNum - Inventory ) AS ClearInventory,
vtb.IsAmazonListingReview,
vtb.ReviewStar,
vtb.ReviewNum,
vtb.ReviewUnReadNum,
vtb.LastReviewCollectionTime,
vtb.LastSynReviewTime,
vtb.BadReviewNum,
vtb.ThreeDayReviewNum,
vtb.SevenDayDayReviewNum,
vtb.IsAmazonKeywordRank,
vtb.FBA_Reserved_CustomerOrders,
vtb.FBA_Reserved_Transfers,
vtb.FBA_Reserved_Processing,
YesterdaySessions,
BeforeYesterdaySessions,
HeretoforeSessions,
Isnull(YesterdayUnitSessionPercentage, 0) AS YesterdayUnitSessionPercentage,
Isnull(BeforeYesterdayUnitSessionPercentage, 0) AS BeforeYesterdayUnitSessionPercentage,
Isnull(HeretoforeUnitSessionPercentage, 0) AS HeretoforeUnitSessionPercentage,
vtb.TaxPoint,
vtb.TaxRate,
Isnull(vtb.IsFBABarCode, 0) IsFBABarCode,
Isnull(vtb.IsRecommendOcean, 0) IsRecommendOcean,
Isnull(w.WareHouseName, '') AS DefaultWareHouseName,
Isnull(vtb.YesterdaySpend, 0) YesterdaySpend,
Isnull(vtb.TotalSpend, 0) TotalSpend,
Isnull(vtb.YesterdayAvgCPC, 0) YesterdayAvgCPC,
Isnull(vtb.TotalAvgCPC, 0) TotalAvgCPC,
Isnull(vtb.YesterdayClicks, 0) YesterdayClicks,
Isnull(vtb.TotalClicks, 0) TotalClicks,
Isnull(vtb.YesterdayImpressions, 0) YesterdayImpressions,
Isnull(vtb.TotalImpressions, 0) TotalImpressions,
Isnull(vtb.IsEFN, 0) AS IsEFN,
vtb.EFNCountry,
Isnull(vtb.IsCheckDeclarationCode, 0) IsCheckDeclarationCode,
Isnull(vtb.IsCheckTransport, 0) IsCheckTransport,
Isnull(vtb.IsSalesRank, 0) IsSalesRank,
p.ProdutcArrivalDate,
vtb.HolidaysID,
wh.WareHouseName AS DefaultLocalWarehouse,
swh.WareHouseName SupplierWareHouseName,
vtb.AddAdminName,
vtb.IsSetNoSupplyReason,
vtb.IsEmergencyStockReminder,
vtb.TranslateStatus,
p.MinSaleNum,
p.WithBattery,
pa.IsAmazonSale,
pa.NoSalePlatform,
vtb.TemplateID,
vtb.LableColor,
vtb.LableName,
p.PictureSource,
pa.ImageTime,
vtb.ProfitMargin,
vtb.PublishTime,
vtb.MonthAvgProfits,
vtb.PlanID,
vtb.PlanDetailId,
vtb.PlanName,
vtb.IsAgainPublish,
vtb.IsAutoAdjustPrice,
vtb.IsAutoLowerShelf,
vtb.IsAutoPromotion,
OnShelfMode,
vtb.OrderSourceSkuOrderTime,
vtb.AsinOrderTime,
p.IsIntelligentStocking,
ntf.Reason,
vtb.WaitToFbaType ,
vtb.SevenDaySales,
vtb.SevenDayAcos,
vtb.DefaultWarehouseID,
vtb.RecommendedOrderQuantity,
vtb.DiscountStartTime,
vtb.DiscountEndTime,
vtb.DiscountSalesPrice,
p.ImageUrl as ProductImageUrl,
p.ProductName as ProductTitle,
vtb.IsAdvertising,
vtb.UnsellableQuatity,
pa.NoSaleCountry,
vtb.IsNARF,
vtb.MaximumShipmentQuantity,
vtb.RecommendFinalNum,
vtb.MonitorStockFinalNum,
vtb.IsJoinSmall,
vtb.RecommendJoinSmall,
vtb.NoJoinSmallReason from [RB_Amazon_Product_Base] (nolock) vtb LEFT JOIN RB_StockClearDetailed scd (nolock) ON(vtb.OrderSourceSKUID=scd.ListId AND vtb.CustomerID=scd.CustomerID AND (clearType=1 OR clearType=3))
LEFT JOIN RB_StockClearStrategy scs (nolock) ON(scd.scsID=scs.ID AND scd.CustomerID=scs.CustomerID)
LEFT JOIN (SELECT sum(GoodNum) AS Inventory,WareHouseID,SKU,CustomerID FROM RB_WareHouse_Product (nolock) GROUP BY WareHouseID,SKU,CustomerID) AS tbWHN ON(vtb.WID=tbWHN.WareHouseID AND vtb.SKU=tbWHN.SKU AND vtb.CustomerID=tbWHN.CustomerID)
LEFT JOIN RB_WareHouse w (nolock) ON w.CustomerID=vtb.CustomerID and w.ID=vtb.DefaultWarehouseID
LEFT JOIN RB_OrderSource o (nolock) on vtb.CustomerID=o.CustomerID and vtb.OrderSourceID = o.ID
Left join RB_Product_Admin pa(nolock) on vtb.CustomerID=pa.CustomerID and vtb.SKU = pa.SKU
left join RB_Product p (nolock) on p.CustomerID=vtb.CustomerID and vtb.SKU = p.SKU
left join RB_WareHouse wh(nolock) on wh.CustomerID=p.CustomerID and wh.ID=p.DefaultLocalWarehouse
left join RB_WareHouse swh(nolock) on swh.CustomerID=vtb.CustomerID and swh.ID=vtb.TheWareHouseID
left join RB_Amazon_NotToFbaReason ntf (nolock) on vtb.CustomerID=ntf.CustomerID and vtb.NotToFbaReasonId=ntf.ID WHERE vtb.CustomerID=@CustomerID AND vtb.IsDelete=0 AND DATEDIFF(day,@InputTimeB,vtb.AddTime)>=0 AND DATEDIFF(day,@InputTimeE,vtb.AddTime)<=0 and isnull(IsOffShelf,0)<>3 and vtb.OrderSourceSKUID>0 And vtb.OnShelfMode in (SELECT CONVERT(INT, item) FROM Charsplitforin(@OnShelfMode, ',')) ) AS TempTable ) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 20 option(maxdop 8)
PARAMS:@CustomerID = 1 ,@UserID = 7172 ,@InputTimeB = 2022-06-02 ,@InputTimeE = 2022-06-06 ,@Count1 = ,@Count2 = ,@OnShelfMode = 9 ,
MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
