RE: Spool file journalling

Joe

Others have probably said this a different way, but try
this:

The first statement has 2 "and" predicates - these
restrict the result set, and the optimizer can run them
first, which will eliminate potentially many records,
before getting to the "in" predicates, which are
really "or". The access path suggested might help, but
don't count on it for the first statement.

The problem with the 2nd one is the "or" predicate that
others have mentioned - it means that the entire left
side needs to be evaluated, and if false, the right side
still needs to be evaluated.

Generally, "or" adds work, because you never know to
reject something until you've looked at
everything. "and" exits at the first false test.

The logical maybe helps the second by allowing each side
to exit sooner.

Oy!

Vern
> Here's why I have problems with SQL.  Take a look at the following
> statements:
>
> select * from sccc1 a, sccc1 b
>  where a.c1flag3 = 'V' and
>  a.c1source in ('WRITE', 'READ')
>  and a.c1kwid = b.c1prnt
>  and b.c1source in ('SCREEN-REC', 'SCREEN-FILE')
>
> select * from sccc1 a, sccc1 b
>  where a.c1flag3 = 'V' and
>  ((a.c1source = 'WRITE'
>    and a.c1kwid = b.c1prnt
>    and b.c1source = 'SCREEN-REC') or
>  (a.c1source = 'READ'
>    and a.c1kwid = b.c1prnt
>    and b.c1source = 'SCREEN-FILE'))
>
> Both are trying to do the same thing - use a COBOL cross-reference to
> determine READs and WRITEs to a workstation.  READs use the file name,
> WRITEs use the record name.  The references to "c1kwid/c1prnt", which are
> the same in both queries, are used to make sure the cross-reference lines
> are related.
>
> The first one, which is a little more inclusive (it would include READs on
> the record and WRITEs to the file) runs in milliseconds.  The second one I
> fired off and it chewed up 73% of my machine for over 30 seconds before I
> shut it down.  (Eventually CFINT kicks in and grinds the thing to a halt,
> even though it's the only job on the machine.)
>
> My observation has several parts:
>
> 1. Would any of the SQL gurus like to point out any serious problems in my
> syntax?  Are there better ways to do this?
>
> 2. I looked at the joblog under debug to determine the access paths, and
> while the optimizer recommends I create a logical over C1SOURCE, the two
> statements use the exact same paths.
>
> 3. Why would what seems like a pretty simple syntax change cause such
> drastic difference in response time?
>
>
> =======UPDATE=========
>
> By adding the logical over C1SOURCE as the optimizer suggested, the second
> runtime dropped to milliseconds.  Evidently, the second syntax somehow
> causes the index to be rebuilt over and over again.
>
> Joe
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>