Greetings!

Any thoughts on how I can make this query faster? I would really appreciated. Thanks. SELECT a.*,b.*,c.* FROM Project_List AS a LEFT JOIN Project_Dashboard AS b ON a.ProjID = b.ProjID LEFT JOIN Project_Extras AS c ON a.ProjID = c.ProjID AND a.PMO_Board_Report != 'No' AND a.Status = 'Acknowledged' AND Project_Type != 'Outside IT perimeter' AND a.Start_date < date('now') AND ( (a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15') OR (a.Target_Go_Live_Date = '' AND a.Finish_Date > substr(date('now'),1,4) || '-01-15') ) AND ( b.TimeIndicator = '0' OR b.CostIndicator = '0' ) AND a.InsertDate = (SELECT MAX(InsertDate) FROM Project_List WHERE a.ProjID = ProjID) AND b.InsertDate = (SELECT MAX(InsertDate) FROM Project_Dashboard WHERE b.ProjID = ProjID) AND c.InsertDate = (SELECT MAX(InsertDate) FROM Project_Extras WHERE c.ProjID = ProjID) WHERE a.ProjID = b.ProjID AND a.ProjID = c.ProjID group BY a.Manager, a.ProjID ORDER BY a.Manager, a.ProjID ; This is what I get from the .expert function:

(no new indexes)

MULTI-INDEX OR
INDEX 1
SEARCH TABLE Project_Dashboard AS b USING INDEX PDash_TimeIndicator (TimeIndicator=?)
INDEX 2
SEARCH TABLE Project_Dashboard AS b USING INDEX PDash_CostIndicator (CostIndicator=?)
CORRELATED SCALAR SUBQUERY 2
SEARCH TABLE Project_Dashboard USING COVERING INDEX PDash_ProjID_InsertDate (ProjID=?)
SEARCH TABLE Project_List AS a USING INDEX PL_ProjID_InsertDate (ProjID=?)
CORRELATED SCALAR SUBQUERY 1
SEARCH TABLE Project_List USING COVERING INDEX PL_ProjID_InsertDate (ProjID=?)
SEARCH TABLE Project_Extras AS c USING INDEX sqlite_autoindex_Project_Extras_1 (ProjID=?)
CORRELATED SCALAR SUBQUERY 3
SEARCH TABLE Project_Extras USING COVERING INDEX sqlite_autoindex_Project_Extras_1 (ProjID=?)
USE TEMP B-TREE FOR GROUP BY

josé

Thanks Gunter. I can fix, at least, 3 of your suggestions because I understand them. :-) I may come back for more later. :-) Gracias.

josé

The conditions in the WHERE clause make the LEFT superfluous and the ON conditions duplicitous.

Remove the word LEFT and the entire ON conditions (the duplicate conditions), change the first AND to WHERE and the last WHERE to AND.

SELECT a.*,b.*,c.*
  FROM Project_List AS a 
  JOIN Project_Dashboard AS b
  JOIN Project_Extras AS c
  WHERE
    a.PMO_Board_Report != 'No' 
  AND
    a.Status = 'Acknowledged' 
  AND
    Project_Type != 'Outside IT perimeter'
  AND
    a.Start_date < date('now')
  AND
  (
    (a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15') OR
    (a.Target_Go_Live_Date = '' AND a.Finish_Date >  substr(date('now'),1,4) || '-01-15')
  )
  AND
  (
    b.TimeIndicator = '0'
    OR
    b.CostIndicator = '0'
  )
  AND
    a.InsertDate = 
      (SELECT MAX(InsertDate) FROM Project_List WHERE a.ProjID = ProjID)
  AND
    b.InsertDate = 
      (SELECT MAX(InsertDate) FROM Project_Dashboard WHERE b.ProjID = ProjID)
  AND
    c.InsertDate = 
      (SELECT MAX(InsertDate) FROM Project_Extras WHERE c.ProjID = ProjID)
  AND a.ProjID = b.ProjID 
  AND a.ProjID = c.ProjID 
group BY a.Manager, a.ProjID
ORDER BY a.Manager, a.ProjID
;

Whats killing you is the three correlated subqueries. You need to generate those into a materialized view first and then use that in the query. Eg:

with maxes(ProjID, pl_insert, pd_insert, pe_insert)
  as (
      select projid,
             max(InsertDate),
             (select max(insertdate) from project_dashboard where ProjID = e.projID),
             (select max(insertdate) from project_extras where projid = e.projid)
        from project list e
    group by projid
     )
SELECT a.*,b.*,c.*
  FROM Project_List AS a 
  JOIN Project_Dashboard AS b
  JOIN Project_Extras AS c
  JOIN maxes
  WHERE
    a.PMO_Board_Report != 'No' 
  AND
    a.Status = 'Acknowledged' 
  AND
    Project_Type != 'Outside IT perimeter'
  AND
    a.Start_date < date('now')
  AND
  (
    (a.Target_Go_Live_Date > substr(date('now'),1,4) || '-01-15') OR
    (a.Target_Go_Live_Date = '' AND a.Finish_Date >  substr(date('now'),1,4) || '-01-15')
  )
  AND
  (
    b.TimeIndicator = '0'
    OR
    b.CostIndicator = '0'
  )
  AND
    a.InsertDate = maxes.pl_insert
  AND
    b.InsertDate = maxes.pd_insert
  AND
    c.InsertDate = maxes.pe_insert
  AND a.ProjID = b.ProjID 
  AND a.ProjID = c.ProjID 
  AND a.projid = maxes.projid
group BY a.Manager, a.ProjID
ORDER BY a.Manager, a.ProjID
;

And then you need to "fiddle" to make sure that the subquery "maxes" is getting materialized (if it does not get materialized as is, then maybe making it uselessly DISTINCT (as in SELECT DISTINCT ...) will do it.

Just curious, how long does the query take now?

For this part of the query:

with maxes(ProjID, pl_insert, pd_insert, pe_insert)
  as (
      select projid,
             max(InsertDate),
             (select max(insertdate) from project_dashboard where ProjID = e.projID),
             (select max(insertdate) from project_extras where projid = e.projid)
        from project_list e
    group by projid
     )
select * 
  from maxes

See if that is what is taking the greatest part of the time. You may be able to speed up the query even more by optimizing this part which basically generates the set of candidates based on the (projid, insertdate) for each of the other tables.

The remaining conditions in the original query are basically just "culling and sorting" those candidates.

Just curious, how long does the query take now?

The original query took,

Run Time: real 15.901 user 0.390625 sys 2.125000

or longer, and the fixed query where Keith was instrumental with his fix, took,

Run Time: real 1.275 user 0.062500 sys 0.312500

Thanks.

I am happy with the 2 seconds or less result. Thanks.

josé