003- DECLARE @TmpSKUFromES TABLE (SKU VARCHAR(50));INSERT INTO @TmpSKUFromES (SKU) VALUES (' '); CREATE TABLE #ProductSKUS(SKU VARCHAR(50)); INSERT INTO #ProductSKUS SELECT SKU FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ID DESC ) AS [PageIndex], * FROM ( SELECT P.SKU,P.ID FROM dbo.Rb_product p with(nolock,index(IX_RB_Product_CustomerID_DeleteStatus_IsGroup)) LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum > 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError > 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0 ) AS TempTable ) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 100; SELECT ROW_NUMBER() OVER (ORDER BY ID DESC ) AS [PageIndex], * FROM (SELECT p.ID , p.CustomerID , p.ClientSKU , p.SKU , p.ProductState , p.MinSaleNum , p.ImageUrl , p.ProductProperty , p.WithBattery , p.PlugRelate , p.PlugRelateType , p.ProductGroupSKU , p.ProductName , p.ProductNameCN , p.LastSupplierPrice , p.GrossWeight , p.UnitShipFee , p.LastBuyShipFee , p.SalePrice , p.PackWeight , p.ProductMaterial, p.IsGroup , p.OnlineStatus , p.ComeSource AS ComeSource , p.HasLawFirmAgent, p.IsCompliantProduct, ISNULL(whp.GoodNum, 0) GoodNum , ISNULL(whp.AvgDailySales, 0) AvgDailySales , ISNULL(wh.WareHouseName,'') DefaultLocalWarehouseName, pa.RecommendValue , pa.AddAdminID , pa.AddAdminName , pa.AddTime , pa.DevelopmentAuditOperatorName , pa.ProductDevelopmentAuditTime , pa.EditAuditOperatorName , pa.ProductEditAuditTime , pa.PictureAuditOperatorName , pa.ProductPictureAuditTime , pa.ProductPicAndTextAuditUserName , pa.ProductPicAndTextAuditTime , pa.IsAmazonSale , pa.NoSalePlatform , pa.NoSaleCountry , pa.DevelopStatus , pa.DevelopAdminID , pa.DevelopAdminName , pa.DevelopTime , pa.DevelopRefuseReason , pa.DevelopRefuseTime , pa.AssignDevelopAdminID , pa.AssignDevelopAdminName , pa.AssignDevelopTime , pa.EditStatus , pa.EditAdminID , pa.EditAdminName , pa.EditTime , pa.EditRefuseTime , pa.ToImageMemo , pa.ImageStatus , pa.ImageAdminID , pa.ImageAdminName , pa.ImageTime , pa.ImageRefuseTime , pa.BuyerID , pa.BuyerName , pa.BuyerSetTime , pa.CheckStatus , pa.CheckReason , pa.CheckAdminID , pa.CheckAdminName , pa.CheckTime , pa.OnlineTime , pa.OfflineTime , pa.OfflineAdminID , pa.OfflineAdminName , p.TaxPoint , p.TaxRate , p.IsRawMaterial , ISNULL(ProductBadNum, 0) ProductBadNum , ISNULL(NumError, 0) NumError , p.ProductType , ISNULL(p.IsFBABarCode, 0) IsFBABarCode , ISNULL(p.IsRecommendOcean, 0) AS IsRecommendOcean , pd.DeclarationPriceRate , p.IsCheckDeclarationCode , p.IsCheckTransport , p.LastBuyPrice , pa.PriceSpecialistOperatTime , ISNULL(p.DefaultLocalWarehouse, 0) AS DefaultLocalWareHouse , p.DevelopType , p.AppointDevelopType , p.PictureSource , p.PreclassStatus , p.PreclassGoodsCode , p.ImitatingBrandRemark, p.ProductStateRemarks, p.ClassID1, p.ClassID2, p.ClassID, p.BeginTime, p.EndTime, p.ProdutcArrivalDate, p.AutoChooseKeyWords, p.AccurateKeyword, ISNULL(pa.PhotographerId,0) PhotographerId, ISNULL(pa.PhotographerName,'') PhotographerName, pa.PhotographerTime, pa.WrapDesignerId, pa.WrapDesignerName, pa.WrapDesignerTime, pa.ImageDesignerId, pa.ImageDesignerName, pa.ImageDesignerTime, pa.IntellectualPropertyRightsAdminID, pa.IntellectualPropertyRightsAdminName, pa.IntellectualPropertyRightsTime, pa.RiskControlEnquiryAdminId, pa.RiskControlEnquiryAdminName, pa.RiskControlEnquiryTime, ISNULL(p.EditNum,0) EditNum, p.IsNeedCeCertification, p.TypeOfTorts, pa.RiskLevelType, pa.RiskLevelRemark, isnull(pa.MainCategoryCheckStatus,0) PreCheckStatus, p.IsAdultsOnly FROM (SELECT * FROM dbo.RB_Product (NOLOCK) WHERE SKU IN (SELECT * FROM #ProductSKUS)) p LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum > 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError > 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0) AS TempTable; SELECT COUNT(1) FROM (SELECT P.SKU,P.ID FROM dbo.Rb_product p with(nolock,index(IX_RB_Product_CustomerID_DeleteStatus_IsGroup)) LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum > 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError > 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0) AS TempCount; DROP TABLE IF Exists #ProductSKUS; [结果]
003+ DECLARE @TmpSKUFromES TABLE (SKU VARCHAR(50));INSERT INTO @TmpSKUFromES (SKU) VALUES (' '); CREATE TABLE #ProductSKUS(SKU VARCHAR(50)); INSERT INTO #ProductSKUS SELECT SKU FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ID DESC ) AS [PageIndex], * FROM ( SELECT P.SKU,P.ID FROM dbo.Rb_product p with(nolock,index(IX_RB_Product_CustomerID_DeleteStatus_IsGroup)) LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum > 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError > 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0 ) AS TempTable ) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 100; SELECT ROW_NUMBER() OVER (ORDER BY ID DESC ) AS [PageIndex], * FROM (SELECT p.ID , p.CustomerID , p.ClientSKU , p.SKU , p.ProductState , p.MinSaleNum , p.ImageUrl , p.ProductProperty , p.WithBattery , p.PlugRelate , p.PlugRelateType , p.ProductGroupSKU , p.ProductName , p.ProductNameCN , p.LastSupplierPrice , p.GrossWeight , p.UnitShipFee , p.LastBuyShipFee , p.SalePrice , p.PackWeight , p.ProductMaterial, p.IsGroup , p.OnlineStatus , p.ComeSource AS ComeSource , p.HasLawFirmAgent, p.IsCompliantProduct, ISNULL(whp.GoodNum, 0) GoodNum , ISNULL(whp.AvgDailySales, 0) AvgDailySales , ISNULL(wh.WareHouseName,'') DefaultLocalWarehouseName, pa.RecommendValue , pa.AddAdminID , pa.AddAdminName , pa.AddTime , pa.DevelopmentAuditOperatorName , pa.ProductDevelopmentAuditTime , pa.EditAuditOperatorName , pa.ProductEditAuditTime , pa.PictureAuditOperatorName , pa.ProductPictureAuditTime , pa.ProductPicAndTextAuditUserName , pa.ProductPicAndTextAuditTime , pa.IsAmazonSale , pa.NoSalePlatform , pa.NoSaleCountry , pa.DevelopStatus , pa.DevelopAdminID , pa.DevelopAdminName , pa.DevelopTime , pa.DevelopRefuseReason , pa.DevelopRefuseTime , pa.AssignDevelopAdminID , pa.AssignDevelopAdminName , pa.AssignDevelopTime , pa.EditStatus , pa.EditAdminID , pa.EditAdminName , pa.EditTime , pa.EditRefuseTime , pa.ToImageMemo , pa.ImageStatus , pa.ImageAdminID , pa.ImageAdminName , pa.ImageTime , pa.ImageRefuseTime , pa.BuyerID , pa.BuyerName , pa.BuyerSetTime , pa.CheckStatus , pa.CheckReason , pa.CheckAdminID , pa.CheckAdminName , pa.CheckTime , pa.OnlineTime , pa.OfflineTime , pa.OfflineAdminID , pa.OfflineAdminName , p.TaxPoint , p.TaxRate , p.IsRawMaterial , ISNULL(ProductBadNum, 0) ProductBadNum , ISNULL(NumError, 0) NumError , p.ProductType , ISNULL(p.IsFBABarCode, 0) IsFBABarCode , ISNULL(p.IsRecommendOcean, 0) AS IsRecommendOcean , pd.DeclarationPriceRate , p.IsCheckDeclarationCode , p.IsCheckTransport , p.LastBuyPrice , pa.PriceSpecialistOperatTime , ISNULL(p.DefaultLocalWarehouse, 0) AS DefaultLocalWareHouse , p.DevelopType , p.AppointDevelopType , p.PictureSource , p.PreclassStatus , p.PreclassGoodsCode , p.ImitatingBrandRemark, p.ProductStateRemarks, p.ClassID1, p.ClassID2, p.ClassID, p.BeginTime, p.EndTime, p.ProdutcArrivalDate, p.AutoChooseKeyWords, p.AccurateKeyword, ISNULL(pa.PhotographerId,0) PhotographerId, ISNULL(pa.PhotographerName,'') PhotographerName, pa.PhotographerTime, pa.WrapDesignerId, pa.WrapDesignerName, pa.WrapDesignerTime, pa.ImageDesignerId, pa.ImageDesignerName, pa.ImageDesignerTime, pa.IntellectualPropertyRightsAdminID, pa.IntellectualPropertyRightsAdminName, pa.IntellectualPropertyRightsTime, pa.RiskControlEnquiryAdminId, pa.RiskControlEnquiryAdminName, pa.RiskControlEnquiryTime, ISNULL(p.EditNum,0) EditNum, p.IsNeedCeCertification, p.TypeOfTorts, pa.RiskLevelType, pa.RiskLevelRemark, isnull(pa.MainCategoryCheckStatus,0) PreCheckStatus, p.IsAdultsOnly FROM (SELECT * FROM dbo.RB_Product (NOLOCK) WHERE SKU IN (SELECT * FROM #ProductSKUS)) p LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum > 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError > 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0) AS TempTable; SELECT COUNT(1) FROM (SELECT P.SKU,P.ID FROM dbo.Rb_product p with(nolock,index(IX_RB_Product_CustomerID_DeleteStatus_IsGroup)) LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum > 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError > 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0) AS TempCount; DROP TABLE IF Exists #ProductSKUS;
004- [期望] 去掉索引，性能更好，建议删除指定索引
004+
005-
005+ [结果]
006+ [期望] 去掉索引，性能更好，建议删除指定索引
007+

