Home » RDBMS Server » Performance Tuning » nested select statements
nested select statements [message #64992] Thu, 25 March 2004 08:02 Go to next message
David
Messages: 110
Registered: November 1998
Senior Member
Hello,

I have a query with several nested select statements that takes about 4 seconds to run.

If I run the nested statements seperately and manually include the results in my top level select, the time to execute is much lower - less than 1 second for all of the combined select statements.

How can I improve the performance of the top level statement?

Here is my exact query :

SELECT bm.classNameA2A2, bm.idA2A2 FROM BaselineMember bm, GDLSCorePart version WHERE version.idA3MasterReference IN (36863603, 36861423, 36864779, 36862513, 36842983) AND version.idA2A2 = bm.idA3B5 AND bm.idA3A5 IN (SELECT idA2A2 FROM WTProductConfiguration START WITH idA2A2 = 41300399 CONNECT BY PRIOR idA3C2IterationInfo = idA2A2) AND NOT EXISTS (SELECT brm.idA2A2 FROM BaselineRemoveMember brm WHERE brm.idA3B5 = bm.idA2A2 AND brm.idA3A5 IN (SELECT idA2A2 FROM WTProductConfiguration START WITH idA2A2 = 41300399 CONNECT BY PRIOR idA3C2IterationInfo = idA2A2)      );

which takes 4 seconds.

If I run the nested query :

SELECT idA2A2 FROM WTProductConfiguration START WITH idA2A2 = 41300399 CONNECT BY PRIOR idA3C2IterationInfo = idA2A2;

this takes .01 seconds to run and returns the values :

41300399,41300390

Now if I include this resultsset into the original query :

SELECT bm.classNameA2A2, bm.idA2A2 FROM BaselineMember bm, GDLSCorePart version WHERE version.idA3MasterReference IN (36863603, 36861423, 36864779, 36862513, 36842983) AND version.idA2A2 = bm.idA3B5 AND bm.idA3A5 IN (41300399,41300390) AND NOT EXISTS (SELECT brm.idA2A2 FROM BaselineRemoveMember brm WHERE brm.idA3B5 = bm.idA2A2 AND brm.idA3A5 IN (41300399,41300390));
.391 seconds

The query takes only .4 seconds.

Is this a sequence problem?

Sorry if I am missing somethign obvious.. I am new to this.

 

 
Re: nested select statements [message #65011 is a reply to message #64992] Mon, 29 March 2004 11:58 Go to previous messageGo to next message
Ivan
Messages: 180
Registered: June 2000
Senior Member
The EXPLAIN PLAN result of your query would help more, but before you post it, try this:

SELECT bm.classNameA2A2, bm.idA2A2
  FROM BaselineMember bm, GDLSCorePart version
 WHERE version.idA3MasterReference IN (36863603, 36861423, 36864779, 36862513, 36842983)
   AND version.idA2A2 = bm.idA3B5
   AND bm.idA3A5 IN (SELECT idA2A2
                       FROM WTProductConfiguration
                      START WITH idA2A2 = 41300399
                      CONNECT BY PRIOR idA3C2IterationInfo = idA2A2)
   AND NOT EXISTS (SELECT <font color=red>/*+ PUSH_SUBQ */</font>
                          brm.idA2A2
                     FROM BaselineRemoveMember brm
                    WHERE brm.idA3B5 = bm.idA2A2
                      AND brm.idA3A5 IN (SELECT idA2A2
                                           FROM WTProductConfiguration
                                          START WITH idA2A2 = 41300399
                                         CONNECT BY PRIOR idA3C2IterationInfo = idA2A2));


The PUSH_SUBQ hint causes non-merged subqueries to be evaluated at the earliest
possible place in the execution plan. Normally, subqueries that are not merged are
executed as the last step in the execution plan. If the subquery is relatively
inexpensive and reduces the number of rows significantly, then it improves
performance to evaluate the subquery earlier.
Re: nested select statements [message #65026 is a reply to message #65011] Wed, 31 March 2004 09:07 Go to previous message
David
Messages: 110
Registered: November 1998
Senior Member
I tried the PUSH_SUBQ hint on all of the nested subqueries, but it doesnt make it faster.

Thanks anyway.
Previous Topic: How to join performance views ?
Next Topic: Performance Tuning a Query
Goto Forum:
  


Current Time: Sat Apr 20 05:08:52 CDT 2024