Greetings!
This takes about 1.5 minutes to run with sqlite v3.20.1 with about 200K
records
sqlite> explain query plan
...> SELECT
...> O.XtraF AS PortalID,
...> O.ProjID,
...> O.A_No AS GTXNo,
...> O.proj AS ProjName,
...> O.lang AS Target,
...> (SELECT max(edate) from LSOpenJobs AS I WHERE I.ProjID = O.ProjID
AND
...> I.PSubClass = 'QuoteAppr' ) AS QuoteAvailable,
...> (SELECT min(edate) from LSOpenJobs AS E WHERE E.ProjID = O.ProjID
AND
...> PSubClass = 'Delivery') AS DeliveryDate,
...> sum(O.Xtra8) AS PriceUSD,
...> 0 AS PriceCAD,
...> sum(O.ProjFund) AS TransferCost,
...> O.XtraE AS Department,
...> O.XtraA AS BillTo,
...> O.pmuk AS Contact,
...> '-' AS Notes1,
...> '-' AS Notes2
...> from LSOpenJobs AS O WHERE lower(cust) = 'xerox' AND
...> Xtra9 LIKE '2017-09-%'
...> GROUP BY ProjID,lang HAVING sum(ProjFund) > 0;
0|0|0|SCAN TABLE LSOpenJobs AS O USING INDEX OjPid
0|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE LSOpenJobs AS I USING INDEX OjPid (ProjID=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE LSOpenJobs AS E USING INDEX OjPid (ProjID=?)
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
