To Index or Not Index ... That is the question

[FaStOnE]

Need some feed back on a conversion discussion going on here at the Home Office...

I have an open option screen (yea, I know... but, it's just the way it is). The user can pick/chose items that he needs to view. The request pulls data from 2 PF sources. Currently, they are using an OpnQryF to build the selection, then open the file to display.

Tables have about 40k - 50k records

The question is: indexing -OR- SQL based data set returns. If we build LF's across the tables (There are about 4-5 key fields associated to each) then that will push into the CQE and give slower results... No? Then, the question is ... how many to build? Associated access paths included...

My first instinct was to not use indexes (determined on the size of the tables) and build selections in SQL Views or Dynamic SQL as needed BUT (and yes, there's always a big Butt somewhere) what about the linkage between the tables? Should that not be indexed? EVI?

OK, I know most of this is Philosophy... but, just curious on thoughts and reasons why to do one or the other.

Thanks for the feedback!

[MichaelCatalani]

Re: To Index or Not Index ... That is the question

Hey Rick,

What OS release are you guys currently on, and do you have plans to go to 7.1?

[K2r400]

Re: To Index or Not Index ... That is the question

Can you post an example of the SQL statement between both tables ?

[FaStOnE]

Re: To Index or Not Index ... That is the question

We're on 6.1 right now. Yes, eventually (probably 3rd or 4th Quarter) going to 7.1

Patrick, there's really no SQL between the 2 at this point. They're using QRY files to build and process.

The keys for the files are something like:

       PF1
            K    PURCHSEQ
            K    SEQSIZE
            K    PRODRETAIL
            K    PRODCODE
            K    MISCPRODID


       PF2
            K    TYPESEQNUM
            K    SEQINTYPE
            K    PURCHSEQ
            K    SEQSIZE
            K    PRODRETAIL 

Selection could be affected by several "options" the user adds value to ... Size = ?, Code = ?, etc...

[tomholden]

Re: To Index or Not Index ... That is the question

convert that ancient crap to embedded SQL...IIRC OPNQRYF still uses the dog-slow CQE (i'm sure Birgitta will know that answer for sure ) either way SQL is going to outperform the OPNQRYF trash. but...anywho... build the indexes, even if they don't help performance on this process it will for others that use SQL, etc.

[B.Hauser]

Re: To Index or Not Index ... That is the question

... QUERY/400 and OPNQRYF are NOT (not even in Release 7.1) executed by the new SQE.

Try to replace the OPNQRYF with embedded SQL and create at least SQL indexes over the join fields

Birgitta

[K2r400]

Re: To Index or Not Index ... That is the question

I'm agree with tom & birgitta
IMHO, tables with 50K records are very very small tables !!!!
Use embedded dynamic SQL. You can create a view to join both table if you want.

[tomholden]

Re: To Index or Not Index ... That is the question

... QUERY/400 and OPNQRYF are NOT (not even in Release 7.1) executed by the new SQE.

Try to replace the OPNQRYF with embedded SQL and create at least SQL indexes over the join fields

Birgitta

also IIRC neither one will be updated to utilize the SQE (but with IBM that's always subject to change )