003- DECLARE @TmpSKUFromES TABLE (SKU VARCHAR(50));INSERT INTO @TmpSKUFromES (SKU) VALUES (' '); CREATE TABLE #ProductSKUS(SKU VARCHAR(50)); INSERT INTO #ProductSKUS SELECT SKU FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ID DESC ) AS [PageIndex], * FROM ( SELECT P.SKU,P.ID FROM dbo.Rb_product p with(nolock,index(IX_RB_Product_CustomerID_DeleteStatus_IsGroup)) LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum &gt; 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError &gt; 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0 ) AS TempTable ) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 100; SELECT ROW_NUMBER() OVER (ORDER BY ID DESC ) AS [PageIndex], * FROM (SELECT p.ID , p.CustomerID , p.ClientSKU , p.SKU , p.ProductState , p.MinSaleNum , p.ImageUrl , p.ProductProperty , p.WithBattery , p.PlugRelate , p.PlugRelateType , p.ProductGroupSKU , p.ProductName , p.ProductNameCN , p.LastSupplierPrice , p.GrossWeight , p.UnitShipFee , p.LastBuyShipFee , p.SalePrice , p.PackWeight , p.ProductMaterial, p.IsGroup , p.OnlineStatus , p.ComeSource AS ComeSource , p.HasLawFirmAgent, p.IsCompliantProduct, ISNULL(whp.GoodNum, 0) GoodNum , ISNULL(whp.AvgDailySales, 0) AvgDailySales , ISNULL(wh.WareHouseName,'') DefaultLocalWarehouseName, pa.RecommendValue , pa.AddAdminID , pa.AddAdminName , pa.AddTime , pa.DevelopmentAuditOperatorName , pa.ProductDevelopmentAuditTime , pa.EditAuditOperatorName , pa.ProductEditAuditTime , pa.PictureAuditOperatorName , pa.ProductPictureAuditTime , pa.ProductPicAndTextAuditUserName , pa.ProductPicAndTextAuditTime , pa.IsAmazonSale , pa.NoSalePlatform , pa.NoSaleCountry , pa.DevelopStatus , pa.DevelopAdminID , pa.DevelopAdminName , pa.DevelopTime , pa.DevelopRefuseReason , pa.DevelopRefuseTime , pa.AssignDevelopAdminID , pa.AssignDevelopAdminName , pa.AssignDevelopTime , pa.EditStatus , pa.EditAdminID , pa.EditAdminName , pa.EditTime , pa.EditRefuseTime , pa.ToImageMemo , pa.ImageStatus , pa.ImageAdminID , pa.ImageAdminName , pa.ImageTime , pa.ImageRefuseTime , pa.BuyerID , pa.BuyerName , pa.BuyerSetTime , pa.CheckStatus , pa.CheckReason , pa.CheckAdminID , pa.CheckAdminName , pa.CheckTime , pa.OnlineTime , pa.OfflineTime , pa.OfflineAdminID , pa.OfflineAdminName , p.TaxPoint , p.TaxRate , p.IsRawMaterial , ISNULL(ProductBadNum, 0) ProductBadNum , ISNULL(NumError, 0) NumError , p.ProductType , ISNULL(p.IsFBABarCode, 0) IsFBABarCode , ISNULL(p.IsRecommendOcean, 0) AS IsRecommendOcean , pd.DeclarationPriceRate , p.IsCheckDeclarationCode , p.IsCheckTransport , p.LastBuyPrice , pa.PriceSpecialistOperatTime , ISNULL(p.DefaultLocalWarehouse, 0) AS DefaultLocalWareHouse , p.DevelopType , p.AppointDevelopType , p.PictureSource , p.PreclassStatus , p.PreclassGoodsCode , p.ImitatingBrandRemark, p.ProductStateRemarks, p.ClassID1, p.ClassID2, p.ClassID, p.BeginTime, p.EndTime, p.ProdutcArrivalDate, p.AutoChooseKeyWords, p.AccurateKeyword, ISNULL(pa.PhotographerId,0) PhotographerId, ISNULL(pa.PhotographerName,'') PhotographerName, pa.PhotographerTime, pa.WrapDesignerId, pa.WrapDesignerName, pa.WrapDesignerTime, pa.ImageDesignerId, pa.ImageDesignerName, pa.ImageDesignerTime, pa.IntellectualPropertyRightsAdminID, pa.IntellectualPropertyRightsAdminName, pa.IntellectualPropertyRightsTime, pa.RiskControlEnquiryAdminId, pa.RiskControlEnquiryAdminName, pa.RiskControlEnquiryTime, ISNULL(p.EditNum,0) EditNum, p.IsNeedCeCertification, p.TypeOfTorts, pa.RiskLevelType, pa.RiskLevelRemark, isnull(pa.MainCategoryCheckStatus,0) PreCheckStatus, p.IsAdultsOnly FROM (SELECT * FROM dbo.RB_Product (NOLOCK) WHERE SKU IN (SELECT * FROM #ProductSKUS)) p LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum &gt; 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError &gt; 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0) AS TempTable; SELECT COUNT(1) FROM (SELECT P.SKU,P.ID FROM dbo.Rb_product p with(nolock,index(IX_RB_Product_CustomerID_DeleteStatus_IsGroup)) LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum &gt; 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError &gt; 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0) AS TempCount; DROP TABLE IF Exists #ProductSKUS; <br /><br /><p>[结果]</p>
003+ DECLARE @TmpSKUFromES TABLE (SKU VARCHAR(50));INSERT INTO @TmpSKUFromES (SKU) VALUES (' '); CREATE TABLE #ProductSKUS(SKU VARCHAR(50)); INSERT INTO #ProductSKUS SELECT SKU FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ID DESC ) AS [PageIndex], * FROM ( SELECT P.SKU,P.ID FROM dbo.Rb_product p with(nolock,index(IX_RB_Product_CustomerID_DeleteStatus_IsGroup)) LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum &gt; 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError &gt; 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0 ) AS TempTable ) AS PagerTable WHERE PagerTable.[PageIndex] BETWEEN 1 AND 100; SELECT ROW_NUMBER() OVER (ORDER BY ID DESC ) AS [PageIndex], * FROM (SELECT p.ID , p.CustomerID , p.ClientSKU , p.SKU , p.ProductState , p.MinSaleNum , p.ImageUrl , p.ProductProperty , p.WithBattery , p.PlugRelate , p.PlugRelateType , p.ProductGroupSKU , p.ProductName , p.ProductNameCN , p.LastSupplierPrice , p.GrossWeight , p.UnitShipFee , p.LastBuyShipFee , p.SalePrice , p.PackWeight , p.ProductMaterial, p.IsGroup , p.OnlineStatus , p.ComeSource AS ComeSource , p.HasLawFirmAgent, p.IsCompliantProduct, ISNULL(whp.GoodNum, 0) GoodNum , ISNULL(whp.AvgDailySales, 0) AvgDailySales , ISNULL(wh.WareHouseName,'') DefaultLocalWarehouseName, pa.RecommendValue , pa.AddAdminID , pa.AddAdminName , pa.AddTime , pa.DevelopmentAuditOperatorName , pa.ProductDevelopmentAuditTime , pa.EditAuditOperatorName , pa.ProductEditAuditTime , pa.PictureAuditOperatorName , pa.ProductPictureAuditTime , pa.ProductPicAndTextAuditUserName , pa.ProductPicAndTextAuditTime , pa.IsAmazonSale , pa.NoSalePlatform , pa.NoSaleCountry , pa.DevelopStatus , pa.DevelopAdminID , pa.DevelopAdminName , pa.DevelopTime , pa.DevelopRefuseReason , pa.DevelopRefuseTime , pa.AssignDevelopAdminID , pa.AssignDevelopAdminName , pa.AssignDevelopTime , pa.EditStatus , pa.EditAdminID , pa.EditAdminName , pa.EditTime , pa.EditRefuseTime , pa.ToImageMemo , pa.ImageStatus , pa.ImageAdminID , pa.ImageAdminName , pa.ImageTime , pa.ImageRefuseTime , pa.BuyerID , pa.BuyerName , pa.BuyerSetTime , pa.CheckStatus , pa.CheckReason , pa.CheckAdminID , pa.CheckAdminName , pa.CheckTime , pa.OnlineTime , pa.OfflineTime , pa.OfflineAdminID , pa.OfflineAdminName , p.TaxPoint , p.TaxRate , p.IsRawMaterial , ISNULL(ProductBadNum, 0) ProductBadNum , ISNULL(NumError, 0) NumError , p.ProductType , ISNULL(p.IsFBABarCode, 0) IsFBABarCode , ISNULL(p.IsRecommendOcean, 0) AS IsRecommendOcean , pd.DeclarationPriceRate , p.IsCheckDeclarationCode , p.IsCheckTransport , p.LastBuyPrice , pa.PriceSpecialistOperatTime , ISNULL(p.DefaultLocalWarehouse, 0) AS DefaultLocalWareHouse , p.DevelopType , p.AppointDevelopType , p.PictureSource , p.PreclassStatus , p.PreclassGoodsCode , p.ImitatingBrandRemark, p.ProductStateRemarks, p.ClassID1, p.ClassID2, p.ClassID, p.BeginTime, p.EndTime, p.ProdutcArrivalDate, p.AutoChooseKeyWords, p.AccurateKeyword, ISNULL(pa.PhotographerId,0) PhotographerId, ISNULL(pa.PhotographerName,'') PhotographerName, pa.PhotographerTime, pa.WrapDesignerId, pa.WrapDesignerName, pa.WrapDesignerTime, pa.ImageDesignerId, pa.ImageDesignerName, pa.ImageDesignerTime, pa.IntellectualPropertyRightsAdminID, pa.IntellectualPropertyRightsAdminName, pa.IntellectualPropertyRightsTime, pa.RiskControlEnquiryAdminId, pa.RiskControlEnquiryAdminName, pa.RiskControlEnquiryTime, ISNULL(p.EditNum,0) EditNum, p.IsNeedCeCertification, p.TypeOfTorts, pa.RiskLevelType, pa.RiskLevelRemark, isnull(pa.MainCategoryCheckStatus,0) PreCheckStatus, p.IsAdultsOnly FROM (SELECT * FROM dbo.RB_Product (NOLOCK) WHERE SKU IN (SELECT * FROM #ProductSKUS)) p LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum &gt; 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError &gt; 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0) AS TempTable; SELECT COUNT(1) FROM (SELECT P.SKU,P.ID FROM dbo.Rb_product p with(nolock,index(IX_RB_Product_CustomerID_DeleteStatus_IsGroup)) LEFT JOIN RB_Product_Admin pa WITH ( NOLOCK ) ON pa.SKU = p.SKU AND pa.CustomerID = p.CustomerID LEFT JOIN RB_WareHouse_Product whp WITH ( NOLOCK ) ON whp.SKU = p.SKU AND whp.CustomerID = p.CustomerID AND whp.WareHouseID = (CASE p.DefaultLocalWarehouse WHEN 0 THEN 5312 WHEN NULL THEN 5312 ELSE p.DefaultLocalWarehouse END) AND EXISTS (SELECT 1 FROM RB_WareHouse WHERE whp.WareHouseID =RB_WareHouse.ID AND whp.CustomerID = RB_WareHouse.CustomerID AND RB_WareHouse.WareHouseType = 1) LEFT JOIN RB_WareHouse wh (NOLOCK) ON p.CustomerID=wh.CustomerID AND p.DefaultLocalWarehouse=wh.ID AND wh.IsActive=1 LEFT JOIN ( SELECT CustomerID , SKU , SUM(BadNum) AS ProductBadNum FROM RB_Procurement_Detail_BadList WITH ( NOLOCK ) WHERE BadNum &gt; 0 AND DType = 1 GROUP BY CustomerID , SKU ) AS RB_Procurement_Detail_BadList ON p.SKU = RB_Procurement_Detail_BadList.SKU AND p.CustomerID = RB_Procurement_Detail_BadList.CustomerID LEFT JOIN ( SELECT CustomerID , SKU , SUM(NumError) AS NumError FROM RB_ProductManageLog WITH ( NOLOCK ) WHERE NumError &gt; 0 GROUP BY CustomerID , SKU ) AS RB_ProductManageLog ON p.SKU = RB_ProductManageLog.SKU AND p.CustomerID = RB_ProductManageLog.CustomerID LEFT JOIN RB_Product_Declaration pd ( NOLOCK ) ON p.CustomerID = pd.CustomerID AND p.SKU = pd.SKU WHERE p.CustomerID =1 AND p.DeleteStatus =0 AND p.ProductNameCN like @ProductNameCN AND pa.EditStatus IN (1,3) AND p.IsGroup=0) AS TempCount; DROP TABLE IF Exists #ProductSKUS; <br /><p><br /></p>
004- <br /><p>[期望] 去掉索引，性能更好，建议删除指定索引</p>
004+ <p><img src="{359696.png}" alt="" /></p>
005- <br />
005+ <p>[结果]</p>
006+ <br /><p>[期望] 去掉索引，性能更好，建议删除指定索引</p>
007+ <br />