DECLARE @CustomerID INT=1
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = Object_id(N'tempdb..#tbOrderSourceID')
AND type = 'U')
DROP TABLE #tbOrderSourceID
CREATE TABLE #tbOrderSourceID
(
Id INT PRIMARY KEY
)
INSERT INTO #tbOrderSourceID
SELECT item AS Id
FROM Charsplitforin('13238,10481,10103,10372,11237,11238,10792,10489,10102,8960,7962,6759,6240,16641,13245,16107,16101,15936,15889,15791,14893,14652,10482,10478,10101,8592,8134,7783,17225,17814,17813,17816,10500,6635,7172,10235,9500,12626,18054,18063,10232,12824,9636,13235,13354,13062,7100,18224,18223,10791,10227,13645,9502,9674,10828,9656,10451,10098,6864,10473,10470,8419', ',')
SELECT *
FROM (SELECT Row_number()
OVER (
ORDER BY ListingProductID DESC ) AS [PageIndex],
*
FROM (SELECT P.ListingProductID,
P.CustomerID,
P.OrderSourceID,
P.SiteCode,
P.ListingBasicID,
P.SKU,
P.OrderSourceSKU,
P.BuyItNowPrice,
P.EnableBestOffer,
B.Duration,
B.EnableTimerPosting,
B.TimerPosting,
P.MinBestOfferPrice,
P.AutoAcceptPrice,
P.Quantity,
P.EAN,
P.UPC,
P.EPID,
P.ParentListingProductID,
P.IsVariation,
P.ProductType,
P.IsPriceStrategy,
P.PriceStrategyID,
P.PriceStrategyName,
B.TagName,
B.ItemID,
B.ListingType,
B.ProductTitle,
B.ProductSubTitle,
B.AuctionStartPrice,
B.AuctionReservePrice,
B.AuctionBuyItNowPrice,
B.Brand,
B.BrandMPN,
B.ClassID1,
B.ClassID2,
B.StoreClass1,
B.StoreClass2,
B.StoreURL,
B.StartTime,
B.FbaOrderSourceSKU,
B.EndTime,
Isnull(B.UpdateStatus, 0) UpdateStatus,
B.ViewItemURL,
B.AddTime,
B.AdminID,
B.AdminName,
B.UpdateTime,
B.UpdateAdminID,
B.UpdateAdminName,
B.AddItemAdminID,
B.AddItemAdminName,
B.SellerAdminID,
B.SellerAdminName,
B.AddItemFee,
B.OnlineState,
B.Visits,
B.RowVersion,
B.SyncTime,
B.IsSync,
Isnull(B.QuantitySold, 0) QuantitySold,
OS.OrderSourceName,
'' ClientSKU,
'' ProductNameCN,
NULL ProductState,
Isnull(ASP.EnableAutoSupplement, 0) EnableAutoSupplement,
Isnull(ASP.IsInventoryCheck, 0) IsInventoryCheck,
ASP.ListingQty,
ASP.SupplementaryQty,
B.Warehouse,
ELS.SiteCountry,
country.CountryDescription,
Isnull(LSA.SalesToday, 0) AS SalesToday,
Isnull(LSA.SalesYesterDay, 0) AS SalesYesterDay,
Isnull(LSA.SalesBeforeDay, 0) AS SalesBeforeDay,
Isnull(LSA.SalesMoth, 0) AS SalesMoth,
Isnull(LSA.SalesDayTrend, 0) AS SalesDayTrend,
Isnull(LSA.ViewsToday, 0) AS ViewsToday,
Isnull(LSA.ViewsYesterDay, 0) AS ViewsYesterDay,
Isnull(LSA.ViewsBeforeDay, 0) AS ViewsBeforeDay,
Isnull(LSA.ViewsMoth, 0) AS ViewsMoth,
Isnull(LSA.ViewsDayTrend, 0) AS ViewsDayTrend,
CASE
WHEN Isnull(LSA.ViewsToday, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesToday, 0)) / CONVERT(FLOAT, LSA.ViewsToday), 4) * 100
END AS RoiToday,
CASE
WHEN Isnull(LSA.ViewsYesterDay, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesYesterDay, 0)) / CONVERT(FLOAT, LSA.ViewsYesterDay), 4) * 100
END AS RoiYesterDay,
CASE
WHEN Isnull(LSA.ViewsBeforeDay, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesBeforeDay, 0)) / CONVERT(FLOAT, LSA.ViewsBeforeDay), 4) * 100
END AS RoiBeforeDay,
CASE
WHEN Isnull(LSA.ViewsMoth, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesMoth, 0)) / CONVERT(FLOAT, LSA.ViewsMoth), 4) * 100
END AS RoiMoth,
0 SkuCount,
B.BidCount,
B.BidCurrentPrice,
P.OriginalRetailPrice,
B.MarkdownPromotionID,
B.MarkdownPromotionName,
B.MarkdownPromotionDisCountName,
B.MarkdownPromotionStartDate,
B.MarkdownPromotionEndDate,
B.MarkdownPromotionDisCountID,
MK.PromotionStatus AS MKPromotionStutas
FROM RB_Ebay_Listing_Basic_V2 AS B WITH (NOLOCK)
INNER JOIN #tbOrderSourceID OrderSourceIDs
ON B.OrderSourceID = OrderSourceIDs.Id
LEFT JOIN RB_Ebay_Listing_Product_V2 AS P WITH (NOLOCK)
ON B.CustomerID = P.CustomerID
AND B.ListingBasicID = P.ListingBasicID
AND B.OrderSourceID = P.OrderSourceID
AND B.SiteCode = P.SiteCode
LEFT JOIN RB_Ebay_Listing_Sale_Attach_V2 LSA WITH (NOLOCK)
ON LSA.CustomerID = P.CustomerID
AND LSA.ListingBasicID = p.ListingBasicID
AND LSA.OrderSourceID = p.OrderSourceID
LEFT OUTER JOIN RB_OrderSource AS OS WITH (NOLOCK)
ON B.CustomerID = OS.CustomerID
AND B.OrderSourceID = OS.ID
LEFT JOIN RB_Ebay_Listing_AutoSupplement_V2 ASP WITH (NOLOCK)
ON ASP.CustomerID = P.CustomerID
AND ASP.ListingBasicID = P.ListingBasicID
AND ASP.ListingProductID = P.ListingProductID
AND ASP.OrderSourceID = P.OrderSourceID
AND ASP.SiteCode = P.SiteCode
LEFT JOIN RB_Ebay_Listing_Shipping_V2 ELS WITH(NOLOCK)
ON B.ListingBasicID = ELS.ListingBasicID
AND B.CustomerID = ELS.CustomerID
LEFT JOIN E_Ebay_Country_V2 country (NOLOCK)
ON country.Country = ELS.SiteCountry
LEFT JOIN RB_Ebay_Listing_Markdown_Promotion MK WITH(NOLOCK)
ON MK.CustomerID = P.CustomerID
AND MK.ID = B.MarkdownPromotionID
WHERE P.CustomerID = @CustomerID
AND P.IsVariation = 0
AND B.OnlineState = 0
AND ( Datediff(day, B.AddTime, '2023-04-01 00:00:00') <= 0 )) AS TempTable) AS PagerTable
WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500
OPTION(maxdop 8) ;
优化前2分钟都出不来，新建（idx_AddTime_OrderSourceID_ListingBasicID）这个索引，并强制指定，AddTime条件改变，优化后1秒出来，请参考。
DECLARE @CustomerID INT=1
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id = Object_id(N'tempdb..#tbOrderSourceID')
AND type = 'U')
DROP TABLE #tbOrderSourceID
CREATE TABLE #tbOrderSourceID
(
Id INT PRIMARY KEY
)
INSERT INTO #tbOrderSourceID
SELECT item AS Id
FROM Charsplitforin('13238,10481,10103,10372,11237,11238,10792,10489,10102,8960,7962,6759,6240,16641,13245,16107,16101,15936,15889,15791,14893,14652,10482,10478,10101,8592,8134,7783,17225,17814,17813,17816,10500,6635,7172,10235,9500,12626,18054,18063,10232,12824,9636,13235,13354,13062,7100,18224,18223,10791,10227,13645,9502,9674,10828,9656,10451,10098,6864,10473,10470,8419', ',')
SELECT *
FROM (SELECT Row_number()
OVER (
ORDER BY ListingProductID DESC ) AS [PageIndex],
*
FROM (SELECT P.ListingProductID,
P.CustomerID,
P.OrderSourceID,
P.SiteCode,
P.ListingBasicID,
P.SKU,
P.OrderSourceSKU,
P.BuyItNowPrice,
P.EnableBestOffer,
B.Duration,
B.EnableTimerPosting,
B.TimerPosting,
P.MinBestOfferPrice,
P.AutoAcceptPrice,
P.Quantity,
P.EAN,
P.UPC,
P.EPID,
P.ParentListingProductID,
P.IsVariation,
P.ProductType,
P.IsPriceStrategy,
P.PriceStrategyID,
P.PriceStrategyName,
B.TagName,
B.ItemID,
B.ListingType,
B.ProductTitle,
B.ProductSubTitle,
B.AuctionStartPrice,
B.AuctionReservePrice,
B.AuctionBuyItNowPrice,
B.Brand,
B.BrandMPN,
B.ClassID1,
B.ClassID2,
B.StoreClass1,
B.StoreClass2,
B.StoreURL,
B.StartTime,
B.FbaOrderSourceSKU,
B.EndTime,
Isnull(B.UpdateStatus, 0) UpdateStatus,
B.ViewItemURL,
B.AddTime,
B.AdminID,
B.AdminName,
B.UpdateTime,
B.UpdateAdminID,
B.UpdateAdminName,
B.AddItemAdminID,
B.AddItemAdminName,
B.SellerAdminID,
B.SellerAdminName,
B.AddItemFee,
B.OnlineState,
B.Visits,
B.RowVersion,
B.SyncTime,
B.IsSync,
Isnull(B.QuantitySold, 0) QuantitySold,
OS.OrderSourceName,
'' ClientSKU,
'' ProductNameCN,
NULL ProductState,
Isnull(ASP.EnableAutoSupplement, 0) EnableAutoSupplement,
Isnull(ASP.IsInventoryCheck, 0) IsInventoryCheck,
ASP.ListingQty,
ASP.SupplementaryQty,
B.Warehouse,
ELS.SiteCountry,
country.CountryDescription,
Isnull(LSA.SalesToday, 0) AS SalesToday,
Isnull(LSA.SalesYesterDay, 0) AS SalesYesterDay,
Isnull(LSA.SalesBeforeDay, 0) AS SalesBeforeDay,
Isnull(LSA.SalesMoth, 0) AS SalesMoth,
Isnull(LSA.SalesDayTrend, 0) AS SalesDayTrend,
Isnull(LSA.ViewsToday, 0) AS ViewsToday,
Isnull(LSA.ViewsYesterDay, 0) AS ViewsYesterDay,
Isnull(LSA.ViewsBeforeDay, 0) AS ViewsBeforeDay,
Isnull(LSA.ViewsMoth, 0) AS ViewsMoth,
Isnull(LSA.ViewsDayTrend, 0) AS ViewsDayTrend,
CASE
WHEN Isnull(LSA.ViewsToday, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesToday, 0)) / CONVERT(FLOAT, LSA.ViewsToday), 4) * 100
END AS RoiToday,
CASE
WHEN Isnull(LSA.ViewsYesterDay, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesYesterDay, 0)) / CONVERT(FLOAT, LSA.ViewsYesterDay), 4) * 100
END AS RoiYesterDay,
CASE
WHEN Isnull(LSA.ViewsBeforeDay, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesBeforeDay, 0)) / CONVERT(FLOAT, LSA.ViewsBeforeDay), 4) * 100
END AS RoiBeforeDay,
CASE
WHEN Isnull(LSA.ViewsMoth, 0) = 0 THEN 0
ELSE Round(CONVERT(FLOAT, Isnull(LSA.SalesMoth, 0)) / CONVERT(FLOAT, LSA.ViewsMoth), 4) * 100
END AS RoiMoth,
0 SkuCount,
B.BidCount,
B.BidCurrentPrice,
P.OriginalRetailPrice,
B.MarkdownPromotionID,
B.MarkdownPromotionName,
B.MarkdownPromotionDisCountName,
B.MarkdownPromotionStartDate,
B.MarkdownPromotionEndDate,
B.MarkdownPromotionDisCountID,
MK.PromotionStatus AS MKPromotionStutas
FROM RB_Ebay_Listing_Basic_V2 AS B WITH (NOLOCK,index(idx_AddTime_OrderSourceID_ListingBasicID))
INNER JOIN #tbOrderSourceID OrderSourceIDs
ON B.OrderSourceID = OrderSourceIDs.Id
LEFT JOIN RB_Ebay_Listing_Product_V2 AS P WITH (NOLOCK)
ON B.CustomerID = P.CustomerID
AND B.ListingBasicID = P.ListingBasicID
AND B.OrderSourceID = P.OrderSourceID
AND B.SiteCode = P.SiteCode
LEFT JOIN RB_Ebay_Listing_Sale_Attach_V2 LSA WITH (NOLOCK)
ON LSA.CustomerID = P.CustomerID
AND LSA.ListingBasicID = p.ListingBasicID
AND LSA.OrderSourceID = p.OrderSourceID
LEFT OUTER JOIN RB_OrderSource AS OS WITH (NOLOCK)
ON B.CustomerID = OS.CustomerID
AND B.OrderSourceID = OS.ID
LEFT JOIN RB_Ebay_Listing_AutoSupplement_V2 ASP WITH (NOLOCK)
ON ASP.CustomerID = P.CustomerID
AND ASP.ListingBasicID = P.ListingBasicID
AND ASP.ListingProductID = P.ListingProductID
AND ASP.OrderSourceID = P.OrderSourceID
AND ASP.SiteCode = P.SiteCode
LEFT JOIN RB_Ebay_Listing_Shipping_V2 ELS WITH(NOLOCK)
ON B.ListingBasicID = ELS.ListingBasicID
AND B.CustomerID = ELS.CustomerID
LEFT JOIN E_Ebay_Country_V2 country (NOLOCK)
ON country.Country = ELS.SiteCountry
LEFT JOIN RB_Ebay_Listing_Markdown_Promotion MK WITH(NOLOCK)
ON MK.CustomerID = b.CustomerID
AND MK.ID = B.MarkdownPromotionID
WHERE b.CustomerID = @CustomerID
AND P.IsVariation = 0
AND B.OnlineState = 3
--AND ( Datediff(day, B.AddTime, '2023-04-01 00:00:00') <= 0 )
and b.AddTime>'2023-04-01'
) AS TempTable) AS PagerTable
WHERE PagerTable.[PageIndex] BETWEEN 1 AND 500
OPTION(maxdop 8)
1.已经按系统部提供的方案，强制新增索引：新建（idx_AddTime_OrderSourceID_ListingBasicID）这个索引，并强制指定
2.更改代码：
3.测试结果，可正常搜索，根据条件也可正常搜索