wf autostrategy occurs where an ordered list is being searched such as a key.

If the keys are ordered ascending 1,2,3,4,5 and we are searching for 3 then we know to stop at 3 as all other values are greater.

This seems to work ok, but if the key is ordered in reverse 5,4,3,2,1 then wf search does not seem to stop at 3 as all other values are lower.

Does ibi'er out there know the answer off the top of their head.

Any ideas appreciated.

Regards

John

John, i don't have an answer, just more questions..
is there an index on that key field?
-s.

My belief* is that this should work for Sn/SHn but not S0.
(* belief = not tested)

The rules for Autostrategy are a bit more complex. When the top segment has a sorted KEY of either S or SH AND the request uses the form TABLEF then Autostrategy is in effect on the top segment (and lower segments).
If the form of request is TABLE then autostrategy is only in effect for children segments (not the top segment), and this is both S and SH.

Hi Everyone

Thank you very much for all your answers.

The particular example I was using is the classic employee has historical information for each day he has worked. This is stored in quite a big segment.

FILE=_TANDA, SUFFIX=XFOC, REMARKS='Time and Attendance Reporting View',$
 
$=======================================================================================================
$ Employee Details
$=======================================================================================================
SEGNAME=EMPBD, SEGTYPE=S1, $
  FIELD=CPA_EMP_NO,  FORMAT=A11, FIELDTYPE=I, MISSING=ON, TITLE='Emp,No', $
$=======================================================================================================
$ Employee Schedule
$=======================================================================================================
SEGNAME=EMP_SCHE, SEGTYPE=SH1, PARENT=EMPBD, $
  FIELD=ESH_DT, FORMAT=DMYY, MISSING=ON, TITLE='Work,Date', DESCRIPTION='Work Date (Continuous for Scheduled Period)', $
$ big segment follows

Orginally EMP_SCHE was a S1 segment, but I changed this to a SH1 when a requirement came about to look at todays data only. eg

TABLE FILE _TANDA
PRINT SEG.CPA_EMP_NO SEG.ESH_DT

WHERE ESH_DT EQ '&DMYY'
END

As Gerry says, since this is not a TOP level segment AUTOSTRATEGY should apply. However swapping the S1 for a SH1 segment with most recent first in the chain did not speed up the retrieval and in the end I had to demote the bulk of the ESH_DT segment to a unique segment to achieve the desired performance.

I will come up with a replicable test scenario and investigate further.

John

John

I've been checking some figures at a site I look after, also with an SH1 date on lower segment. In 2 files I have a DMYY and an A8DMYY.

It appears that autostrategy does not work on date fields. The data for these reports never seem to change with the AUTOSTRATEGY setting.

It does work on A8DMYY in SH order but only properly after a REBUILD, which we do weekly. As the week progresses the figures get worse as the data gets disorganised, ie this weeks dates being added. That may be the same for S1.

I have two comments. First, you can't just change a segtype from S to SH, you also have to rebuild the database.
Second, you can use a date as a key, but it has to be a modern format date, like DMYY. The reason is that all dates are stored in the same format that permits low to high sorting, and the date is just formatted at display time. While A8DMYY, will not allow this type of comparison, as it is not an internal date.

I agree with John here, this is what I tested.

I have 2 FOCUS datafiles.
Both keyed on consignment number, both with a lower level segment of SH1, keyed on date. One A8DMYY and one DMYY. Both started life this way.

With the DMYY field, a run using the highest date, today, uses 15760 &BASEIO with AUTOSTRATEGY ON or OFF, and after a weekly REBUILD, 11980 respectively as some old data is removed during REBUILD.

With the A8DMYY, with AUTOSTRATEGY ON, prior to the weekly REBUILD, running for todays date, 19757 &BASEIO. After REBUILD, the same request uses 4375 &BASIO. With AUTOSTRATEGY OFF uses 28934. (Too late for a run with AUTOSTRATEGY OFF before REBUILD).

As data is added in reverse order, the data gets disorganised, which is why these figures get worse during the week and REBUILD is necessary.

In can only conclude that AUTOSTRATEGY works on A8DMYY, but not DMYY.

It is not possible for autostrategy to work properly on A8DMYY because this can't be sorted in any low to high, or high to low order since it starts with the DAY. e.g is 01122008 greater than 12121995, yes, but not in sort order.
The DMYY format on the other hand is actually an increasing binary number starting from 1899.

Gerry

I agree with what you are saying, the figures seem to tell a different story though. I am waiting for a slack period on the site to run some further tests.

