Slow query

Question

Hello everyone

I have the following query that is taking more than 1 hour to run.

SELECT 
    RES.NUM_PROCESS,
    RES.ID_SYSTEM
FROM 
    RESTRICTED_PRECESS RES   
WHERE   
    RES.ID_SYSTEM <> 'CYFV'  
    AND RES.NUM_PROCESS NOT IN 
                                (
                                SELECT PR.NUM_PROCESS  
                                FROM PRECESS PR   
                                WHERE PR.ID_SYSTEM = RES.ID_SYSTEM
                                )

Can someone tell me how I do this query with NOT EXISTS

Thank you

Answer 1

 SELECT 
     RES.NUM_PROCESS,
     RES.ID_SYSTEM
 FROM 
     RESTRICTED_PRECESS RES   
 WHERE   
     RES.ID_SYSTEM <> 'CYFV'  
     AND  NOT Exists   ( SELECT 1  FROM PRECESS PR   
                                 WHERE PR.ID_SYSTEM = RES.ID_SYSTEM
                                 and RES.NUM_PROCESS and  PR.NUM_PROCESS  
                                 )

Answer 2

You can try INNER JOIN instead:

SELECT 
    RES.NUM_PROCESS,
    RES.ID_SYSTEM
 FROM 
    RESTRICTED_PRECESS RES 
    INNER JOIN PRECESS PR ON PR.ID_SYSTEM = RES.ID_SYSTEM AND PR.NUM_PROCESS <> RES.NUM_PROCESS
WHERE 
    RES.ID_SYSTEM <> 'CYFV';

Answer 3

Check if it has sense to try this too:

SELECT 
     NUM_PROCESS,
     ID_SYSTEM
 FROM 
     RESTRICTED_PRECESS 
 WHERE   
     ID_SYSTEM <> 'CYFV'  
EXCEPT
SELECT 
     NUM_PROCESS,
     ID_SYSTEM
 FROM 
     PRECESS 

Answer 4

Hi @Edgar Toia Neto ，

A small modification on Jingyang's query as below:

SELECT   
      RES.NUM_PROCESS,  
      RES.ID_SYSTEM  
  FROM   
      RESTRICTED_PRECESS RES     
  WHERE     
      RES.ID_SYSTEM <> 'CYFV'    
      AND  NOT Exists   ( SELECT 1  FROM PRECESS PR     
                                  WHERE PR.ID_SYSTEM = RES.ID_SYSTEM  
                                  and RES.NUM_PROCESS =  PR.NUM_PROCESS    
                                  )  

If we change one query from 'NOT IN' to 'NOT EXISTS', we need to add one condition which is 'RES.NUM_PROCESS = PR.NUM_PROCESS' in your situation.

In addition, you could also have a try with 'LEFT JOIN'. In some situation, it could be faster than others.

SELECT   
     RES.NUM_PROCESS,  
     RES.ID_SYSTEM  
 FROM   
     RESTRICTED_PRECESS RES     
LEFT JOIN PRECESS PR ON  PR.ID_SYSTEM = RES.ID_SYSTEM   
 WHERE     
     RES.ID_SYSTEM <> 'CYFV'    
  AND RES.NUM_PROCESS<>PR.NUM_PROCESS  

'EXCEPT' is also a good solution but you need to consider whether it is needed to exclude the NULL values during the query.

Besides, it is recommeded to create some indexes on columns 'ID_SYSTEM' and 'NUM_PROCESS' on both tables.

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa