Q

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?

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
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close