I need to find the latest location of each cargo item in a consignment. We mostly do this by looking at the route selected for a consignment and then finding the latest (max) time entered against nodes of this route. For example if a route has 5 nodes and we have entered timings against first 3 nodes, then the latest timing (max time) will tell us its location among the 3 nodes.
I am really stu开发者_运维问答ck on this query regarding performance issues. Even on few hundred rows, it takes more than 2 minutes. Please suggest how can I improve this query or any alternative approach I should acquire?Note: ATA= Actual Time of Arrival and ATD = Actual Time of Departure
SELECT DISTINCT
c.id as cid
, c.ref as cons_ref
, c.Name
, c.CustRef
FROM consignments c
INNER JOIN routes r ON c.Route = r.ID
INNER JOIN routes_nodes rn ON rn.Route = r.ID
INNER JOIN cargo_timing ct ON c.ID=ct.ConsignmentID
INNER JOIN ( SELECT t.ConsignmentID, Max(t.firstata) as MaxDate
FROM cargo_timing t
GROUP BY t.ConsignmentID
) as TMax
ON ( TMax.MaxDate=ct.firstata
AND TMax.ConsignmentID=c.ID
)
INNER JOIN nodes an ON ct.routenodeid = an.ID
INNER JOIN contract cor ON cor.ID = c.Contract
WHERE c.Type = 'Road'
AND ( c.ATD = 0 AND c.ATA != 0 )
AND (cor.contract_reference in ('Generic','BP001','020-543-912'))
ORDER BY c.ref ASC
Can you assign each node an ID and add that ID to the table consignments
as a foreign key? That would allow you to set the "current" node when the cargo passes through a node. The time difference between now and the last update of the "current node" field in the consignments
table would then give you an idea how for the cargo got on the route.
精彩评论