Re: Bad timing with nested sql select

From: <stowe_at_mcs.net>
Date: 1995/05/23
Message-ID: <3pt4uc$gsp_at_News1.mcs.com>#1/1


> unter_at_mecati.mecasoft.ch (Stefano UNTERNAEHRER) writes:
> Hello folks!
>
> I have a really bad performance with a nested sql selection.
> The select is structurated like:
>
> SELECT ... /* 3th select */
> FROM ...
> WHERE ... IN
> SELECT ... /* 2nd select */
> FROM ...
> WHERE ... IN
> SELECT ... /* 1st select */
> FROM ...
> WHERE ...
Oh, lord, this is going to take forever. The IN operator is probably the least efficient choice of a plethora of other options and operators: EXISTS, =, and outer joins being preferable to IN's.

> The complete nested selection require 37.2400 seconds.
>
> The 1st selection alone require: 1.0600 seconds,
> the 2nd, giving the IN values: 0.2000 seconds,
> and the 3th with IN values: 0.2400 seconds,
>
> with a total of supposed 1.5000 seconds,
> much better then 37 !!!!

If each subquery is executing once for each parent, then the times should be MULTIPLIED by the number of parent rows, not simply added.   

> Why will my nested selection lost all this time?

Essentially, because you are telling the database to do a lot more work than a simple set of three independent queries.

> I think the problem is not in rollback segments since
> this is just a select, not DML (insert/update/delete) right?

Although it is *possible*, this is not a likely cause of the problem... Your problem is the syntactical difficulty imposed on the database.

> And I also don't think the problem can be in the index ts.
> So, the problem may be with the temporary tablespace, where
> the result of differents select are stored during the
> nested selection... right?

Not necessarily -- depending on the execution plan, you may not need the temporary tablespace at all.   

> This is the tablespace I use as temporary:
> NAME INIT_EXT NEXT_EXT MIN_EXT MAX_EXT PCT_INCREASE STATUS
> ---- -------- -------- ------- ------- ------------ ------
> TEMP 16384 51200 1 999 20 ONLINE
> The TEMP tablespace as a Free Space Fragmentation Index
> FSFI = 23.8094802
>
> What else? Thanks for any help,
> Stefano
>>>>

Well, you didn't include enough of the statement for me to rewrite it, but it can be rewritten -- either as a join or an EXISTS subquery. Also helpful would be you execution plan.

Michael Stowe
Constellation Engineering, Inc.
http://www.mcs.com/~stowe Received on Tue May 23 1995 - 00:00:00 CEST

Original text of this message