[步骤]
[13:17:19] -------------------------------------------------------------
CODE:N1317191
Error:11_-2_0_-2146232060
SQL:
SELECT pop.ID ,
pop.CustomerID ,
pop.MergerPackageNo ,
pop.TrackNumber ,
pop.TurnNumber,
pop.LogisticsOrderNo ,
pop.PackageState ,
pop.SystemWeight ,
pop.PrintBatchNo ,
pop.TransportID ,
pop.GetPackageTraceTime,
pop.PrintType,
pop.OvertimeTagType,
oi.OrderSourceID,
oi.ClientOrderCode,
oi.ShipMemo,
ISNULL(pop.GetTrackNumberTime, oi.SaveTrackNumbersTime) TrackNumberTime,
t.PrintType TransportPrintType,
t.TransportState,
t.TrackingNumberValidDays,
oi.OrderStatus,
oi.OrderState,
os.OrderSourceType,
os.IsPrintNote,
pop.Country Shorthand,
ISNULL(api.Transport_API_Class, -1) Transport_API_Class ,
t.AllowPrint,
t.AllowLogistPrint,
t.AllowTurnPrint,
pop.PackagePositionCode,
pop.PrintAddressState,
pop.AddTime,
cse.IsMergeLabelAliExpress,
cse.LimitTransports,
oi.AmazonShipmentId,
oi.Token OrderInfoToken,oi.AliExpressTrusteeType,pop.VirtuallyFastType
FROM RB_Procurement_Order_Package pop ( NOLOCK )
Left JOIN dbo.RB_OrderInfo oi ( NOLOCK ) ON oi.CustomerID = pop.CustomerID
AND oi.OrderCode = pop.MergerPackageNo
Left JOIN RB_Transport t( NOLOCK ) ON t.CustomerID = pop.CustomerID
AND t.ID = pop.TransportID
Left JOIN RB_Transport_API api ( NOLOCK ) ON api.CustomerID = pop.CustomerID
AND api.ID = t.Transport_API_ID
LEFT JOIN RB_OrderSource os ( NOLOCK ) ON os.CustomerID = pop.CustomerID
AND os.ID = oi.OrderSourceID
LEFT JOIN RB_Customer_Setting_Extend cse ( NOLOCK ) ON cse.CustomerID = pop.CustomerID
WHERE pop.ID IN (SELECT ID
FROM RB_Procurement_Order_Package (NOLOCK)
WHERE CustomerID = @CustomerID
AND (TrackNumber=@track OR LogisticsOrderNo=@track ) )
AND pop.CustomerID = @CustomerID
AND pop.WareHouseID = @WareHouseID
AND
(
(
pop.TrackNumber <> ''
AND
(
ISNULL(t.TrackingNumberValidDays, 0) = 0
OR
(
pop.GetTrackNumberTime IS NOT NULL
AND Isnull(t.TrackingNumberValidDays, 0) > 0
AND Datediff(SECOND, Getdate(), ( Dateadd(day, t.TrackingNumberValidDays, pop.GetTrackNumberTime) )) >= 0
)
)
AND
(
t.AllowTurnPrint = 0
OR
(t.AllowTurnPrint = 1 AND ISNULL(pop.TurnNumber, '') != '')
)
)
OR
(
pop.TrackNumber = ''
AND t.AllowPrint = 1
AND ISNULL(pop.LogisticsOrderNo, '') != ''
AND
(
ISNULL(t.TrackingNumberValidDays, 0) = 0
OR
(
pop.GetTrackNumberTime IS NOT NULL
AND Isnull(t.TrackingNumberValidDays, 0) > 0
AND Datediff(SECOND, Getdate(), ( Dateadd(day, t.TrackingNumberValidDays, pop.GetTrackNumberTime) )) >= 0
)
)
)
OR
(
pop.TrackNumber = ''
AND t.AllowPrint = 1
AND ISNULL(pop.LogisticsOrderNo, '') = ''
AND t.AllowLogistPrint = 1
AND ISNULL(pop.TurnNumber, '') = ''
AND t.AllowTurnPrint = 0
)
)
PARAMS:@CustomerID = 1 ,@WareHouseID = 333 ,@track = ,
MESSAGE:执行超时已过期。完成操作之前已超时或服务器未响应。
TRACE: 在 System.Data.SqlClient.SqlConnection.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.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.DALExtend.RB_Procurement_Order_PackagePrintExtend.GetPackagePrintInfoByScanBarCode(CM_GetScanPackParam model)
在 Biz.Irobotbox.Delivery.CsPackagePrint.PackagePrintClass.GetPackagePrintInfoByScanBarCode(CM_GetScanPackParam model)
在 RB.WebApi.ApiCode.WmsCs.CsPrint.GetPackagePrintUrl(GetPackagePrintUrlRequest request, Int32 adminId, String userName)
在 System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
在 System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
在 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
在 RB.WebApi.ApiCode.API_CsEntrance.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.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)
-------------------------------------------------------------
查看了语句 非常慢，返回的数据量也很大 1133856 条记录
[结果]
[期望]
改造SQL 添加核心条件 提升SQL 性能