RE: Can a Query fill the system?

[jpcarr@xxxxxxxxxxxx]

>While we're on the subject, which is better performance wise, having the
>smallest file as the primary joined to a larger file, or visa versa?
>I remember being told this many moons ago.

Left File(1st one defined in Query/400)  should be the smaller.   Remember,
That's the smallest after any where clauses.

>And another bottom feeders question, performance wise, am I better off
>querying a logical file than the primary? I expect yes, but ignorance is
not
>bliss.

As a rule of Thumb,  Chose the PF.  The optimizer should find the one you
need.
Remember, the indexes are looked at chronologically(the last ones created
are the first ones looked at)  So if the most useful LF/Index is the first
one you create, and you have large number of indexes (over ????? now comes
the posts on "We have more than that")   The optimizer may timeout before
finding the "Perfect" choice.

If you are running an SQLRPG program,  do a PRTSQLINF and see some
interesting history of past run times.

Before running your program or before running even Query/400  do a STRDBG.
Then Look at your job log.  You will see messages like CPI432C which tells
you why it didn't use certain indexes.

Also if there is only (say) a few thousand records in the file,  It doesn't
matter what file (PF or LF)  you point at,  the optimizer will decide that
it would be faster just to do a full data base scan(ie read each record)
instead of wasting time bringing an index into memory, etc. etc.

Hope this helps

John