深圳智汇创想科技有限公司

截图看下哪些组合条件一起搜索超时

158-2.zhcxkj.com/IrobotBox/LogFile/LogFileEdit.aspx?1=1&keywords=N1653141&fileName=DBError_2023-07-05.txt

客户订单页面，选择平台类目或者多加几个条件去搜索，一直小黄鸡

158-2.zhcxkj.com/IrobotBox/LogFile/LogFileEdit.aspx?1=1&keywords=N1653141&fileName=DBError_2023-07-05.txt

涉及千万数据 帮忙看看是否有优化空间

执行时间1分钟

执行语句

drop table if exists #tempTableSkus;SELECT SKU into #tempTableSkus FROM   E_Product_Amazon_BrowseTree(NOLOCK) pabt
        JOIN Charsplitforin('1055398,1063498', '-') Temp
        ON pabt.BrowsePathByID LIKE Temp.item + '%'
WHERE  pabt.IsDelete = 0 AND pabt.CustomerID = @CustomerID; create index idx_sku on #tempTableSkus(sku);  drop table if exists #tempTableCodes  select OrderCode into #tempTableCodes from RB_OrderList tol (nolock)  inner join #tempTableSkus t on tol.sku=t.SKU and tol.CustomerID=@CustomerID  create index idx_OrderCode on #tempTableCodes(OrderCode);  
               SELECT Count(1) FROM RB_OrderInfo oi WITH(nolock) LEFT JOIN  RB_OrderSource os WITH(nolock)
                ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID
            LEFT JOIN E_Country e WITH(nolock)
                ON oi.Country = e.Shorthand
            LEFT JOIN RB_Transport t WITH(nolock)
                ON oi.CustomerID = t.CustomerID AND oi.TransportID = t.ID 
            LEFT JOIN RB_Transport_API tapi WITH(nolock)       
                ON t.Transport_API_ID = tapi.ID AND t.CustomerID = tapi.CustomerID
            LEFT JOIN RB_WareHouse wh WITH(nolock)
                ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID
            LEFT JOIN RB_Admin rba WITH(nolock)
                ON rba.CustomerID = os.CustomerID AND rba.ID = os.SaleAdminID
            LEFT JOIN RB_BusinessTeam bt WITH(nolock)
                ON bt.CustomerID = os.CustomerID AND bt.ID = rba.BusinessTeamID
            LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)
                ON os.CustomerID = api.CustomerID AND os.ID = api.OrderSourceID
            LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand
                                        ON oi.CustomerID = cexpand.CustomerID
                                           AND oi.OrderCode = cexpand.OrderCode outer apply (SELECT Count(1) IsAttachMail
                        FROM   RB_OrderInfo_Attach att(nolock)
                        WHERE  att.CustomerID = oi.CustomerID
                               AND att.OrderCode = oi.OrderCode) att1   inner join #tempTableCodes temptcode on oi.OrderCode=temptcode.OrderCode  WHERE  oi.CustomerID = @CustomerID
                                        AND oi.IsStockOrder = 0 AND oi.IsPay = @IsPay AND oi.AddTime >= @StartTime AND oi.AddTime <= @EndTime   AND Exists (
                        SELECT 1 FROM RB_OrderList ol WITH(nolock) 
                            INNER JOIN RB_Product_SalesStatus ps WITH(nolock) ON ol.CustomerID = ps.CustomerID AND ol.SKU = ps.SKU
                                WHERE ol.CustomerID = @CustomerID
                                    AND ol.CustomerID = oi.CustomerID 
                                    AND ol.OrderCode = oi.OrderCode
                                    AND ps.ProductVitalityType IN (1) 
                                    AND ol.AddTime >= @OrderListStartTime AND ol.AddTime <= @EndTime ) AND Exists (
                        SELECT 1 FROM RB_OrderList ol WITH(nolock)  INNER JOIN RB_Product_Admin pa WITH(nolock) ON ol.CustomerID = pa.CustomerID AND ol.SKU = pa.SKU 
                        WHERE ol.CustomerID = @CustomerID 
                                    AND ol.OrderCode = oi.OrderCode
                                     AND pa.AddTime >= @ProductStartTime  AND ol.AddTime >= @OrderProductStartAddTime 
                        )  option(recompile,maxdop 16)
PARAMS:@CustomerID = 1 ,@IsPay = 1 ,@StartTime = 2023/5/5 0:00:00 ,@EndTime = 2023/7/5 23:59:59 ,@ProductStartTime = 2023/1/1 16:51:38 ,@OrderListStartTime = 2023/4/28 0:00:00 ,@OrderProductStartAddTime = 2023/5/4 23:00:00 ,

建议优化为：

