Thanks, Eduardo. Obrigado, or gracias. :-) Adding an INDEX for (ProjID,
PSubClass, lower(cust)) has dropped both queries to less than 2 seconds:
BEFORE INDEX:
Run Time: real 125.947 user 0.795605 sys 4.648830
Run Time: real 130.343 user 1.622410 sys 7.035645
AFTER INDEX:
Run Time: real 1.384 user 0.374402 sys 0.717605
Run Time: real 1.498 user 0.312002 sys 0.967206
That is amazing, and an unexpected triumph. And, after reading a bit about
the INDEXing, I actually understand the reasoning behind the INDEX. Now I
can go and add INDEXes for daily routines searches and reporting. But also
get rid of some of them that are not working as expected. Thanks.
josé
-----Original Message-----
From: Eduardo Morras
Sent: Saturday, October 21, 2017 1:06 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Any change to make this query better?
On Fri, 20 Oct 2017 14:21:38 -0400
"jose isaias cabrera" <jic...@barrioinvi.net> wrote:
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>
You could try indexing by (ProjID, PSubClass, lower(cust)).
You do all the work on the same table 'fake' joined with the result itself,
LSOpenJobs.
SELECT max(edate) from LSOpenJobs AS I WHERE I.ProjID = O.ProjID AND
I.PSubClass = 'QuoteAppr'
You can try a WITH with the main query and subselect from it to get those
two values (max(edate) and min(edate))
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
