重现步骤
CODE:N10564158
Error:11_-2_0_-2146232060
SQL:  declare @tb_id as Tb_Ids;  INSERT INTO @tb_id  SELECT CONVERT(INT, item) FROM  dbo.CharSplitForIn('7085,7056,7057,7556,3922,7590,4316,9029,5312,2,279,336,333,687,2205,13237,13558,1100000', ',');  SELECT Count(1) FROM RB_WareHouse_Product a WITH ( NOLOCK ) INNER JOIN RB_WareHouse wh WITH ( NOLOCK ) ON a.CustomerID=wh.CustomerID AND a.WareHouseID=wh.ID AND wh.IsActive=1
                               INNER JOIN RB_WareHouse_OneProductMorePosition_Product whopmpp ( NOLOCK ) ON a.CustomerID=whopmpp.CustomerID AND a.WareHouseID=whopmpp.WareHouseID AND a.SKU=whopmpp.SKU
                               LEFT JOIN RB_WareHouse_Position AS b WITH ( NOLOCK ) ON whopmpp.PositionID=b.ID AND whopmpp.CustomerID=b.CustomerID
                               LEFT JOIN RB_Product AS c WITH ( NOLOCK ) ON a.SKU=c.SKU AND a.CustomerID=c.CustomerID
                               LEFT JOIN (
                               SELECT ps.CustomerID, SKU,ps.IsFBABarCode
                               FROM   RB_Product_SupplierPrice sp WITH( NOLOCK )
                               LEFT JOIN RB_Product_Supplier ps WITH( NOLOCK ) ON sp.SupplierID = ps.ID AND sp.CustomerID = ps.CustomerID
                               WHERE  sp.IsDefault = 1 AND ps.IsFBABarCode = 1) s ON a.SKU=s.SKU AND s.CustomerID=a.CustomerID
                               LEFT JOIN RB_Warehouse_Product_ActiveInfo r WITH(NOLOCK) on a.CustomerID=r.CustomerID AND a.SKU=r.SKU AND a.WarehouseID=r.WarehouseID
                               LEFT JOIN RB_7DaysOutStockRecord sr WITH(NOLOCK) on a.CustomerID=sr.CustomerID and a.SKU=sr.SKU and a.WareHouseID=sr.WareHouseID
                               LEFT JOIN RB_Warehouse_PickingPackage pp WITH(NOLOCK) on a.CustomerID=pp.CustomerID and a.SKU=pp.SKU and a.WareHouseID=pp.WareHouseID   INNER JOIN @tb_id tid on wh.ID=tid.Id   WHERE  a.CustomerID=1 AND wh.WareHouseType=1
PARAMS:
MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
TRACE:   在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   在 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   在 System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   在 System.Data.SqlClient.SqlDataReader.get_MetaData()
   在 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   在 System.Data.SqlClient.SqlCommand.ExecuteScalar()
   在 RB.DAL.DBOperator.GetSingle(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB)   在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms)
   在 RB.DAL.DBOperator.GetSingle(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB)
   在 RB.DAL.DBOperator.GetSingle(String SQLString, Boolean IsWriteDB)
   在 RB.DALExtend.Generate.RB_WareHouse_ProductExtend.GetWareHouseProductPositionInfoPagerList(RB_WareHouse_Product_Position_PageList_PSParameters parameters, Int32& recordCount, String& sqlQueryCriteria, Int32& totalGoodNum, Int32& totalLockNum, Decimal& totalInventoryAmount, String& sqlCreateTable)
   在 PandaBuy.ERP.Web.IrobotBox.WareHouse.LocalWarehouseInventory.InitProductsData()
   在 PandaBuy.ERP.Web.IrobotBox.WareHouse.LocalWarehouseInventory.Page_Load(Object sender, EventArgs e)
   在 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
   在 System.Web.UI.Control.OnLoad(EventArgs e)
   在 System.Web.UI.Control.LoadRecursive()
   在 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   在 System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
   在 System.Web.UI.Page.ProcessRequest()
   在 System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)
   在 System.Web.UI.Page.ProcessRequest(HttpContext context)
   在 ASP.irobotbox_warehouse_localwarehouseinventory_aspx.ProcessRequest(HttpContext context)
   在 System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
   在 System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step)
   在 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
   在 System.Web.HttpApplication.PipelineStepManager.ResumeSteps(Exception error)
   在 System.Web.HttpApplication.BeginProcessRequestNotification(HttpContext context, AsyncCallback cb)
   在 System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context)
   在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
   在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)
   在 System.Web.Hosting.UnsafeIISMethods.MgdIndicateCompletion(IntPtr pHandler, RequestNotificationStatus& notificationStatus)
   在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotificationHelper(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)

   在 System.Web.Hosting.PipelineRuntime.ProcessRequestNotification(IntPtr rootedObjectsPointer, IntPtr nativeRequestContext, IntPtr moduleData, Int32 flags)