declare @CustomerID int= 1 ,@IsPay int= 1 ,@StartTime datetime= '2023/5/5 0:00:00' ,@EndTime datetime= '2023/7/5 23:59:59' ,@ProductStartTime datetime= '2023/1/1 16:51:38' ,@OrderListStartTime datetime= '2023/4/28 0:00:00' ,@OrderProductStartAddTime datetime= '2023/5/4 23:00:00';

drop table if exists #tempTableSkus;
SELECT SKU into #tempTableSkus
FROM   E_Product_Amazon_BrowseTree(NOLOCK) pabt
        JOIN Charsplitforin('1055398,1063498', '-') Temp
        ON pabt.BrowsePathByID LIKE Temp.item + '%'
WHERE  pabt.IsDelete = 0 AND pabt.CustomerID = @CustomerID;
create index idx_sku on #tempTableSkus(sku);  
--drop table if exists #tempTableCodes  
--select OrderCode into #tempTableCodes from RB_OrderList tol with(nolock,index(IX_RB_OrderList_Sku_OrderCode))  inner join #tempTableSkus t on tol.sku=t.SKU and tol.CustomerID=1  
--create index idx_OrderCode on #tempTableCodes(OrderCode);  
               SELECT Count(1) FROM RB_OrderInfo oi WITH(nolock) LEFT JOIN  RB_OrderSource os WITH(nolock)
                ON oi.CustomerID = os.CustomerID AND oi.OrderSourceID = os.ID
            LEFT JOIN E_Country e WITH(nolock)
                ON oi.Country = e.Shorthand
            LEFT JOIN RB_Transport t WITH(nolock)
                ON oi.CustomerID = t.CustomerID AND oi.TransportID = t.ID
            LEFT JOIN RB_Transport_API tapi WITH(nolock)       
                ON t.Transport_API_ID = tapi.ID AND t.CustomerID = tapi.CustomerID
            LEFT JOIN RB_WareHouse wh WITH(nolock)
                ON oi.CustomerID = wh.CustomerID AND oi.WareHouseID = wh.ID
            LEFT JOIN RB_Admin rba WITH(nolock)
                ON rba.CustomerID = os.CustomerID AND rba.ID = os.SaleAdminID
            LEFT JOIN RB_BusinessTeam bt WITH(nolock)
                ON bt.CustomerID = os.CustomerID AND bt.ID = rba.BusinessTeamID
            LEFT JOIN RB_OrderSource_EbayAmazonAPI api WITH(nolock)
                ON os.CustomerID = api.CustomerID AND os.ID = api.OrderSourceID
            LEFT JOIN RB_OrderInfo_ClientExpand (NOLOCK) cexpand
                                        ON oi.CustomerID = cexpand.CustomerID
                                           AND oi.OrderCode = cexpand.OrderCode
            outer apply (SELECT Count(1) IsAttachMail
                        FROM   RB_OrderInfo_Attach att(nolock)
                        WHERE  att.CustomerID = oi.CustomerID
                               AND att.OrderCode = oi.OrderCode) att1   
            --inner join #tempTableCodes temptcode on oi.OrderCode=temptcode.OrderCode  
            inner join RB_OrderList ol (nolock) on ol.OrderCode=oi.OrderCode
            inner join #tempTableSkus t1 on ol.sku=t1.SKU and ol.CustomerID=1
             WHERE  oi.CustomerID = @CustomerID
                                        AND oi.IsStockOrder = 0 AND oi.IsPay = @IsPay AND oi.AddTime >= @StartTime AND oi.AddTime <= @EndTime   AND Exists (
                        SELECT 1 FROM RB_OrderList ol WITH(nolock)
                            INNER JOIN RB_Product_SalesStatus ps WITH(nolock) ON ol.CustomerID = ps.CustomerID AND ol.SKU = ps.SKU
                                WHERE ol.CustomerID = @CustomerID
                                    AND ol.CustomerID = oi.CustomerID
                                    AND ol.OrderCode = oi.OrderCode
                                    AND ps.ProductVitalityType IN (1)
                                    AND ol.AddTime >= @OrderListStartTime AND ol.AddTime <= @EndTime ) AND Exists (
                        SELECT 1 FROM RB_OrderList ol WITH(nolock)  INNER JOIN RB_Product_Admin pa WITH(nolock) ON ol.CustomerID = pa.CustomerID AND ol.SKU = pa.SKU
                        WHERE ol.CustomerID = @CustomerID
                                    AND ol.OrderCode = oi.OrderCode
                                     AND pa.AddTime >= @ProductStartTime  AND ol.AddTime >= @OrderProductStartAddTime
                        )  option(recompile,maxdop 16)

更改后搜索

svn 提交记录

线上观察，相关页面搜索正常