I'm trying to make a SQL request but that request is taking forever to finish. The request is done in Excel 2003 with VBA.

Size of the TABLE:

  • TABLE1 = 12600 Row
  • TABLE2 = 361K Row

Here's the query:

SELECT DISTINCT 
    y.code AS CODE,
    y.name AS LIBELLE,
    #[...]
    #[...]
    #[...]
    #[...]
    y.IS_BILAN,
    y.INACTIVE,
    (SELECT COUNT(1) 
     FROM TABLE1 d, TABLE2 a 
     WHERE a.record_date_time >= '2018/01/01' 
       AND a.record_date_time < '2019/01/01' 
       AND global_status <> 'C' 
       AND a.id = d.id 
       AND d.type_id = y.code) AS TOTAL_2018
FROM 
    anal_exam y 
ORDER BY 
    code

The whole query run instantly when removing the last part "SELECT COUNT(1)"

The execution plan I see in Oracle SQL Developer:

Execution plan

How could I speed up this query? It takes 47 minutes to finish

3 Answers

1
T McKeown On

Try defining your JOIN like this:

SELECT DISTINCT 
 y.code AS CODE,
 y.name AS LIBELLE,
 y.IS_BILAN,
 y.INACTIVE,
 COUNT(*) AS TOTAL_2018
FROM anal_exam y 
JOIN TABLE1 d
  ON d.type_id = y.code
  JOIN TABLE2 a 
    ON d.ID = a.ID
WHERE a.record_date_time BETWEEN '2018/01/01' AND '2019/01/01'
  AND global_status <> 'C'
order by code
0
Jmeyer On

I added a GROUP BY y.code, y.name, y.IS_BILAN, y.inactive at the end and it work's

  • runtime is 47 sec.

It's quite fast but i'm wondering if there's a way to get the line with count = 0 because 3k line are omitted in this query

0
Jmeyer On

With the code from T McKeown i'm getting this result :

CODE1|LIBELLE1|T|T|1530
CODE3|LIBELLE2|T|T|20
CODE5|LIBELLE3|T|T|143

The result i'm seeking include the line with count()=0

CODE1|LIBELLE1|T|T|1530
CODE2|LIBELLE2|T|F|0
CODE3|LIBELLE2|T|F|20
CODE4|LIBELLE4|T|T|0
CODE5|LIBELLE3|F|T|143

How can i achieve this ?