Any change to make this query better?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Any change to make this query better?

jose isaias cabrera-3

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Any change to make this query better?

Simon Slavin-3


On 20 Oct 2017, at 7:21pm, jose isaias cabrera <[hidden email]> wrote:

> This takes about 1.5 minutes to run with sqlite v3.20.1 with about 200K records

Can you please time each of the sub-SELECTs ?

Do you have any indexes defined on LSOpenJobs ?

Can you give LSOpenJobs.cust an affinity of NOCASE ?  If so, you do not need the "lower" and can define an index which will speed up the selection.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Any change to make this query better?

David Raymond
In reply to this post by jose isaias cabrera-3
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Any change to make this query better?

jose isaias cabrera-3

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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Any change to make this query better?

Eduardo Morras-2
In reply to this post by jose isaias cabrera-3
On Fri, 20 Oct 2017 14:21:38 -0400
"jose isaias cabrera" <[hidden email]> 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))

HTH
---   ---
Eduardo Morras <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Any change to make this query better?

jose isaias cabrera-3

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: [hidden email]
Subject: Re: [sqlite] Any change to make this query better?

On Fri, 20 Oct 2017 14:21:38 -0400
"jose isaias cabrera" <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users