Below are four runs of each version. The old one is still faster...
Original version:
Run Time: real 126.549 user 0.265202 sys 1.045207
Run Time: real 123.742 user 0.655204 sys 2.776818
Run Time: real 125.947 user 0.795605 sys 4.648830
Run Time: real 130.343 user 1.622410 sys 7.035645
your version:
Run Time: real 165.174 user 0.889206 sys 5.116833
Run Time: real 161.924 user 1.497610 sys 6.146439
Run Time: real 151.702 user 1.544410 sys 7.628449
Run Time: real 149.010 user 1.840812 sys 9.516061
And yes, PSubClass has an INDEX. Thanks.
-----Original Message-----
From: David Raymond
Sent: Friday, October 20, 2017 3:53 PM
To: SQLite mailing list
Subject: Re: [sqlite] Any change to make this query better?
Make sure it gets the same results, but how about something like the below.
The collate nocase on the cust field was also a good suggestion.
select
XtraF as PortalID,
ProjID,
A_No as GTXNo,
proj as ProjName,
lang as Target,
QuoteAvailable,
DeliveryDate,
sum(Xtra8) as PriceUSD,
0 as PriceCAD,
sum(ProjFund) as TransferCost,
XtraE as Department,
XtraA as BillTo,
pmuk as Contact,
'-' as Notes1,
'-' as Notes2
from LSOpenJobs as O
inner join
(select ProjID, max(edate) as QuoteAvailable
from LSOpenJobs
where PSubClass = 'QuoteAppr'
group by ProjID) as I
using (ProjID)
inner join
(select ProjID, min(edate) as DeliveryDate
from LSOpenJobs
where PSubClass = 'Delivery'
group by ProjID) as E
using (ProjID)
where lower(cust) = 'xerox'
and Xtra9 like '2017-09-%'
group by ProjID, lang
having sum(ProjFund) > 0;
selectid|order|from|detail
1|0|0|SCAN TABLE LSOpenJobs USING INDEX OjPid
2|0|0|SCAN TABLE LSOpenJobs USING INDEX OjPid
0|0|1|SCAN SUBQUERY 1 AS I
0|1|0|SEARCH TABLE LSOpenJobs AS O USING INDEX OjPid (ProjID=?)
0|2|2|SEARCH SUBQUERY 2 AS E USING AUTOMATIC COVERING INDEX (ProjID=?)
0|0|0|USE TEMP B-TREE FOR GROUP BY
Or maybe an index on PSubClass to speed up the subqueries?
sqlite> create index idx1 on LSOpenJobs (PSubClass, ProjID);
selectid|order|from|detail
1|0|0|SEARCH TABLE LSOpenJobs USING INDEX idx1 (PSubClass=?)
2|0|0|SEARCH TABLE LSOpenJobs USING INDEX idx1 (PSubClass=?)
0|0|1|SCAN SUBQUERY 1 AS I
0|1|0|SEARCH TABLE LSOpenJobs AS O USING INDEX OjPid (ProjID=?)
0|2|2|SEARCH SUBQUERY 2 AS E USING AUTOMATIC COVERING INDEX (ProjID=?)
0|0|0|USE TEMP B-TREE FOR GROUP BY
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of jose isaias cabrera
Sent: Friday, October 20, 2017 2:22 PM
To: SQLite mailing list
Subject: [sqlite] Any change to make this query better?
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
