SQL record selection performance

[Denny]

I have a file with 60 million records.

System is V5R3.

Which is better.

1. Create a keyed logical file that excludes 30 million records.

2. Create a keyed join logical that excludes 45 million records.

3. Create a SQL index over everything and do where + join to get the correct records.

Key/index wold be store number/date. Processing will be to read all records for store x beginning with y date. There are 275 stores. Dates will be random, but generally in the range of today minus 30 to 180 days.

A. Write in in SQL RPGIV
B. Write in good old setll and chain.

[Mercury]

Re: SQL record selection performance

I think that I'd do

• Create a SQL index over everything.
• Write in good old setll and chain.

[arrow483]

Re: SQL record selection performance

Depends what you want to do with it. If its SQL queries, then I would consider 2 EVI indexes, one each for store and date. A lot of other considerations Any "group by" or "order by" requirements ? Or will you need to process by group or by record ? How many records would you expect to be returned ? If you need to process record by record, the SETLL option might be better.

[Denny]

Re: SQL record selection performance

At first guess, I will be summarizing 5,000 or so records per store. I don't really need to see the detail record. That means using SQL group by ...

Since I am going after such a small subset of records, I'm leaning toward SQL running over a logical. I know this means using to "old" SQL engine but with the correct join logical it should read nothing but good records.

This is a weekly batch job that will run on Sunday when the stores are closed. I could make the access path delayed since this will be the only job using it.

[B.Hauser]

Re: SQL record selection performance

Hi,

you should use the following:
3. Create SQL indexes over the join fields and the fields that are used witin the where conditions. In your case it may even be better to create an index with Date/Number (instead of Number/Date).
4. Create SQL view that joins the tables and the where conditions that are fix. ... and where even the group bys can be specified. (A view is an unkeyed logical file where everything that is possible in a SELECT-statement can be used except ORDER BY. Because a view has no key there is no performance deficit when inserting, updating or deleting rows.

Since I am going after such a small subset of records, I'm leaning toward SQL running over a logical. I know this means using to "old" SQL engine but with the correct join logical it should read nothing but good records.

When specifying a joined logical file, the old query engine may use it directly (if this join file is really complex, i.e. a lot of joins and complex select/omit clauses), but normally the query will be rewritten by the query optimizer based on the underlying phyiscal tables. In this way you may get only "good" records, but there is an additional overhead, when rerouting to the CQE and analzying the DDS described logial files and rewriting the query.

With an SQL view you also only read "good" records. SQL also needs access paths (keyed logical files or SQL Indexes) to get fast access to the data. SQL indexes may be better, because of their default page size of 64K while keyed logical files only have a default page size of 8K.

SQL is even smart enough (at least the SQL Query Engine SQE) when using a joined view where only information from a single file are used to only use this table (without joining to the other one).

In either way, try to execute your query with iSeries Navigator's Visual explain and Check which indexes are adviced.

Birgitta

[Denny]

Re: SQL record selection performance

Birgitta

Thanks for the great advise.

I built a join view that did a group by store,date,product. Processed it with SQLRPGLE that selected by date and did group by store,product.

Ran test with visual explain and created suggested index. It took nearly 20 minutes to build the index.

I am amazed at the final result. Running in a test partition my first execution was 29 minutes. This partition has .2 processing power. The production partition runs at 1.6.

[kitvb1]

Re: SQL record selection performance

In addition to what Biggie has written, to make it even faster, you could do a multiple row fetch into an array DS. Then process the array. This depends on the size of your fetched rows as SQL always tries to fetch 32k blocks. So the shorter your fetched row, the more rows you can fetch, obviously also within array limitations. The max is 32767 rows in one fetch statement. Using an MR fetch does not affect the way you define your cursor. You could also check how many rows and the last row fetched from the diagnostics area.

Just a note on the code below. The SFLSIZE is 500 and contains only library names i.e. 500 lines of 6 colums = 30k = just under 1 block.

exec sql  
 DECLARE XLibCsr cursor for   
   Select * from XREF02     
   order by whlib;

Here's the fetch, from the diagnostics area you can also check the nr of rows and the last row fetched. In this case, I didn't need to.

  exec sql                                
    fetch next from XLibCsr               
    for :dArrSize rows into :LbArrScn;    
  clear x;                                
  SflRowNr = 1;                           
 
// Now build the SFL from the array       
// build the row                          
  For x = SflRowNr to dArrSize;           
    clear DspFld;     
 
// build the column                           
    LNamArr = %subarr(LbArrScn :SflRowNr :6);
...

p.s. the pic is from the XRefIT manual. XRefIT v1.21 will be released with ChangefIT v2.25 next month and is bundled free with ChangefIT.

[B.Hauser]

Re: SQL record selection performance

... great Kit! You learned something in my workshop

Just a note: %SUBARR() and GET DIAGNOSTICS will not work before release V5R3.

Would you like to write an article on the concept for the PowerInfo - NewsLetter (free Newsletter iNN - PowerInfo 7/2009)

Your last one was great.
You'll find Kit's article under Tekki Corner under the link above.

Birgitta

[kitvb1]

Re: SQL record selection performance

Correction... that should be "workshops". See... I wasn't sleeping, just thinking with my eyes closed

Sure, no problem.

[kitvb1]

Re: SQL record selection performance

...

...
 DECLARE XLibCsr cursor for   
   Select * from XREF02     
   order by whlib;

Just before Biggie moans at me
I use "select *..." in the above statement as the there is only 1 field (whlib) in the file. If you have many fields, it is better & faster to use "select myfield1, myfield2...", not the asterisk.

btw... Biggie is the German shortened name for Birgitta**. While not common, it is not unusual.

**(sorta like Jack = John in the USA... huh?