Transcript Title of Your Presentation

All New One Liners for FOCUS and
WebFOCUS Efficiencies
Joel Starkman
Director of Operations
June 2008
‘Highway Blues’ from the “Speaking Out” album
Mark Seales, composer; Ernie Watts, saxophone
Copyright 2007, Information Builders. Slide 1
One-Liners For [Web]FOCUS Efficiency
Perform one extra pre-step in your process, or
Replace one type of statement with another, or
Add one line into or before your request, or
Issue just one additional SET statement, or
Increase the appropriate default limit value
Jump to end
Copyright 2007, Information Builders. Slide 2
Prove it - How do I see these savings?
-SET &STARTCPU = &FOCCPU ;
TABLE, etc….
Capture CPU
-RUN
in-stream
-SET &ENDCPU = &FOCCPU ;
-SET &USED=(&ENDCPU - &STARTCPU) /1000;
-TYPE Table used &USED CPU seconds
Examine your
post-execution
operating system
statistics
TABLE, etc….
STATISTICS OF LAST COMMAND
RECORDS
LINES
BASEIO
SORTIO
SORT PAGES
READS
TRANSACTIONS
ACCEPTED
SEGS INPUT
SEGS CHNGD
=
=
=
=
=
=
=
=
=
=
0
0
0
0
0
0
0
0
0
0
INTERNAL MATRIX CREATED: YES
SORT USED:
FOCUS
AGGREGATION BY EXT.SORT: NO
? STAT
SEGS DELTD
NOMATCH
DUPLICATES
FORMAT ERRORS
INVALID CONDTS
OTHER REJECTS
CACHE READS
MERGES
SORT STRINGS
INDEXIO
=
=
=
=
=
=
=
=
=
=
0
0
0
0
0
0
0
0
0
0
AUTOINDEX USED:
AUTOPATH USED:
HOLD FROM EXTERNAL SORT:
NO
NO
NO
Jump to end
Copyright 2007, Information Builders. Slide 3
Use TABLEF when appropriate
“Why is it that when you transport something by car, it's
called a shipment, but
when
you transport
something
by
One
Liners
For FOCUS
Efficiency
ship, it's called cargo?”
F
TABLE
FILE CAR
SUM DCOST
BY COUNTRY
BY CAR
ON CAR SUBFOOT
END
Extract
data
S
Steven Wright
Data already sorted
in the order of the report request?
Use TABLEF !!
Sort and
Merge
data
Generate
report
You can still use COMPUTE,
SUM and WHERE TOTAL
AVINGS: Eliminates large cost of sorting, even if none to do.
Jump to end
Copyright 2007, Information Builders. Slide 4
Use JOIN instead of WHERE IN FILE
"If you live to the age of a hundred you have it made
because very few people
past theFor
age FOCUS
of a hundred.”
OnedieLiners
Efficiency
TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY NE 'ENGLAND'
ON TABLE HOLD AS VALUES FORMAT ALPHA
END
George
Burns
born Nathan Birnbaum
TABLE FILE CAR
SUM SEATS BY COUNTRY
WHERE COUNTRY IN FILE VALUES
END
JOIN COUNTRY IN VALUES TO COUNTRY IN CAR AS J1
? STAT
TABLE FILE VALUES
SUM SEATS BY COUNTRY
END
? STAT
S
Donated by Art Greenhaus
AVINGS: JOIN is generally faster than a file search for each record
Jump to end
Copyright 2007, Information Builders. Slide 5
Do the HOLDs after the END
“Apparently, a new survey says that three out of four
people make One
up 75%
of the population.”
Liners
For FOCUS Efficiency
HOLD’ing the same results
in 3 different formats?
S
TABLE FILE CAR
PRINT COUNTRY CAR
ON TABLE HOLD AS HTML
END
TABLE FILE CAR
PRINT COUNTRY CAR
ON TABLE HOLD AS EXL2K
END
TABLE FILE CAR
PRINT COUNTRY CAR
ON TABLE HOLD AS PDF
END
Re-extracts
entire data set
3 times
Instead:
David Letterman
SET SAVEMATRIX = ON
TABLE FILE CAR
PRINT COUNTRY CAR
ON TABLE HOLD AS HTML
END
HOLD AS EXL2K
HOLD AS PDF
Re-uses internal
matrix from first
to generate others
AVINGS: 50% for 2 HOLD, 66% for 3 HOLDS, etc.
Jump to end
Copyright 2007, Information Builders. Slide 6
Use POOLED TABLES for multiple TABLEs
“Always get married in the morning. That way if it
doesn’t work out, you
haven’t
wasted
whole day.”
One
Liners
Forthe
FOCUS
Efficiency
TABLE FILE HUGE
SUM …
IF REGION EQ ‘NORTH’
SET POOL= ON
END
TABLE FILE HUGE
• No HOLD file needed
SUM …
• Extracts from database once
IF REGION EQ ‘SOUTH’
END
• Feeds all TABLEs at one time
TABLE FILE HUGE
• Output identical to no pooling SUM …
REGION EQ ‘EAST’
• Set ESTLINES/ESTRECORDS IF
END
for even more efficiency
TABLE FILE HUGE
SET POOL= OFF
SUM …
IF REGION EQ ‘WEST’
Note: Pooled Tables is a chargeable feature.
END
SAVINGS: 1/n
Mickey
Rooney
born Joseph Yule, Jr.
Why
extract the
entire
database
four times?
Must SET POOLFEATURE=ON in FOCPARM
th
of I/O’s where n is number of TABLES.
Jump to end
Copyright 2007, Information Builders. Slide 7
Don’t read the entire flat file
"I always wanted to be somebody, but I should have
been
specific."
Onemore
Liners
For FOCUS Efficiency
Allen
Beatty
Bullock
Denver
Kidman
.
.
Zappa
Ten Records
“The flat file is sorted on the
screened field; stop searching
when you see a larger value.”
One Million Records
SET FIXRETRIEVE=ON
TABLE FILE NAMES
PRINT NAME
IF NAME LT ‘E’
END
Lily Tomlin
*Only for
flat files via
SEGTYPE
S
AVINGS: Average 50% search time; could save 1% or 99%.
Jump to end
Copyright 2007, Information Builders. Slide 8
Reformat fields inside the TABLE instead of DEFINE
“Somebody actually complimented me on my driving
today. They left
a little
note on
the FOCUS
windshield.
One
Liners
For
Efficiency
It said 'Parking Fine’.”
DEFINE FILE CAR
Tommy Cooper
-* DCOST is originally D12.2
INTCOST/I6 = DCOST ;
Instead:
END
TABLE FILE CAR
TABLE FILE CAR SUM DCOST
SUM COST
DCOST/I6 AS ‘INTCOST’
INTCOST
BY CAR
Hint: Extra field appears in a HOLD file.
BY CAR
END
To remove, SET HOLDLIST=PRINTONLY
END
No DEFINE needed
S
(but creates extra field in matrix)
AVINGS: Only final displayed field is reformatted.
Jump to end
Copyright 2007, Information Builders. Slide 9
Flip to Alternate File View to elevate screening criteria
“You look at the floor and see the floor. I look at the
floorOne
and see
molecules.”
Liners
For FOCUS Efficiency
N
T
E
MODEL
R
S
BODY
DCOST
Dan Aykroyd
MODEL
TABLE.DCOST
FILE CAR
CAR
PRINT MODEL DCOST
IF DCOST GE 20000
END
COUNTRY
COUNTRY
CAR
DCOST
CAR
I
BODY
MODEL
O
Why follow
pointers all the
way to the bottom,
just to find that a
DCOST does not
fit the criteria?
BODY
COUNTRY
DCOST
P
SAVINGS: Tremendous reductions in I/O and response time.
Jump to end
Copyright 2007, Information Builders. Slide 10
Use Indexed File View to elevate screening criteria
“If dogs could talk, it would take a lot of the fun
out
of owning
One
Linersone.”
For FOCUS Efficiency
N
T
E
MODEL
R
S
BODY
DCOST
Andy Rooney
MODEL
TABLE.DCOST
FILE CAR
CAR
PRINT MODEL DCOST
IF DCOST GE 20000
END
COUNTRY
COUNTRY
CAR
DCOST
CAR
I
BODY
MODEL
O
Same dot-syntax
as alternate view;
uses index
automatically
when present.
BODY
COUNTRY
DCOST
P
SAVINGS: Tremendous reductions in I/O and response time.
Note: Index view requires test on indexed field
Jump to end
Copyright 2007, Information Builders. Slide 11
Let your Sort Utility do all the work –not just the sorting!
“We spend the first twelve months of our children’s
lives teaching them to
walkLiners
and talk,For
and FOCUS
the next twelve
One
Efficiency
telling them to sit down and shut up.”
FOCUS
Sort
Phyllis
Diller
born Phyllis Ada Driver
SET EXTSORT=ON
SET EXTAGGR=ON
SET EXTHOLD=ON
S
System Sort
SYNCSORT
DFSORT
VMSORT
HOLD File
AVINGS: Tremendous reductions in sort time and data movement.
Jump to end
Copyright 2007, Information Builders. Slide 12
Presort the data before loading
“I’m the kind of guy who will have nothing all my life and
then they’ll discover
while digging
my grave.”Efficiency
OneoilLiners
For FOCUS
E
H
F
G
D
Unsorted
Sorted
D
E
F
G
H
George Gobel
A
B
Page 1
C D P a g e 2E F
G
Page 3
H I
J
Page 4
Call system sort
MODIFY FILE …
FIXFORM keyfield
MATCH keyfield
ON NOMATCH UPDATE
DATA ON …
Sort the data file first
SAVINGS: Could be 100’s of times faster on large volumes.
Jump to end
Copyright 2007, Information Builders. Slide 13
REBUILD without the Index first
“Getting on a plane, I told the ticket agent to send one of
my bags to New YorkOne
and the
otherFor
to LA.
She saidEfficiency
she
Liners
FOCUS
can’t do that. I told her she did it last week”.
FILE = …
SEGMENT = …
FIELD=ABC,,I5, FIELDTYPE=I ,$
FIELD=DEF,,A12,$
FIELD=GHI,,D12.2, FIELDTYPE=I ,$
FIELD=JKL,,F5,$
S
1.
2.
3.
4.
Henny Youngman
* (up to 7 indexes)
Remove indexes and save as alternate master
REBUILD REBUILD with no-index master
Put back original master with indexes
REBUILD INDEX
AVINGS: Usually measured in hours of elapsed time savings.
Jump to end
Copyright 2007, Information Builders. Slide 14
JOIN in the faster direction
“I find television very educating. Every time somebody
turns on the set,One
I go into
the other
and Efficiency
Liners
For room
FOCUS
read a book.”
Lorenzo Alcazar
Nicolas Cassadine
Carly Corinthos
Michael Corinthos, III
Sonny Corinthos
Alexis Davis
Noah Drake
Patrick Drake
Jasper "Jax" Jacks
Georgie/Maxie Jones
Ric Lansing
Samantha McCall
Jason Morgan
Dillon Quartermaine
Edward Quartermaine
Tracy Quartermaine
Skye Quartermaine
Emily Quartermaine
Dr. Monica Quartermaine
Malcolm ‘Mac’ Scorpio
Robert Scorpio
Dr. Robin Scorpio
Bobbie Spencer
Elizabeth Spenser
Lucky/Lulu Spencer
Luke Spencer
Elizabeth Webber
President
Vice President Manager A1
Operator
Operator C2 TABLE FILE
Operator
President C2 PRINT NAME
President
Technical
Secretary B3 WHERE BLDG IS ‘C2’
Manager
born Julius Henry Marx
Technical C2
Operator
END
Vice Pres A1
President
Operators
Technical
Operator
Technical
Vice President
Manager
Operator
Secretary
Manager
Technical
Manager
FILE=GH
FILE=JOB
Technical
SEGMENT=GH1, …
SEGMENT=JOB1, …
Technical
Secretary
FIELD=NAME, …
FIELD=POS, …, INDEX=I,$
Secretary
FIELD=POS, …INDEX=I,$
FIELD=BLDG, …
Operators
President
…
…
Technical
????
JOB
Groucho Marx
JOIN POS IN
GH
TO POS IN
JOB
JOIN POS IN
JOB
TO POS IN
GH
SAVINGS: Could reduce processing time by 90% or more.
Jump to end
Copyright 2007, Information Builders. Slide 15
Build your own External Index
when you don’t have write access to the file
“I base most of my fashion taste on what doesn't itch.”
One Liners For FOCUS Efficiency
One Million Records
S
AHP
FGJH
IDSA
JSDQR
LFS
ZWIO
F
J
E
W
Z
E
X
T
E
R
N
A
L
I
N
D
E
X
H
A
A
A
C
D
D
E
F
H
H
H
J
.
.
.
.
U
W
X
Y
Z
Z
>> rebuild
ENTER OPTION
external index
Gilda Radner
NEW OR ADD TO EXISTING?
...
Build your
own index# USE
mastername
extindexname INDEX mastername
END
TABLE FILE filename .indexfield
#Note:
External Index is static - not updated per transaction.
Must Rebuild Index if file changes, say nightly.
AVINGS: Could reduce data accessing time by 90% or more.
Jump to end
Copyright 2007, Information Builders. Slide 16
BY TOTAL – Sort by computed columns in one pass
“I was born with an adult head and a tiny body.
Like aOne
'Peanuts'
character.”
Liners
For FOCUS Efficiency
Sort by Average Sales*
COUNTRY CAR
AVE SALES
SALES
ENGLAND JENSEN
17650
73000
TRIUMPH
24200
122000
JAPAN
HONDA
17480
235000
18250
197300
TABLE FILE TOYOTA
CAR
John Stewart
No HOLD !
SUM SALES AVE.SALES
TABLE FILE CAR
BY COUNTRY BY CAR BY AVE.SALES
SUM SALES
ON TABLE HOLD
BY COUNTRY BY CAR
END
BY TOTAL AVE.SALES
TABLE FILE HOLD
SUM E03 BY COUNTRY BY CAR
END
BY E04 AS ‘Ave Sales’’
END
SAVINGS: Eliminates second pass of the data – could be 50%.
*Invented
numbers
Jump to end
Copyright 2007, Information Builders. Slide 17
Test higher-hit fields first
“I asked a fellow at hang-glider school 'How many
successful jumps do
you need
before
graduate?'
One
Liners
Foryou
FOCUS
Efficiency
He said, 'All of them.’ ”
DEFINE FILE ...
EXCHANGE/A3 = EDIT(PHONE,'$$$999$$$$');
FLAG/A1 = IF (SALARY + BENEFITS GT 50000)
Red Skelton
AND (EXCHANGE EQ '736‘) THEN 'Y' ELSE 'N';
Do real fields first,
END
Calculated first
or those most likely
for every record TABLE FILE ...- and often false
TABLE FILE ...to eliminate records
PRINT ...
PRINT ...
WHERE FLAG EQ 'Y‘
IF LASTNAME EQ ‘$M$*'
IF EXCHANGE EQ '736‘
IF EXCHANGE EQ '736'
IF LASTNAME EQ ‘$M$*'
WHERE FLAG EQ 'Y‘
END
END
Complicated calc
Technique applies to fields on the same level.
Higher level screens are always done first,
regardless of the order coded.
done only when all
other criteria pass
SAVINGS: Potentially significant reduction in calculation time.
Jump to end
Copyright 2007, Information Builders. Slide 18
Force use of the higher-hit index
“It’s money. I remember it from when I was single.”
One Liners For FOCUS Efficiency
FILE=CAR,…
CAR Master
SEGMENT=…,SEGTYPE=S1,$
FIELD=VALUE1,,A5,$
Billy Crystal
FIELD=VALUE2,,A9,FIELDTYPE=I,$
FIELD=VALUE3,,A2,FIELDTYPE=I,$
FOCUS automatically
FIELD=VALUE4,,A1,FIELDTYPE=I,$
uses the first index,
regardless of how the
FIELD=VALUE5,,A6,$
screens are arranged in
. . .
the TABLE
TABLE FILE CAR.VALUE3
PRINT ...
Force FOCUS to use the
WHERE VALUE3 LT '736‘
most valuable Index.
Only you know which
WHERE VALUE4 EQ ‘ABC‘
one that is!
WHERE VALUE2 EQ 'Y‘
END
SAVINGS: Significant reduction in data access time.
Donated by Noreen Redden
Jump to end
Copyright 2007, Information Builders. Slide 19
Build test limit right into the Master
“Curious people are interesting people.
I wonder
why thatFor
is.“ FOCUS
One Liners
Efficiency
FILE=CAR,…
CAR Master
SEGMENT=…,SEGTYPE=S1,$
FIELD=FIELD1,,A5,$
Bill Maher
FIELD=FIELD2,,A9,FIELDTYPE=I,$
. . .
Put the
DBA=JOEL,$
RECORDLIMIT
USER=’ ‘,ACCESS=R,$
in the DBA
USER=TEST,ACCESS=R,RESTRICT=VALUE,
VALUE=RECORDLIMIT EQ 5,$
SET USER=TEST
TABLE FILE CAR
PRINT ...
WHERE FIELD1 LT '736‘
END
Activate the limited
record extraction for test
SAVINGS: No code to add, switch or comment out to do a test run.
Donated by Noreen Redden
Jump to end
Copyright 2007, Information Builders. Slide 20
Duplicate the same field in the MFD for multiple purposes
(Interface-only technique)
“The prime minister held a meeting with the cabinet
today. He also spoke
the bookcase
and arguedEfficiency
Oneto Liners
For FOCUS
with the chest of drawers.”
FILE=CAR,SUFFIX=MSSQL
CAR Master
SEGMENT= . . .
FIELD=. . .,$
FIELD=Order_Date, ORDERDATA, MDYY, DATE,$
FIELD=Order_Month,ORDERDATA, YYM, DATE,$
Want to screen on date,
but sort/sum by month
Ronnie Barker
Duplicate ALIAS,
different formats
TABLE . . .
WHERE Order_Date FROM ‘01012000’
BY Order_Month
. . .
SAVINGS: Saves a DEFINE for sure, and maybe other coding.
Donated by Noreen Redden
Jump to end
Copyright 2007, Information Builders. Slide 21
Do string manipulation inside the TABLE when possible
“I only know two pieces. One is 'Clair de Lune'
and the
other
one isn't.”
One
Liners
For FOCUS Efficiency
$ means any one character
$* means all remaining chars
Victor Borge
DEFINE FILE ...
LASTNAME2/A1 = EDIT(LASTNAME,’$9’);
END
TABLE FILE ...
TABLE FILE ...
PRINT ...
PRINT ...
IF LASTNAME2 EQ ‘M'
IF LASTNAME EQ ‘$M$*'
IF EXCHANGE EQ '736'
IF EXCHANGE EQ '736'
IF FLAG EQ 'Y‘
IF FLAG EQ 'Y‘
END
END
SAVINGS: In-TABLE string masking is faster than DEFINE.
Jump to end
Copyright 2007, Information Builders. Slide 22
Avoid reparsing Masters over and over again
“It’s no longer a question of staying healthy. It’s a
question of finding
a sickness
you
like.”
One Liners
For
FOCUS
Efficiency
Store up to 99 parsed masters in memory,
so no reparsing
SET SAVEDMASTERS = 99
TABLE FILE CAR
PRINT …
END
TABLE FILE CAR …
TABLE FILE EMPLOYEE …
TABLE FILE CAR …
TABLE FILE SALES …
TABLE FILE EMPLOYEE …
Jackie
Mason
born Jacob Maza
Never reparses on
consecutive use
Without the SET, would:
-- dump CAR master,
-- parse EMPLOYEE,
-- reparse CAR again
-- parse SALES ….
SAVINGS: Large savings when constantly flipping Masters.
Jump to end
Copyright 2007, Information Builders. Slide 23
Split a field into pieces for instantaneous searches
“A boy can learn a lot from a dog: obedience, loyalty,
and the importance
turning around
three times
OneofLiners
For FOCUS
Efficiency
before lying down.”
SSN
123-45-6789
SSN
QUESTION
SSN1TO3
How many pages
would I have to
search to find the
last of 999,999,999
SSN combinations?
SSN4TO5
DATA
USE FOR
Social Security Numbers
Phone Numbers
Credit Card Numbers
Hottrack Numbers (we do!)
Robert Benchley
I could index SSN,
but an index won’t
help on partial
searches
SSN6TO9
Store full SSN here too
DATA
SAVINGS: Rock-bottom I/O when searching for that ‘field’.
Jump to end
Copyright 2007, Information Builders. Slide 24
Index your HOLD file
“I’m this century’s Dennis The Menace.”
One Liners For FOCUS Efficiency
HOLD extracts might be used more
efficiently if you could index important
search fields for subsequent reports.
Bart Simpson
TABLE FILE …
Sequential file
FOCUS database
PRINT CITY TITLE
sorted by
indexed on
EMPL_NO
BY EMPL_NO
CITY
ON TABLE HOLD FORMAT FOCUS INDEX CITY
END
TABLE FILE HOLD
COUNT EMPL_NO BY TITLE IF CITY EQ ‘ORLANDO’
END
born Bartholomew Jo-Jo Simpson
SAVINGS: Slower to build extract, but much faster post-retrieval.
Jump to end
Copyright 2007, Information Builders. Slide 25
Build an alternate master with large alphas
when you need only a few fields out of hundreds
“When you go into court you are putting your fate into
the hands of twelve One
peopleLiners
who weren’t
smart enough
For FOCUS
Efficiency
to get out of jury duty.”
FILE=LOTSOF
SEGMENT=ONE
FIELD=ABC,,A10
FIELD=DEF,,I9
FIELD=GHI,,F4.1
FIELD=JKL,,D12.2
FIELD=MNO,,A5
SEGMENT=TWO
FIELD=PQR,,I9
FIELD=STU,,F4.1
FIELD=VWX,,D12.2
FIELD=YZA,,A5
FIELD=PQR,,I9
FIELD=STU,,F4.1
FIELD=VWX,,D12.2
FIELD=YZA,,A5
FIELD=PQR,,I9
FIELD=STU,,F4.1
FIELD=VWX,,D12.2
FIELD=YZA,,A5
FILE=LOTSOF
SEGMENT=ONE
FIELD=ABC,,A10
FIELD=DEF,,I9
Norm Crosby
FIELD=LAST3,,A17
!
Make sure you
count the bytes
properly
SEGMENT=TWO
FIELD=ALLONE,,A65
.
.
.
TABLE FILE
SUM ABC BY DEF
.
.
END
.
AVINGS: Reduce reparsing time when non-seq but iterative usage.
S
Jump to end
Copyright 2007, Information Builders. Slide 26
Pre-LOAD your application into memory
“If your parents never had children, chances are
you
won't
either.“For FOCUS Efficiency
One
Liners
Dick Cavett
LOAD FOCEXEC fex_name1 fex_name2 …
LOAD FOCCOMP compiled_modify_name
EX …
RUN …
LOAD Masters and
Access Files too
Uses the image now in memory
? LOAD [filetype]
UNLOAD [* | filetype] [* | filename1 [filename2 …] ]
SAVINGS: Instant startup of large components. (No execution value.)
Jump to end
Copyright 2007, Information Builders. Slide 27
Rob’s Rules of Coding for Performance
“The most exciting phrase to hear in science, the one
that heralds new discoveries,
is not
butEfficiency
One Liners
For'Eureka!'
FOCUS
'That's funny...‘ ”
Isaac Asimov
Code for execution performance
Generally, fewer lines of code means faster performance
Code for programmer performance
Don't belabor over tweaking code for one-time applications,
nor spend hours speeding up something that is already quick
Code for maintenance
Don't sacrifice readability and maintainability. Remember that
you or someone will have to maintain your code
S
Donated by Rob Freeman
AVINGS: Live by the rules to save time for everyone
Jump to end
Copyright 2007, Information Builders. Slide 28
One-Liners for [Web]FOCUS Efficiency
"I'd rather be a could-be if I cannot be an are;
because a could-be is a maybe who is reaching for a star.
I'd rather be a has-been
than
a might-have-been,
by far;
One
Liners
For FOCUS
Efficiency
for a might-have-been has never been, but a has was
once an are."
Milton Berle
born Milton Berlinger
I leave you
with this gem
Jump to end
Copyright 2007, Information Builders. Slide 29
Presentation Information
Author: Joel Starkman
Company: Information Builders
Presentation Title: New One-Liners for [Web]FOCUS Efficiency
Presentation Abstract: Does your FOCUS job take hours to run,
or absorb inordinate amounts of system resources, or barely
fit into your nightly production window? This session presents
over three dozen techniques, each virtually a minor one-line
change to your focexec, that target hidden inefficiencies in
your code. With a little help from history's greatest one-liner
comedians, if even one tip helps you gain tremendous
savings, it's worth attending this session.
Jump to end
Copyright 2007, Information Builders. Slide 30