Re: Index/LF question

David

What release of the OS are you on? I believe use of a logical file for SQL is now recorded in recent releases.

As for why is it faster? See the post I sent to Doug Palme, as well as Birgitta's post after that. We list manuals you should read, to understand how SQL does things.

The basic reason for the better performance is, indexes are very fast ways to find a key value, which will point to the record you want almost instantly. SQL uses methods that are like CHAIN - that is a very broad generalization, not meant to be exact, just an analogy. But it will find a record by key in basically the same way, because of the database functions at the lowest level being the same. Just imagine how long your RPG would take if you had to read every record in a file until you found a matching one - and how fast it is when you can CHAIN to it. Reading every record is called a table scan. Getting a record by key is called an index probe, I believe. See the manuals - you need to understand this stuff in order to make your SQL perform best.

You did not have to create an LF - the index was just as good and maybe better, because of page sizes - the amount of data transferred between disk and memory. And you can even use the SQL index in RPG the same as any keyed LF.

Regards
Vern

-------------- Original message --------------
From: David FOXWELL <David.FOXWELL@xxxxxxxxx>

Hi,

I had a complaint that a job was taking nearly 3 hours to run at night in
certain circumstances. In fact I'd added an SQL request on a PF without
realizing the consequences. The job already took a long time before I added it,
maybe half as long.

Anyway, I used the iNav index advisor to create the necessary index. Now, the
job runs in 5 minutes! I am wondering why it is so much faster than before I
added the lengthy SQL request?

I can't find any other SQL references in the job. RPG programs are using the PF
with a CHAIN operation. I have replaced the index I created with iNav with an LF
and it runs just as fast. The same PF with about 2 million records now has 7 LF.

Also, as my new LF is only currently used in my SQL request, there are no
references it. How should I prevent someone from cleaning up and removing it ?

TIA.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.