建议优化：


declare @tb_id as Tb_Ids;  
INSERT INTO @tb_id  SELECT CONVERT(INT, item) FROM  dbo.CharSplitForIn('7085,7056,7057,7556,3922,7590,4316,9029,5312,2,279,336,333,687,2205,13237,13558,1100000', ',');  

drop table if exists #tabHouse;
select wh.CustomerID,wh.ID into #tabHouse from RB_WareHouse wh WITH ( NOLOCK )
INNER JOIN @tb_id tid on wh.ID=tid.Id
where wh.WareHouseType=1 and wh.IsActive=1;
create index idx_id_CustomerID on #tabHouse(id,CustomerID);

SELECT Count(1) FROM RB_WareHouse_Product a WITH ( NOLOCK )
INNER JOIN #tabHouse wh WITH ( NOLOCK ) ON a.CustomerID=wh.CustomerID AND a.WareHouseID=wh.ID
INNER JOIN RB_WareHouse_OneProductMorePosition_Product whopmpp ( NOLOCK ) ON a.CustomerID=whopmpp.CustomerID AND a.WareHouseID=whopmpp.WareHouseID AND a.SKU=whopmpp.SKU
LEFT JOIN RB_WareHouse_Position AS b WITH ( NOLOCK ) ON whopmpp.PositionID=b.ID AND whopmpp.CustomerID=b.CustomerID
LEFT JOIN RB_Product AS c WITH ( NOLOCK ) ON a.SKU=c.SKU AND a.CustomerID=c.CustomerID
LEFT JOIN (
SELECT ps.CustomerID, SKU,ps.IsFBABarCode
FROM   RB_Product_SupplierPrice sp WITH( NOLOCK )
LEFT JOIN RB_Product_Supplier ps WITH( NOLOCK ) ON sp.SupplierID = ps.ID AND sp.CustomerID = ps.CustomerID
WHERE  sp.IsDefault = 1 AND ps.IsFBABarCode = 1) s ON a.SKU=s.SKU AND s.CustomerID=a.CustomerID
LEFT JOIN RB_Warehouse_Product_ActiveInfo r WITH(NOLOCK) on a.CustomerID=r.CustomerID AND a.SKU=r.SKU AND a.WarehouseID=r.WarehouseID
LEFT JOIN RB_7DaysOutStockRecord sr WITH(NOLOCK) on a.CustomerID=sr.CustomerID and a.SKU=sr.SKU and a.WareHouseID=sr.WareHouseID
LEFT JOIN RB_Warehouse_PickingPackage pp WITH(NOLOCK) on a.CustomerID=pp.CustomerID and a.SKU=pp.SKU and a.WareHouseID=pp.WareHouseID   
WHERE  a.CustomerID=1 ;


