Date:         Tue, 4 Oct 2005 12:55:39 -0700
Reply-To:     David L Cassell <davidlcassell@MSN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         David L Cassell <davidlcassell@MSN.COM>
Subject:      Re: Understanding Execution Plan from SQL Optimizer
In-Reply-To:  <200510032020.j93JRhfQ018777@malibu.cc.uga.edu>
Content-Type: text/plain; format=flowed

jbalint@GMAIL.COM wrote:
>Hi all, I am trying to understand a query that I am having a problem
>with. I have used the same technique in a program before, but it is
>executing different now. The SQL shows like this:
>
>/* get the purchase dates in a way we can
>    show the difference in days */
>create table cycle1 as
>select
>         customer_id,
>         (select min(pdate)
>          from &dataset as y
>          where pdate > x0.pdate and
>          x0.customer_id = y.customer_id
>         ) as next,
>         pdate
>from
>         &dataset as x0
>where
>         pdate >= &periodstart
>
>Now I thought SAS created a temporary table for the subquery before,
>but it doesn't seem to be doing it this time. I'll show _method and
>_tree here:
>
>http://www.rafb.net/paste/results/x2TcDn69.html
>
>Now, it mostly makes sense, but it's terribly inefficient. Also, I
>couldn't find a reference that shows what "SUBC" is in the execution
>tree. Can anybody suggest something to change the way this is running?

I see that Ian has spoken.  So what more needs to be said?

Okay, I still can't shut up.

One thing you need to think about is the SQL Optimizer.  The optimization is
likely to change as the underlying data needs drive it.  We've talked about
this
in SAS-L before, when allocating enough buffer space has suddenly allowed
PROC SQL to use a hash to speed up processing, instead of having to resort
to a less-than optimal merge.  I believe Paul Dorfman has given concrete
examples
of this.  So the optimizer may change the methods if the data sets grow, or
shrink, or change shape.  So the difference you may be seeing may be due
to the sizes of the data sets, rather than anything you did to the system.

HTH,
David
--
David L. Cassell
mathematical statistician
Design Pathways
3115 NW Norwood Pl.
Corvallis OR 97330

_________________________________________________________________
Don’t just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/