SQLite Forum

Help speeding up this query
The query is not very useful without the schema it is executed against, but I do have a couple of notes:

Using LEFT JOIN does not give the QP any leeway to reorder the tables for faster lookup. Are you writing LEFT JOIN because it is needed for the correct answer or just because you like writing it?

Duplicating the JOIN ON expressions in the WHERE clause is superflous, and putting expressions in the JOIN ON that do not impact the JOIN strikes me as strange. The JOIN ON condition should only contain expressions that actually impact the JOIN, and the other conditions stated in the WHERE clause.

The SELECT MAX() subqueries use the ProjID field from all three tables while it is already known that they have to be identical. This could cause extra work to extract values that are already known. Choose one (probably a.ProjID) and stick to it.

Don't retrieve all columns of all participating tables, choose the ones you really need instead. At least, eliminate the ProjID field from two of the tables, as these will contain identical values.

SQLite has an optimization for single MAX() aggregate queries that I suspect applies here. This should allow SELECTing MAX(a.InsertDate), MAX(b.InsertDate), MAX(c.InsertDate) from the join and dropping the SELECT MAX(InsertDate) subqueries.

Edit: Try ordering the constraints pertaingin to each table so that the most restrictive comes first, thus saving the effort of checking the less restrictive constraints.