[步骤]
[11:06:45] -------------------------------------------------------------
CODE:N11064532
Error:21_596_1_-2146232060
SQL:select top 5000 ID into #tempid from rb_product WITH(NOLOCK) where id>0 order by id;
SELECT p.ID,
p.SKU,
p.CustomerID,
(SELECT Sum(psd.SalesNum) FROM RB_Report_SKUDay psd WITH(NOLOCK) WHERE p.SKU = psd.SKU AND p.CustomerID = psd.CustomerID AND psd.SalesDay > Dateadd(dd, -7, Getdate())) SalesNum7,
(SELECT Sum(psd.SalesNum) FROM RB_Report_SKUDay psd WITH(NOLOCK) WHERE p.SKU = psd.SKU AND p.CustomerID = psd.CustomerID AND psd.SalesDay > Dateadd(dd, -14, Getdate())) SalesNum14,
(SELECT Sum(psd.SalesNum) FROM RB_Report_SKUDay psd WITH(NOLOCK) WHERE p.SKU = psd.SKU AND p.CustomerID = psd.CustomerID AND psd.SalesDay > Dateadd(dd, -30, Getdate())) SalesNum30,
(SELECT Sum(psd.SalesNum) FROM RB_Report_SKUDay psd WITH(NOLOCK) WHERE p.SKU = psd.SKU AND p.CustomerID = psd.CustomerID AND psd.SalesDay > Dateadd(dd, -90, Getdate())) SalesNum90,
pa.AddTime,
pa.DevelopTime,
pa.CheckTime,
(SELECT Isnull((CASE WHEN Isnull(Max(LastOutOperateTime), 0) > Isnull(Max(LastInOperateTime), 0)
THEN Isnull(Datediff(day, Max(LastOutOperateTime), Getdate()), 0)
ELSE Isnull(Datediff(day, Max(LastInOperateTime), Getdate()), 0)
END ), 0) Kl
FROM View_RB_ProductChina_WareHouse_Table vrpw WITH(NOLOCK) WHERE p.SKU = vrpw.SKU AND p.CustomerID = vrpw.CustomerID) Kl,
(SELECT Isnull(Sum(GoodNum), 0) FROM View_RB_ProductChina_WareHouse_Table vrpw WITH(NOLOCK) WHERE p.SKU = vrpw.SKU AND p.CustomerID = vrpw.CustomerID) AllGoodNum,
(SELECT Isnull(MAX(AllWarehouseGoodNum), 0) FROM View_RB_ProductChina_WareHouse_Table vrpw WITH(NOLOCK) WHERE p.SKU = vrpw.SKU AND p.CustomerID = vrpw.CustomerID) AllWarehouseGoodNum,
(SELECT Isnull(Avg(AllAvgDailySales), 0) FROM View_RB_ProductChina_WareHouse_Table vrpw WITH(NOLOCK) WHERE p.SKU = vrpw.SKU AND p.CustomerID = vrpw.CustomerID) AllAvgDailySales,
pss.TotalShelvesNum,
pss.ProductNewType,
pss.ProductVitalityType,
Sum(wp.GoodNum) GoodNum
FROM RB_Product p WITH(NOLOCK)
LEFT JOIN RB_Product_Admin pa WITH(NOLOCK) ON p.SKU = pa.SKU AND p.CustomerID = pa.CustomerID
LEFT JOIN RB_Product_SalesStatus pss WITH(NOLOCK) ON pss.SKU = p.SKU AND pss.CustomerID = p.CustomerID
LEFT JOIN RB_WareHouse_Product wp WITH(NOLOCK) ON wp.SKU = p.SKU AND wp.CustomerID = p.CustomerID
WHERE p.CustomerID = 1 AND p.DeleteStatus = 0 and exists(select 1 from #tempid where ID=p.ID)
GROUP BY p.ID,p.SKU,p.CustomerID,pa.AddTime,pa.DevelopTime,pa.CheckTime,pss.TotalShelvesNum,pss.ProductNewType,pss.ProductVitalityType;
drop table #tempid;
PARAMS:
MESSAGE:Cannot continue the execution because the session is in the kill state.
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.TryCloseInternal(Boolean closeReader)
在 System.Data.SqlClient.SqlDataReader.Close()
在 System.Data.Common.DbDataReader.Dispose(Boolean disposing)
在 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.DAL.RB_Product_SalesStatus.RollingQueryProduct(Int32 customerId, Int64 id, Int32 pageSize)
在 Biz.Irobotbox.Product.ProductActivityClass.RunProductRuleV3()
在 ChinaDownLoadImage.Task.Product.Task_Product_SetActivityRule.Execute()
在 RB.Utility.SystemTask.<doTask>b__43_0()
在 System.Threading.Tasks.Task.Execute()
在 System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
在 System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
在 System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
在 System.Threading.Tasks.Task.ExecuteEntry(Boolean bPreventDoubleExecution)
在 System.Threading.ThreadPoolWorkQueue.Dispatch()
[结果]
[期望]
排查问题 优化 SQL