INVENTORY COUNT QUERY TIMEOUT
Creation date: 29/08/2024 10:54
Updated: 29/08/2024 10:54
Question:
Answer:
Investigation
______________________________________
check index
____________________________________
then explain
_____________________________-
EXPLAIN SELECT SUM(IFNULL(h1.qtyAllocated, 0)) AS picking
,SUM(IFNULL(h1.qtyPa, 0)) AS putaway
,SUM(IFNULL(h1.qtyRpIn, 0)) AS replenishment
,SUM(IFNULL(h1.qtyMvIn, 0)) AS MOVE
,COUNT(CASE
WHEN h4.taskType = 'RP'
THEN taskId_Sequence
END) AS RPcount
,COUNT(CASE
WHEN h4.taskType = 'PK'
THEN taskId_Sequence
END) AS PKcount
,COUNT(CASE
WHEN h4.taskType = 'PA'
THEN taskId_Sequence
END) AS PAcount
,COUNT(CASE
WHEN h4.taskType = 'MV'
THEN taskId_Sequence
END) AS MVcount
FROM INV_LOT_LOC_ID h1
LEFT JOIN BAS_LOCATION h2 ON h1.organizationId = h2.organizationId
AND h1.warehouseId = h2.warehouseId
AND h1.locationId = h2.locationId
LEFT JOIN BAS_ZONE h3 ON h2.organizationId = h3.organizationId
AND h2.warehouseId = h3.warehouseId
AND h2.zoneGroup = h3.zoneGroup
AND h2.zoneId = h3.zoneId
LEFT JOIN TSK_TASKLISTS h4 ON h1.organizationId = h4.organizationId
AND h1.warehouseId = h4.warehouseId
AND h1.locationId = h4.fmLocation
AND h1.traceId = h4.fmId
AND h1.lotNum = h4.fmLotNum
LEFT JOIN BAS_SKU h5 ON h1.organizationId = h5.organizationId
AND h1.customerId = h5.customerId
AND h1.sku = h5.sku
WHERE h1.organizationId = 'OJV_QL'
AND h1.warehouseId = '90200'
AND h4.taskProcess = '00'
AND h4.taskType IN (
'RP'
,'PK'
,'PA'
,'MV'
)
AND h1.locationId >= 'FC101-01-01'
AND h1.locationId <= 'FC408-04-01'
AND h2.locGroup1 >= 'COUNT'
AND h2.locGroup1 <= 'COUNT';
//////////////////////////////////////////////////
Solution
_______________________
Inventory count show query timeout as below
Location
ID FM: FC101-01-01
Location ID TO FC408-04-01
Answer:
Investigation
______________________________________
check index
____________________________________
SHOW INDEX FROM INV_LOT_LOC_ID;
SHOW INDEX FROM BAS_LOCATION;
SHOW INDEX FROM BAS_ZONE;
SHOW INDEX FROM TSK_TASKLISTS;
SHOW INDEX FROM BAS_SKU;
then explain
_____________________________-
EXPLAIN SELECT SUM(IFNULL(h1.qtyAllocated, 0)) AS picking
,SUM(IFNULL(h1.qtyPa, 0)) AS putaway
,SUM(IFNULL(h1.qtyRpIn, 0)) AS replenishment
,SUM(IFNULL(h1.qtyMvIn, 0)) AS MOVE
,COUNT(CASE
WHEN h4.taskType = 'RP'
THEN taskId_Sequence
END) AS RPcount
,COUNT(CASE
WHEN h4.taskType = 'PK'
THEN taskId_Sequence
END) AS PKcount
,COUNT(CASE
WHEN h4.taskType = 'PA'
THEN taskId_Sequence
END) AS PAcount
,COUNT(CASE
WHEN h4.taskType = 'MV'
THEN taskId_Sequence
END) AS MVcount
FROM INV_LOT_LOC_ID h1
LEFT JOIN BAS_LOCATION h2 ON h1.organizationId = h2.organizationId
AND h1.warehouseId = h2.warehouseId
AND h1.locationId = h2.locationId
LEFT JOIN BAS_ZONE h3 ON h2.organizationId = h3.organizationId
AND h2.warehouseId = h3.warehouseId
AND h2.zoneGroup = h3.zoneGroup
AND h2.zoneId = h3.zoneId
LEFT JOIN TSK_TASKLISTS h4 ON h1.organizationId = h4.organizationId
AND h1.warehouseId = h4.warehouseId
AND h1.locationId = h4.fmLocation
AND h1.traceId = h4.fmId
AND h1.lotNum = h4.fmLotNum
LEFT JOIN BAS_SKU h5 ON h1.organizationId = h5.organizationId
AND h1.customerId = h5.customerId
AND h1.sku = h5.sku
WHERE h1.organizationId = 'OJV_QL'
AND h1.warehouseId = '90200'
AND h4.taskProcess = '00'
AND h4.taskType IN (
'RP'
,'PK'
,'PA'
,'MV'
)
AND h1.locationId >= 'FC101-01-01'
AND h1.locationId <= 'FC408-04-01'
AND h2.locGroup1 >= 'COUNT'
AND h2.locGroup1 <= 'COUNT';
//////////////////////////////////////////////////
Solution
_______________________
ALTER TABLE bas_zone
ADD INDEX idx_bas_zone (organizationId, warehouseId, zoneGroup,zoneId);
ALTER TABLE tsk_tasklists
ADD INDEX idx_tsk_tasklists (organizationId, warehouseId, fmLocation,fmId, fmLotNum, taskProcess, taskType);
Incident Report - FM - INVENTORY COUNT QUERY TIMEOUT (TICKET 510256).docx
fm.jpg