RE: Logical File or OPNQRYF or anyother way ?

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Joe Pluta
> Sent: Monday, March 07, 2005 5:16 PM
> To: 'Midrange Systems Technical Discussion'
> Subject: RE: Logical File or OPNQRYF or anyother way ? 
> 
> 
> 
> WAIT WAIT WAIT!
> 
> If you do NOT already have an index over the file, the index 
> needs to be
> built at run time.  This is a disastrous performance hit, 
> especially on
> a large file.  In fact, if you debug ANY query, you will immediately
> note that the SQL processor tells you to create the appropriate index.
> And what is an index?  It's a logical file.

True, we're not rally discussing logical files vs. indexes.  

Really the discussion RPG native I/O from a logical file/index vs. the query 
engine.


> 
> Here's the cutoff: if the sequence is used rarely or the file is very
> small, you don't need a logical; use SQL and let it build its index on
> the fly.  If the sequence is used often and especially over large
> numbers of records, ALWAYS create a logical view.
> 
> ALWAYS.
> 

No not ALWAYS.  

Yes, a "perfect" index/logical file, with MAINT(*IMMED), is going to give you 
the best performance with both RPG native I/O and SQL.

But best may not ALWAYS be needed.  While RPG native I/O can't efficiantly make 
use of any but a "perfect" index, the query engine can and does.


> 
> > For example, lets say you have three fields: key1, key2, key3.  To
> provide
> > for all possible sorts, you'd need 9 separate logicals with the
> overhead
> > or performance penalties associated with maintaining the 
> access paths.
> On
> > the other hand, you could simply use 3 indexes, one over 
> each key.  As
> > needed the DB2 query engine make composite keys from the separate
> indexes.
> > 
> > Does this make sense?
> 
> Actually, no it doesn't.  How do you think this magic occurs? 
>  The nine
> different sort sequences are still required, and there is a very
> definite overhead when trying to combine two single-field 
> keys to create
> a composite key.  Like most indexing operations, this overhead can be
> disastrous on large files.

Yeah, I shouldn't have said sorting here.  Really what I was thinking about is 
the way the query engine can AND or OR indexes together for selection.

Off the top of my head, I'm not sure how the query engine deals with multiple 
single key indexes when the ordering needed uses multiple keys.  I might check 
more into this.


> 
> I have HEARD anecdotally about engines that somehow reduce 
> the overhead
> of this job, but I suggest you actually test it in practice on a
> multi-million record file to see how it performs.
> 
> 
> > Additionally, one plus for embedded SQL is it's higher 
> performance vs.
> > native RPG I/O.  _IF_ you use it properly.  This means, only select
> the
> > fields of interest to you and fetch more than one row at a time.
> 
> SQL ONLY outperforms native I/O on blocked operations, especially
> queries and even then only if the indexes are already 
> created.  And that
> means a logical file.
> 
> In ALL cases, EVERY case, WITHOUT AN EXCEPTION, if you regularly read
> records from a file using the same sequence, you should have a logical
> file over that physical with those fields.  Even if you 
> subsequently use
> an SQL statement for selection criteria, the SQL engine will still use
> the logical file for ordering the records.
> 
> Like the JVM, people seem to get the idea that the SQL engine 
> is a magic
> land where I/O doesn't have to occur and keys don't need to 
> be compared.
> But in the end, it's just more code.
> 
> Joe
> 

No it's not magic and you're right that a price always has to be paid.  The 
question is do you pay for 100's or 1000's of MAINT(*IMMED) "perfect" 
indexes/logicals.  Or do you pay at run-time when using the query engine with 
less than perfect indexes.

Charles