Re: SQL query optimization

Mike

Lots of possibilities.    How about;

you may have 30 LF's over the file and the optimizer gave up looking after
about 8 - 10 and did the sort. (BTW,  it inspects them Chronologically.
Last one created, first one looked at.   If your most useful LF indexes
were the ones you  created first,  the optimizer may never find them
(especially if you have 20,30,50.....100 LF's over the file)

It may have taken more time for it to bring the index into memory than do
the sort.   Remember if the file has less than (say) 20,000 records or so,
the optimizer probably NEVER will use any  index.   It can do a DB scan
over the whole file faster than anything else.

It looks at available Memory size,   Processor number(speed),  are you
running SMP,   What type of DASD you are running,  and ALL kinds of stuff.
It's really sweet.   Imagine,  Adding more memory, or different disk type
or processor upgrade and the Optimizer automatically recognizing that
change and the next time your ad hoc query runs it will know that fact.

Use PRTSQLINF on the program object and see what you see on the spool file.

Many many times  people's "Tuning"  to second guess the optimizer be the
equivalent of an axe against a chainsaw.

Let it do the work.   Pay attention to the creation dates of the LF's,
Watch your Where statement(that statement usually dictates which index if
any you are using,   NOT the Order By statement BTW).

How many records are in the File?   How many LF's are over the file would
be my first questions.


John Carr

------------------------------------------------


I have an embedded SQL program that I have noticed a delay on the OPEN
CURSOR.  After running it through debug, i find that it is creating a
'Temporary result file' CPI4325 with reason code of '7' which says that the
optimizer decided to do a sort rather than an access path.
The file has a Key on it, in the order that i have selected on my SQL
statement.  So i thought(ok, it doesn't like the key on the physical.) SO i
created a logical file with the proper key.  I still get the same thing
happening.

Does any one have any ideas.

Why is it not using the access path of my logical?
suggesstions!!

Thanks

Mike