Ask the Expert

Subquery slows query performance

Here is a simplified version of the actual query:

 
SELECT  M.CNTR_NO  
FROM TB_GATE_TRAN GT,TB_MASTER M 
WHERE  GT.VSL_CD = M.VSL_CD AND  GT.CNTR_NO
 = M.CNTR_NO  AND GT.CNTR_NO NOT IN  
   (SELECT CNTR_NO FROM VT_APPT  
    WHERE APPT_MOVE_TYPE = 'EmptyIn' AND STATUS IN ('Act','Mis')
    AND  TRUCK_CO IN ('QTPI') 
    AND  APPT_MAKE_TIME > (trunc(SYSDATE) - 104)
    );
Both the main and subquery have been simplified. I'm using Oracle 9i.

Rows in TB_MASTER: 162000
in TB_GATE_TRAN: 113000
in VT_APPT: 109000

There are indexes on VT_APPT:
 
VT_APPT(APPT_MOVE_TYPE)
VT_APPT(STATUS)
VT_APPT(APPT_MOVE_TYPE,status)
VT_APPT(CNTR_NO)
For the data that we are using, the subquery does not return any data, as there is no qualifying data for the new project. Also there may be CNTR_NO in VT_APPT which are not present in TB_MASTER as well as TB_GATE_TRAN.

The problem: With the subquery, the SQL takes a lot of time, about 190 secs to return 520 rows. If we remove the subquery, it returns in 1 or 2 seconds. How can we speed up the query?

    Requires Free Membership to View

Your best bet is to cast the query in different (but semantically equivalent) forms. For example, you could try using
 SELECT  M.CNTR_NO  FROM
TB_GATE_TRAN GT,TB_MASTER M
 WHERE  GT.VSL_CD = M.VSL_CD 
AND  GT.CNTR_NO = M.CNTR_NO  
AND NOT EXISTS  
  (SELECT 1 FROM
  VT_APPT  VT WHERE VT.CNTR_NO = GT.CNTR_NO
  AND VT.APPT_MOVE_TYPE = 'EmptyIn' AND VT.STATUS IN ('Act','Mis')
  AND  VT.TRUCK_CO IN ('QTPI') 
  AND  VT.APPT_MAKE_TIME > (trunc(SYSDATE) - 104));
You could also use an antijoin of VT_APPT and TB_GATE_TRAN (where VT.CNTR_NO != GT.CNTR_NO), or create an inline view of your subquery. The point is to try the various ways that SQL allows you to express a query. You will frequently find that one approach is orders of magnitude faster than others.

This was first published in May 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: