-
Type:
Sprint Defect
-
Status: Done (View Workflow)
-
Priority:
Blocker
-
Resolution: Completed
-
Affects Version/s: CFA 2020R1
-
Fix Version/s: CFA 2020R1
-
Component/s: Entity
-
Labels:
-
Sprint:CFAMX 2020R1 Hardening
MXC_Inventory_GetTransactionSalesOrderDetails is timing out on SandBox we can not go production with this defect!!
It is caused by the change :
CFAMX-6268 - Refactor the OrderDetails page query to use two new stored procedures to get data rather than a complex nhibernate query. Also, add a new Vendor Item setting for HoursSinceLastCountThreshold.
From the higher level when creating an order we are calling bunch of rest apis and one of them is GetOrder API.
When looking at the sql profiler bunch of calls are being made GETOrder API and one of them is
exec sp_executesql N'EXEC [dbo].[MXC_Inventory_GetTransactionSalesOrderDetails] @transactionSalesOrderId = @p0',N'@p0 bigint',@p0=2620505
This query returns fine in JAT it is because [tbTransactionStockCount] table has does not have much data in it.
JATTesting: ~ 2 million
SandBoxDB : ~ 635 million records
Production: ~1 billion
So this query will definitely fail in production since prod has more data then SandBox.
Here is the more detail what sql piece is not taking too long responding.
You may run this in SandBox DB.
declare @transactionSalesOrderId BIGINT = 2262406 DECLARE @minDate DATE = DATEADD(DAY, -180, GETDATE()) DECLARE @entityId BIGINT = (select EntityId from tbTransactionSalesOrder where iTransaction_num = @transactionSalesOrderId) --SELECT * – FROM tbTransactionSalesOrder O WITH(NOLOCK) – INNER JOIN tbTransactionSalesOrderDetail D WITH(NOLOCK) – ON D.iTransaction_num = O.iTransaction_num – INNER JOIN tbItem I WITH(NOLOCK) – ON I.ItemID = D.ItemID – LEFT JOIN tbEntityItem E WITH(NOLOCK) – ON E.EntityID = D.EntityID AND E.ItemID = D.ItemID – INNER JOIN tbVendorItem V WITH(NOLOCK) – ON V.VendorID = D.VendorID AND V.ItemID = D.ItemID AND D.sItem_cd = V.VendorCode – LEFT JOIN tbGroup G WITH(NOLOCK) – ON G.GroupID = I.GroupID – LEFT JOIN tbTransactionSalesOrderDetailItemQuantity Q WITH(NOLOCK) – ON Q.TransactionSalesOrderDetailId = D.TransactionSalesOrderDetailID – LEFT JOIN tbAllocationDetail A WITH(NOLOCK) – ON A.AllocationDetailId = Q.AllocationDetailId – LEFT JOIN ( SELECT tscl.EntityID, --ISNULL(tscd.ItemId, tsc.ItemID) as ItemId, MAX(tsc.StockCountDate) --MAX(ISNULL(tsc.StockCountDate, tscl.ApplyDate)) as LastCountDate FROM [dbo].[tbTransactionStockCountLocation] tscl WITH(NOLOCK) LEFT JOIN [dbo].[tbTransactionStockCount] tsc WITH(NOLOCK) ON tscl.TransactionStockCountLocationID = tsc.TransactionStockCountLocationID AND tscl.AutoGenerated = 0 AND tscl.ApplyStatus > 0 AND tsc.StockCount >= 0 AND tsc.BusinessDay > @minDate --LEFT JOIN tbTransactionStockCountLocationDetail tscd – ON tscl.TransactionStockCountLocationId = tscd.TransactionStockCountLocationID – AND tscl.ReferenceNumber = @transactionSalesOrderId – AND tscd.StockCount >= 0 WHERE tscl.EntityID = @EntityId AND tscl.ApplyDate > @minDate GROUP BY tscl.EntityID – ISNULL(tscd.ItemId, tsc.ItemID) --) S ON ( D.ItemID = S.ItemID AND V.HoursSinceLastCountThreshold IS NOT NULL ) --WHERE E.InUse = 1 --AND O.iTransaction_num = @transactionSalesOrderId --AND O.EntityID = @entityId
- Clones
-
CFAMX-7508
GetByStoreNumber API Needs to be Refactored
-
- Done
-