历史记录  
  1. 2023-12-26 11:54:53, 由 吴高发 创建。
  2. 2023-12-26 11:57:20, 由 吴高发 编辑。
    修改了 影响环境，旧值为 "590,"，新值为 "590"。
    修改了 重现步骤，区别为：
    005- declare @tb_id as Tb_Ids; INSERT INTO @tb_id SELECT CONVERT(INT, item) FROM dbo.CharSplitForIn('7085,7056,7057,7556,3922,7590,4316,9029,5312,2,279,336,333,687,2205,13237,13558,1100000', ','); drop table if exists #tabHouse;select wh.CustomerID,wh.ID into #tabHouse from RB_WareHouse wh WITH ( NOLOCK ) INNER JOIN @tb_id tid on wh.ID=tid.Id where wh.WareHouseType=1 and wh.IsActive=1;create index idx_id_CustomerID on #tabHouse(id,CustomerID);SELECT Count(1) FROM RB_WareHouse_Product a WITH ( NOLOCK ) INNER JOIN #tabHouse wh WITH ( NOLOCK ) ON a.CustomerID=wh.CustomerID AND a.WareHouseID=wh.ID INNER JOIN RB_WareHouse_OneProductMorePosition_Product whopmpp ( NOLOCK ) ON a.CustomerID=whopmpp.CustomerID AND a.WareHouseID=whopmpp.WareHouseID AND a.SKU=whopmpp.SKULEFT JOIN RB_WareHouse_Position AS b WITH ( NOLOCK ) ON whopmpp.PositionID=b.ID AND whopmpp.CustomerID=b.CustomerIDLEFT JOIN RB_Product AS c WITH ( NOLOCK ) ON a.SKU=c.SKU AND a.CustomerID=c.CustomerIDLEFT JOIN (SELECT ps.CustomerID, SKU,ps.IsFBABarCodeFROM RB_Product_SupplierPrice sp WITH( NOLOCK )LEFT JOIN RB_Product_Supplier ps WITH( NOLOCK ) ON sp.SupplierID = ps.ID AND sp.CustomerID = ps.CustomerIDWHERE sp.IsDefault = 1 AND ps.IsFBABarCode = 1) s ON a.SKU=s.SKU AND s.CustomerID=a.CustomerIDLEFT JOIN RB_Warehouse_Product_ActiveInfo r WITH(NOLOCK) on a.CustomerID=r.CustomerID AND a.SKU=r.SKU AND a.WarehouseID=r.WarehouseIDLEFT JOIN RB_7DaysOutStockRecord sr WITH(NOLOCK) on a.CustomerID=sr.CustomerID and a.SKU=sr.SKU and a.WareHouseID=sr.WareHouseIDLEFT JOIN RB_Warehouse_PickingPackage pp WITH(NOLOCK) on a.CustomerID=pp.CustomerID and a.SKU=pp.SKU and a.WareHouseID=pp.WareHouseID WHERE a.CustomerID=1 ;
    005+ declare @tb_id as Tb_Ids; INSERT INTO @tb_id SELECT CONVERT(INT, item) FROM dbo.CharSplitForIn('7085,7056,7057,7556,3922,7590,4316,9029,5312,2,279,336,333,687,2205,13237,13558,1100000', ','); drop table if exists #tabHouse;select wh.CustomerID,wh.ID into #tabHouse from RB_WareHouse wh WITH ( NOLOCK ) INNER JOIN @tb_id tid on wh.ID=tid.Id where wh.WareHouseType=1 and wh.IsActive=1;create index idx_id_CustomerID on #tabHouse(id,CustomerID);SELECT Count(1) FROM RB_WareHouse_Product a WITH ( NOLOCK ) INNER JOIN #tabHouse wh WITH ( NOLOCK ) ON a.CustomerID=wh.CustomerID AND a.WareHouseID=wh.ID INNER JOIN RB_WareHouse_OneProductMorePosition_Product whopmpp ( NOLOCK ) ON a.CustomerID=whopmpp.CustomerID AND a.WareHouseID=whopmpp.WareHouseID AND a.SKU=whopmpp.SKULEFT JOIN RB_WareHouse_Position AS b WITH ( NOLOCK ) ON whopmpp.PositionID=b.ID AND whopmpp.CustomerID=b.CustomerIDLEFT JOIN RB_Product AS c WITH ( NOLOCK ) ON a.SKU=c.SKU AND a.CustomerID=c.CustomerIDLEFT JOIN (SELECT ps.CustomerID, SKU,ps.IsFBABarCodeFROM RB_Product_SupplierPrice sp WITH( NOLOCK )LEFT JOIN RB_Product_Supplier ps WITH( NOLOCK ) ON sp.SupplierID = ps.ID AND sp.CustomerID = ps.CustomerIDWHERE sp.IsDefault = 1 AND ps.IsFBABarCode = 1) s ON a.SKU=s.SKU AND s.CustomerID=a.CustomerIDLEFT JOIN RB_Warehouse_Product_ActiveInfo r WITH(NOLOCK) on a.CustomerID=r.CustomerID AND a.SKU=r.SKU AND a.WarehouseID=r.WarehouseIDLEFT JOIN RB_7DaysOutStockRecord sr WITH(NOLOCK) on a.CustomerID=sr.CustomerID and a.SKU=sr.SKU and a.WareHouseID=sr.WareHouseIDLEFT JOIN RB_Warehouse_PickingPackage pp WITH(NOLOCK) on a.CustomerID=pp.CustomerID and a.SKU=pp.SKU and a.WareHouseID=pp.WareHouseID WHERE a.CustomerID=1 ;
    005- <p>declare @tb_id as Tb_Ids; <br />INSERT INTO @tb_id SELECT CONVERT(INT, item) FROM dbo.CharSplitForIn('7085,7056,7057,7556,3922,7590,4316,9029,5312,2,279,336,333,687,2205,13237,13558,1100000', ','); <br /><br />drop table if exists #tabHouse;<br />select wh.CustomerID,wh.ID into #tabHouse from RB_WareHouse wh WITH ( NOLOCK ) <br />INNER JOIN @tb_id tid on wh.ID=tid.Id <br />where wh.WareHouseType=1 and wh.IsActive=1;<br />create index idx_id_CustomerID on #tabHouse(id,CustomerID);<br /><br />SELECT Count(1) FROM RB_WareHouse_Product a WITH ( NOLOCK ) <br />INNER JOIN #tabHouse wh WITH ( NOLOCK ) ON a.CustomerID=wh.CustomerID AND a.WareHouseID=wh.ID <br />INNER JOIN RB_WareHouse_OneProductMorePosition_Product whopmpp ( NOLOCK ) ON a.CustomerID=whopmpp.CustomerID AND a.WareHouseID=whopmpp.WareHouseID AND a.SKU=whopmpp.SKU<br />LEFT JOIN RB_WareHouse_Position AS b WITH ( NOLOCK ) ON whopmpp.PositionID=b.ID AND whopmpp.CustomerID=b.CustomerID<br />LEFT JOIN RB_Product AS c WITH ( NOLOCK ) ON a.SKU=c.SKU AND a.CustomerID=c.CustomerID<br />LEFT JOIN (<br />SELECT ps.CustomerID, SKU,ps.IsFBABarCode<br />FROM RB_Product_SupplierPrice sp WITH( NOLOCK )<br />LEFT JOIN RB_Product_Supplier ps WITH( NOLOCK ) ON sp.SupplierID = ps.ID AND sp.CustomerID = ps.CustomerID<br />WHERE sp.IsDefault = 1 AND ps.IsFBABarCode = 1) s ON a.SKU=s.SKU AND s.CustomerID=a.CustomerID<br />LEFT JOIN RB_Warehouse_Product_ActiveInfo r WITH(NOLOCK) on a.CustomerID=r.CustomerID AND a.SKU=r.SKU AND a.WarehouseID=r.WarehouseID<br />LEFT JOIN RB_7DaysOutStockRecord sr WITH(NOLOCK) on a.CustomerID=sr.CustomerID and a.SKU=sr.SKU and a.WareHouseID=sr.WareHouseID<br />LEFT JOIN RB_Warehouse_PickingPackage pp WITH(NOLOCK) on a.CustomerID=pp.CustomerID and a.SKU=pp.SKU and a.WareHouseID=pp.WareHouseID <br />WHERE a.CustomerID=1 ;<br /><br /></p>
    005+ <p>declare @tb_id as Tb_Ids; <br />INSERT INTO @tb_id SELECT CONVERT(INT, item) FROM dbo.CharSplitForIn('7085,7056,7057,7556,3922,7590,4316,9029,5312,2,279,336,333,687,2205,13237,13558,1100000', ','); <br /><br />drop table if exists #tabHouse;<br />select wh.CustomerID,wh.ID into #tabHouse from RB_WareHouse wh WITH ( NOLOCK ) <br />INNER JOIN @tb_id tid on wh.ID=tid.Id <br />where wh.WareHouseType=1 and wh.IsActive=1;<br />create index idx_id_CustomerID on #tabHouse(id,CustomerID);<br /><br />SELECT Count(1) FROM RB_WareHouse_Product a WITH ( NOLOCK ) <br />INNER JOIN #tabHouse wh WITH ( NOLOCK ) ON a.CustomerID=wh.CustomerID AND a.WareHouseID=wh.ID <br />INNER JOIN RB_WareHouse_OneProductMorePosition_Product whopmpp ( NOLOCK ) ON a.CustomerID=whopmpp.CustomerID AND a.WareHouseID=whopmpp.WareHouseID AND a.SKU=whopmpp.SKU<br />LEFT JOIN RB_WareHouse_Position AS b WITH ( NOLOCK ) ON whopmpp.PositionID=b.ID AND whopmpp.CustomerID=b.CustomerID<br />LEFT JOIN RB_Product AS c WITH ( NOLOCK ) ON a.SKU=c.SKU AND a.CustomerID=c.CustomerID<br />LEFT JOIN (<br />SELECT ps.CustomerID, SKU,ps.IsFBABarCode<br />FROM RB_Product_SupplierPrice sp WITH( NOLOCK )<br />LEFT JOIN RB_Product_Supplier ps WITH( NOLOCK ) ON sp.SupplierID = ps.ID AND sp.CustomerID = ps.CustomerID<br />WHERE sp.IsDefault = 1 AND ps.IsFBABarCode = 1) s ON a.SKU=s.SKU AND s.CustomerID=a.CustomerID<br />LEFT JOIN RB_Warehouse_Product_ActiveInfo r WITH(NOLOCK) on a.CustomerID=r.CustomerID AND a.SKU=r.SKU AND a.WarehouseID=r.WarehouseID<br />LEFT JOIN RB_7DaysOutStockRecord sr WITH(NOLOCK) on a.CustomerID=sr.CustomerID and a.SKU=sr.SKU and a.WareHouseID=sr.WareHouseID<br />LEFT JOIN RB_Warehouse_PickingPackage pp WITH(NOLOCK) on a.CustomerID=pp.CustomerID and a.SKU=pp.SKU and a.WareHouseID=pp.WareHouseID <br />WHERE a.CustomerID=1 ;</p>

  3. 2023-12-26 14:35:28, 由 李乐同 指派给 何建宝。
    修改了 指派给，旧值为 "liletong"，新值为 "hejianbao"。
  4. 2023-12-26 14:41:09, 由 何建宝 指派给 李乐同。
    修改了 指派给，旧值为 "hejianbao"，新值为 "liletong"。
    库存是程伟那边的业务范围
  5. 2023-12-26 14:42:42, 由 李乐同 指派给 程伟。
    修改了 指派给，旧值为 "liletong"，新值为 "chengwei"。
  6. 2023-12-26 15:10:19, 由 程伟 指派给 谢业福。
    修改了 指派给，旧值为 "chengwei"，新值为 "xieyefu"。
  7. 2023-12-26 15:10:33, 由 程伟 编辑。
    修改了 影响环境，旧值为 "590"，新值为 "577"。
  8. 2023-12-26 15:25:04, 由 谢业福 指派给 程伟。
    修改了 指派给，旧值为 "xieyefu"，新值为 "chengwei"。
    业务影响，不能这样优化  RB_WareHouse 表中还有其他条件
  9. 2023-12-26 18:35:32, 由 程伟 指派给 赵喜。
    修改了 指派给，旧值为 "chengwei"，新值为 "zhaoxi"。

    申请转需求


    方案：

    1. 先根据查询条件，查询出仓库ID后存到临时表，再关联临时表查询

    2. 根据仓库ID，再单独查询每个仓库的名称以及类型

    3. 导出功能方案参考以上步骤1和2


  10. 2023-12-27 15:12:00, 由 赵喜 转为 需求，编号为 #18395 WMS：库位库存查询页面超时问题优化（责任人：赵喜） 。
  11. 2023-12-27 15:12:00, 由 赵喜 关闭。
返回
所属项目 技术中心-需求池
所属模块 系统运维   性能优化
所属计划
所属迭代
Bug类型 性能问题
严重程度 一般
优先级 P1
Bug状态 已关闭
业务部门
影响功能
开发责任人
测试责任人
解决时效 0
激活次数 0
激活日期
是否确认 否
当前指派 Closed 于 2023-12-27 15:12:00
截止日期 0000-00-00
上线时间
上线时效
操作系统
浏览器
关键词
抄送给
所属迭代
相关需求
相关任务
由谁创建 吴高发 于 2023-12-26 11:54:52
影响环境 577
由谁解决 赵喜 于 2023-12-27 15:12:00
解决版本
解决方案 tostory
由谁关闭 赵喜 于 2023-12-27 15:12:00
最后修改 程伟 于 2023-12-26 18:34:45