Thanks, David. Long story short, my data keeps growing and some of the queries that were fast at the beginning of the year are becoming slower and slower. I am trying to speed them up with the novice SQL talent I possess. So, this query,
`
SELECT a.*,sum(b.AnnualDossier) as Dossier FROM Project_List AS a
LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID
AND
b.InsertDate =
(SELECT MAX(InsertDate) FROM ABT_Budget WHERE b.ProjID = ProjID)
WHERE a.ProjID IN
(
SELECT ProjID FROM Project_List WHERE 1=1
AND
PMO_Board_Report != 'No'
AND
(
(
Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15'
)
OR
(
Target_Go_Live_Date = '' AND Finish_Date > substr(date('now'),1,4) || '-01-15'
)
)
AND
InsertDate =
(
SELECT MAX(InsertDate) FROM Project_List WHERE 1=1
AND
PMO_Board_Report != 'No'
AND
(
(
Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15'
)
OR
(
Target_Go_Live_Date = '' AND Finish_Date > substr(date('now'),1,4) || '-01-15'
)
)
)
)
AND
a.InsertDate =
(
SELECT MAX(InsertDate) FROM Project_List WHERE a.ProjID = ProjID
)
group BY a.ProjID;
`
Takes about 12-20 seconds depending,
`
Run Time: real 12.539 user 10.765625 sys 0.468750
`
I am trying to speed it up by following Keith's suggestion which is to get the ProjID, and both InsertDates ahead of time, and using their values in the query. Using one of Keith's examples, I was able to minimize the response time from 30 secods to less than 2 seconds on another query. So, I was trying to follow the same idea to try to fix the query above in which I failed miserably. So, I will go back to the drawing board and try to re-do this one and see if I can get any speed, and also the same output.