How can I replace WHERE IN choices in WHERE clause to reduce execution time of the request?

Question

I am using DBF database in a C++ project written on RAD Studio 10.2 Tokyo. To make request on this database, I am using UniDac Devart component (that is a little bit limited compared to MySQL itself). The problem I have is when using WHERE IN condition in the request, the request makes too much time to be execute (more than one hour sometimes).

This is the request I have :

SELECT accountrp, SUM(amounteur) AS montant FROM %s 
WHERE doctype='1' AND period<>'00' AND
matchno IN(SELECT matchno FROM %s GROUP BY matchno HAVING SUM(amounteur)<>0)
GROUP BY accountrp

accountrp, doctype, period are character and amounteur is numeric.

The problem is around the line matchno IN. I would like to optimize the request without using IN. I saw on Internet that WHERE IN condition can be replaced by INNER JOIN junctions but I Don't know if it is the solution and how to do this.

Can you help me, please ?

score 0 · Answer 1 · answered Dec 28 '18 at 08:32

0

You can try with below

SELECT s.accountrp, SUM(s.amounteur) AS montant FROM %s s
  join 
   (SELECT matchno FROM %s GROUP BY matchno HAVING SUM(amounteur)<>0) t
  on s.matchno =t.matchno
WHERE s.doctype='1' AND s.period<>'00'
GROUP BY s.accountrp

answered Dec 28 '18 at 08:32

flyingfox

13,414
3
24
39

score 0 · Accepted Answer · answered Dec 28 '18 at 08:33

0

A first suggestion you could change the IN clause in a INNER JOIN
this should be more efficent for performance

SELECT accountrp, 
    SUM(amounteur) AS montant 
FROM %s  as s 
INNER JOIN  ( 
  SELECT matchno 
  FROM %s 
  GROUP BY matchno 
  HAVING SUM(amounteur)<>0
) t on t.matchno = s.matchno
WHERE doctype='1' AND period<>'00' AND
GROUP BY accountrp

this because a In clause is equivalent to a set of OR condition and the query is performed each time for each OR .. a inner join instead is just a join between tow table and is performed just one time

answered Dec 28 '18 at 08:33

ScaisEdge

131,976
10
91
107

It seems to be very fast ! INNER JOIN and JOIN are the same think ? – development Dec 28 '18 at 08:47
yes inner join and join are the same ..but is more explici and easy to read – ScaisEdge Dec 28 '18 at 09:04
@development all the test you need ..but the difference between a IN clause and a JOIN don't change .. youd conld check that the difference in performance between the two method ins base on the numbedr of the lement in result ...more elements more differnce in time ..and over a certain number of element the In clasue raise an error because there is a limit in the number of elements manage by IN clause – ScaisEdge Dec 28 '18 at 09:13
@scaisEdge In MySQL 5.6 and later, the subquery should only be executed once, and result stored in a temporary table. What makes your query faster is probably that this temporary table will be first table in join. This way, instead of reading all rows of table s, only the rows with relevant matchno will be read. – Øystein Grøvlen Dec 28 '18 at 09:52
@oysteing . you can try changing the order of nested subquery and yuo can see that a inner joion on subselect is always faster than a IN clause on the same subquery .. this for simple concept ..each result value of a IN clause implies a scan for this value in related table .. (as an OR clause) .. for a JOIN the result set of the query is itself a table and the relazione between these values is scanned just one time .. The position of the table in the query is managed by query optimized and can be influence but in a complex way not ever convenient – ScaisEdge Dec 28 '18 at 10:11
@scaisEdge Please read about subquery materialization. (https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html) It is basically executed as an inner join, but join order is unfortunately fixed. – Øystein Grøvlen Jan 05 '19 at 09:50
@oysteing . for what i know the INNER JOIN the materialization since .. 5.6 at least .. – ScaisEdge Jan 05 '19 at 10:47

How can I replace WHERE IN choices in WHERE clause to reduce execution time of the request?

2 Answers2