I have done some testing using the following code.

The conclusions are:

1. On a non top Segment autostrategy will only work if the segment is sorted S1 and a single IF or WHERE test is applied against a constant value. All datatypes numeric, alpha and date obey this rule

2. The following DO NOT work

(a) EQ operator on SH1
(b) LE on S1 or GE on SH1 or FROM TO.

3. The following DO NOT having any bearing

(a) Whether it is FOCUS or XFOCUS database
(b) Whether the simple test is a WHERE or an IF.
(c) Whether it is a TABLE or TABLEF.
(d) An alternate/indexed view by definition will not use autostrategy.

4. The following MAY have some effect

(a) The release of WebFOCUS I am using is 762 and I am unable to test it on other releases. It would be appreciated if other users might try the below code on their other releases.

Regards

John

-* File autostrategy.fex

-SET &SEGSIZE = 10 ;
-* SET &TESTVAL = 84000 ;
-* SET &TESTVAL = '''&DATEYYMD.EVAL''' ;
-SET &TESTVAL = '''&DATEDMYY.EVAL''' ;
-SET &TESTMOD = TESTMOD ;
-SET &LOADMAX = 1000 ;
-SET &TESTTAB = TESTTAB ;
-*SET &SEGTYPE = 'S1' ;
-SET &SEGTYPE = 'SH1' ;
-*SET &SUFFIX  = 'FOC' ;
-SET &SUFFIX  = 'XFOC' ;
-*SET &FMT     = 'I6' ;
-*SET &FMT     = 'YYMD' ;
-SET &FMT     = 'DMYY' ;
-*SET &WHEREIF = 'WHERE' ;
-SET &WHEREIF = 'IF' ;
-*SET &TESTREL = 'GT' ;
-*SET &TESTREL = 'LT' ;
-*SET &TESTREL = 'GE' ;
-*SET &TESTREL = 'LE' ;
-SET &TESTREL = 'EQ' ;

FILEDEF MASTER DISK &TESTMOD|.MAS
-RUN
-WRITE MASTER FILE=&TESTMOD.EVAL, SUFFIX=&SUFFIX,$
-WRITE MASTER SEGNAME=ONE, SEGTYPE=S1, $
-WRITE MASTER   FIELD=ONE_KEY,  FORMAT=A11,  MISSING=ON,  $
-WRITE MASTER SEGNAME=TWO, SEGTYPE=&SEGTYPE.EVAL, PARENT=ONE, $
-WRITE MASTER   FIELD=TWO_KEY, FORMAT=&FMT.EVAL,  $
-REPEAT :LOOP1 FOR &I FROM 1 TO &SEGSIZE
-WRITE MASTER   FIELD=TWO_DATA&I.EVAL, FORMAT=A255,  $
-:LOOP1

-RUN
-DOS TYPE &TESTMOD|.MAS

CHECK FILE &TESTMOD

MODIFY FILE &TESTMOD
COMPUTE
  LOADCNT/I5 = 0 ;
  STARTVAL/&FMT = &TESTVAL ;
GOTO C001

CASE C001
FIXFORM ONE_KEY/11
MATCH ONE_KEY
  ON NOMATCH INCLUDE
  ON NOMATCH GOTO C002
  ON MATCH REJECT
ENDCASE

CASE C002
COMPUTE
  TWO_KEY = STARTVAL ;
  TWO_KEY = IF '&SEGTYPE' EQ 'SH1' THEN TWO_KEY  - LOADCNT ELSE TWO_KEY + LOADCNT ;
  LOADCNT = LOADCNT + 1 ;

MATCH TWO_KEY
  ON NOMATCH INCLUDE
  ON NOMATCH IF LOADCNT LT &LOADMAX THEN GOTO C002 ELSE GOTO EXIT ;
  ON MATCH REJECT
ENDCASE

DATA
DAMIENHIRST
END
-RUN
-TYPE MODIFY STATS
? STAT
-RUN
-* now rename so we dont hit the cached bins from the modify
-DOS RENAME &TESTMOD|.MAS &TESTTAB|.MAS
-DOS RENAME &TESTMOD|.FOC &TESTTAB|.FOC

TABLE FILE &TESTTAB
PRINT *
&WHEREIF TWO_KEY &TESTREL &TESTVAL
ON TABLE HOLD
END
-RUN
-TYPE TABLE STATS
? STAT
-RUN

This message has been edited. Last edited by: hammo1j,