Hi,
I am currently inserting values into a table (say machine status) and this table will get updated once the status has been changed.
I am using an update query with some sub queries to execute the above. I have a dilemma like whether this logic slows down the gateway (as longest query) or impacts the db.
Query eg:
UPDATE tbl_productiondata AS pd
,
(SELECT RunID,EquipmentID
FROM cdcms.tbl_productiondata where EquipmentID= :iEquipmentID order by RunID desc limit 1, 1) As t
SET pd.Actual_OrderEndTime = :iOrderEndTime,
pd.Actual_OrderRunTime = TIME_TO_SEC(TIMEDIFF(:iOrderEndTime, Actual_OrderStartTime)),
pd._modifiedDate = NOW(),
pd._modifiedUser = "ign_upd"
WHERE pd.RunID = t.RunID
AND pd.EquipmentID = t.EquipmentID
AND pd.Actual_OrderEndTime IS NULL
What below logic is recommended to do the mentioned function.
Update Query or StoredProcedure or system.db.runUpdateQuery