[性能问题描述] ERP 查询 超时,IN的内容 居多
[16:12:08] -------------------------------------------------------------
CODE:N1612081
Error:11_-2_0_-2146232060
SQL:SELECT p.SKU,
ISNULL(w.WareHouseName, csw.WareHouseName) LocalWareHouseName
FROM dbo.RB_Product p (NOLOCK)
LEFT JOIN RB_Product_SupplierPrice psp (NOLOCK)
ON psp.CustomerID = p.CustomerID
AND p.SKU = psp.SKU
AND psp.IsDefault = 1
LEFT JOIN dbo.RB_Product_Supplier ps (NOLOCK)
ON ps.CustomerID = psp.CustomerID
AND psp.SupplierID = ps.ID
LEFT JOIN RB_WareHouse w (NOLOCK)
ON w.CustomerID = ps.CustomerID
AND ps.TheWareHouseID = w.ID
LEFT JOIN RB_Customer_Setting cs (NOLOCK)
ON cs.CustomerID = p.CustomerID
LEFT JOIN RB_WareHouse csw (NOLOCK)
ON csw.CustomerID = cs.CustomerID
AND cs.DefaultWareHouseID = csw.ID
WHERE p.CustomerID = @CustomerID
AND p.SKU IN ( SELECT CAST(item AS VARCHAR(50)) FROM dbo.CharSplitForIn(@SKUStr,',') );
PARAMS:@CustomerID = 1 ,@SKUStr = 2216517,11027130,4718878,8720215,4119553,2615681,3744350,1904964,4840413,3819286,11295718,3159120,2588495,1902244,3159187,13523964,3013923,1934688,6073533,7908561,3363785,6355736,7734041,13938706,13100286,10960777,13907989,13533667,11389502,12221462,9141542,5663410,13578325,13895490,13490378,12591799,10026764,7300900,14169670,10384486,6245765,13413555,3278944,11010181,6707281,9967780,14291642,3972776,11860311,13502412,6659973,5977866,8628525,10386469,12147023,7028933,14358194,5429570,14308393,11199839,11258974,11586333,9978557,12073270,11873390,14002201,13687854,7539828,11226434,10809976,8106543,8754012,13739638,11855892,14018050,10704148,11267313,5207660,12093325,5561895,3631686,4008938,11803518,6020630,11818817,3538447,8934938,2261194,8631050,6389688,5404340,6752749,9272086,9731556,5303370,6676757,6855455,9223607,6249595,11642795,6604367,2901719,11849649,14325503,6827551,5900912,8360730,14058439,10524437,2714241,5799780,5240294,11046693,13782572,9240035,8403164,4089343,2739164,10687567,6148702,4385820,6337247,2929691,6314178,11848123,14287654,1924715,8004041,7290037,9343737,3063090,10601570,9010943,6100550,14370176,11283414,10524538,3081065,8552968,8701119,1487201,7935479,5579843,10695336,5733776,10517781,10096728,4157457,2321171,5682903,8891823,6795784,5187523,4984625,4112751,6150690,8279738,13751459,3513274,10446132,9338084,14284384,6809580,11354704,4257695,13696704,8320096,6697990,3665498,7870124,8174184,10755462,8452462,1738156,3210414,14593403,13971644,7593170,7420939,4154746,3968850,6793990,8356673,1750495,2816305,12601285,4565226,3152470,13966779,3050946,8757601,1813720,2259936,13242723,3235782,10094627,6477211,11307082,5745216,10890786,1673235,2636519,7785787,8207289,14401281,9711986,3934024,12017869,11510607,2978439,12053075,3758506,2733049,10905535,10736285,2168742,8452621,8330186,14017667,4963183,10555896,11062099,2962444,10514562,10477532,14593377,7233694,13885844,10652891,10828631,11769510,4003097,4485649,10372360,9840556,3660333,13108698,7321041,6995232,8646981,7117335,10893428,12859945,12060624
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.ExecuteReader(CommandBehavior behavior, String method)
在 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
在 System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
在 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
在 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
在 RB.DAL.DBOperator.Query(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB) 在 RB.DAL.DBOperator.toCommonTipException(SqlException ex, String sql, SqlParameter[] cmdParms)
在 RB.DAL.DBOperator.Query(String SQLString, SqlCommand cmd, Int32 Times, SqlParameter[] cmdParms, Boolean IsWriteDB)
在 RB.DALExtendV2.RB_OrderInfoExtendV2.GetOrderProductWareHouseList(Int32 customerId, List`1 skuList)
在 PandaBuy.ERP.Web.IrobotBox.Restock.Local.LocalOrderList.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_restock_local_localorderlist_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)
[分析过程]查询一下业务设计是否有漏洞
[期望结果]提升性能,不出小黄鸡


