I've been currently tasked with optimising some of the SQL queries that are ran on very large datasets. I have the query below which I'm trying to optimise but at the moment struggling with it
The query takes roughly around 40-45 minutes to comeback with an answer which is obviously not acceptable. It will even take longer as the dataset grows within DB.
I believe its the inner MAX() function that dramatically slows down the query?
I appreciate any help or advice in making the query better and faster.
WHERE positionview.outbound = false
AND positionview.referenceIdentifier is null
AND positionview.responseType = 'XYZ'
AND positionview.equipmentTimestamp >='2016-02-02 14:44:08.213'
AND positionview.equipmentTimestamp <='2016-02-05 14:44:08.213'
AND positionview.equipmentTimestamp =(
FROM PositionView positionview2
WHERE positionview.iNumber = positionview2.iNumber
AND positionview2.outbound = false
AND positionview2.referenceIdentifier is null
AND positionview2.responseType = 'XYZ'
AND positionview2.equipmentTimestamp >='2016-02-02 14:44:08.213'
AND positionview2.equipmentTimestamp <='2016-02-05 14:44:08.213');
Many thanks in advance!