non-relational efficiencies
DESCRIPTION
Non-Relational Efficiencies. Walter F. Blood Technical Director Information Builders, Inc. Non-Relational Efficiencies Relational Efficiencies. Why is Relational Efficiency important? Code optimization for the relational engine Translation of JOINS Aggregation and sorting - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/1.jpg)
Copyright 2007, Information Builders. Slide 1
Non-Relational Efficiencies
Walter F. BloodTechnical Director
Information Builders, Inc
![Page 2: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/2.jpg)
Non-Relational EfficienciesRelational Efficiencies
Why is Relational Efficiency important?Code optimization for the relational engine Translation of
JOINSAggregation and sortingRecord selectionVirtual fields
Target – reduce answer set returned to minimum to get fastest returnMaking maximum use of the relational engineLimiting data traffic to a minimum
.
![Page 3: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/3.jpg)
Non-Relational EfficienciesNon-Relational Efficiencies
Non-Relational Efficiency has similar target
Target – get answer set in shortest period of timeMake optimum use of WebFOCUS engine functionality
What is Non-relational?How do you recognize efficiency?
.
![Page 4: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/4.jpg)
Non-Relational EfficienciesNon-Relational Efficiencies
Non-relational databases – FOCUS, flatfiles,…Non SQL
Intermediate files created during the development of a report
Combinations of Non-relational datafiles and relational tables
Creating non-relational output – PDF, EXCEL, comma delimited
.
When Are We Non-Relational?
![Page 5: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/5.jpg)
Efficiency ?
Non-Relational EfficienciesRecognizing Efficiency
Efficiency ?
.
RelativeBaseline
IncrementalVary and Test
MeasureSystem
CPUIOs
WebFOCUSRecordsLines
![Page 6: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/6.jpg)
CPU?
Non-Relational EfficienciesRecognizing Efficiency
From operating system – process based
ps -u username
time programname
![Page 7: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/7.jpg)
CPU?
Non-Relational EfficienciesRecognizing Efficiency
From WebFOCUS – fine tuning control
-SET &TIME1 = &FOCCPU;
WebFOCUS processing -SET &TIME2 = &FOCCPU;-SET &CPUTIME=&TIME2 - &TIME1;
![Page 8: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/8.jpg)
I/O?
Non-Relational EfficienciesRecognizing Efficiency
From operating system – process based-device oriented
iostat
![Page 9: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/9.jpg)
I/O?
Non-Relational EfficienciesRecognizing Efficiency
From WebFOCUS – fine tuning control
-TYPE &READS-TYPE &WRITES-IF &LINES GT 0 THEN GOTO CONTINUE;-IF &RECORDS EQ 0 THEN GOTO EXIT;
![Page 10: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/10.jpg)
ELAPSED TIME ?
Non-Relational EfficienciesRecognizing Efficiency
From operating system – process based
time programname
![Page 11: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/11.jpg)
ELAPSED TIME?
Non-Relational EfficienciesRecognizing Efficiency
From WebFOCUS – fine tuning control
-SET &START = HHMMSS(‘A8’);
DEFINE FILE ABCTIME1/A8 WITH FLD=HHMMSS(TIME1);
![Page 12: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/12.jpg)
Non-Relational EfficienciesWeb-FOCUS Functionality
… in Structures… in Connections … in Expressions… in Selections… in Sorts
Where in Web-FOCUS?
![Page 13: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/13.jpg)
Copyright 2007, Information Builders. Slide 13
Non-Relational Efficiencies…In Structures
Metadata – Master Files - MFDs
Trim out unused fields Re-Describe data
Index, index, index McGyver
![Page 14: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/14.jpg)
Non-Relational Efficiencies…in Structures
Trim out unused fieldsLess parsingSmaller data buffers
-------------------------------1. Remove from master2. Replace with FILLER in MFD
![Page 15: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/15.jpg)
Non-Relational Efficiencies…in Structures
&TEST = 128.9
Re-Describe data----------------------
Identify repetitive fields in master file
![Page 16: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/16.jpg)
Non-Relational Efficiencies…in Structures
&TEST = 128.9
Re-Describe data----------------------
Combine repetitive fields with OCCURS clause
![Page 17: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/17.jpg)
Non-Relational Efficiencies…in Structures
Re-Describe data----------------------
Combine repetitive fields with OCCURS clause
![Page 18: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/18.jpg)
Non-Relational Efficiencies…in Structures
Re-Describe data-----------------------Look for data that has rectype behavior•Characterizing data•Common position
![Page 19: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/19.jpg)
Non-Relational Efficiencies…in Structures
1. For JOINing – add Internal index on HOLD2. External index – for static data3. Multi-dimensional index – REBUILD 4. Create dimension file to act as index - JOIN
Index, index, index
![Page 20: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/20.jpg)
Non-Relational Efficiencies…in Structures
For homework:
FOCALPOINT or techsupport
McGyver
![Page 21: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/21.jpg)
Copyright 2007, Information Builders. Slide 21
Non-Relational Efficiencies…In Connections
Two Modes of Connecting
Nested Loop - JOIN
Sort and Merge - MATCH
![Page 22: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/22.jpg)
Copyright 2007, Information Builders. Slide 22
Non-Relational Efficiencies…In Connections
Nested Loop
A B
C
![Page 23: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/23.jpg)
Copyright 2007, Information Builders. Slide 23
Non-Relational Efficiencies…In Connections
Sort Merge
A B
C
![Page 24: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/24.jpg)
Copyright 2007, Information Builders. Slide 24
Non-Relational Efficiencies…In Connections
Equality based JOINMost direct connectionMultiple fields
Conditional JOIN Less efficientProvides additional functionalityExpression controls connection/efficiency
JOIN Efficiency Considerations
![Page 25: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/25.jpg)
Copyright 2007, Information Builders. Slide 25
Non-Relational Efficiencies…JOINs
![Page 26: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/26.jpg)
Copyright 2007, Information Builders. Slide 26
Non-Relational Efficiencies…JOINs
![Page 27: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/27.jpg)
Copyright 2007, Information Builders. Slide 27
Non-Relational EfficienciesConnect Based on Range of Values
• Connection can also be based upon range - • LE, LT, GE, GT, NE or FROM /TO
EMPLOYEE IDLAST NAMEFIRST NAMEHIRE DATECURRENT SALARY…
EMPLOYEETAX YEARTAX RATEMINIMUM SALARYMAXIMUM SALARY
TAX RATE
JOIN FILE file AT field TO ALL FILE file AT field AS nameWHERE condition
![Page 28: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/28.jpg)
Copyright 2007, Information Builders. Slide 28
Non-Relational EfficienciesConnect Based on Range of Values
![Page 29: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/29.jpg)
Copyright 2007, Information Builders. Slide 29
Non-Relational EfficienciesConnect Based on Expression
Connect files that have no apparent connection
Employee Salary History
New CarFinance
Packages
JOIN?
![Page 30: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/30.jpg)
Copyright 2007, Information Builders. Slide 30
Non-Relational EfficienciesConnect Based on Expression
Employee Salary History
New CarFinance
Packages
JOIN?
![Page 31: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/31.jpg)
Copyright 2007, Information Builders. Slide 31
Non-Relational EfficienciesConnect Based on Expression
Employee Salary History
New CarFinance
Packages
JOIN?
![Page 32: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/32.jpg)
Copyright 2007, Information Builders. Slide 32
OLD-OR-NEW – all records from first and second files. This is the default if the AFTER MATCH line is omitted. (The UNION of the sets.)
OLD-AND-NEW – only records common to both files. (The INTERSECTION of the sets.)
OLD-NOT-NEW – records from the first file with no match in the second file.
MATCH Efficiency – variety of outputs
Non-Relational EfficienciesMATCH Output
![Page 33: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/33.jpg)
Copyright 2007, Information Builders. Slide 33
NEW-NOT-OLD - records from second file with no match in the first file.
OLD-NOR-NEW - non-matching records from both files -records from the first file with no match in the second file, and records from the second file with no match in the first file.
OLD – records from the first file with matching records in the second file.
NEW – records from the second file with matching records in the first file.
Non-Relational EfficienciesMATCH Output
![Page 34: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/34.jpg)
Copyright 2007, Information Builders. Slide 34
Non-Relational EfficienciesConnect Based on Expression
1. What types of files? How large?2. Where are the fields you are selecting on?3. Is the connection equality or conditional ?4. If conditional how complex is the expression ?5. Where are short paths expected?
Host File Xref File
JOIN orMATCH
![Page 35: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/35.jpg)
Copyright 2007, Information Builders. Slide 35
Non-Relational Efficiencies…In Expressions
DATAIN
MATRIX DATAIN
DEFINEIFWHEREBY
COMPUTEIF TOTALWHERE TOTALBY TOTAL
![Page 36: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/36.jpg)
Copyright 2007, Information Builders. Slide 36
Non-Relational Efficiencies…In Expressions
For virtual fields, timing is keyDEFINE
On initial read of data Maximum data volume
COMPUTEOn exit from matrixEqual or smaller volume
Efficiency – evaluate on smaller volume
![Page 37: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/37.jpg)
Copyright 2007, Information Builders. Slide 37
Non-Relational Efficiencies…In Expressions
For expressions, not all subroutines are equalReferencing other data
DECODE functionDBLOOKUP subroutine-READFILE dialogue manager commandJOIN with LOOKUP/FIND
Changing dataCTRAN – 1 characterSTRREP – string of charactersGETTOK/SUBSTR – positional change
Efficiency – check possibilities for optimum
![Page 38: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/38.jpg)
Copyright 2007, Information Builders. Slide 38
Non-Relational Efficiencies…In Expressions
Working with more than one record instance at a time
Writing to multiple files simultaneously
![Page 39: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/39.jpg)
Copyright 2007, Information Builders. Slide 39
Non-Relational Efficiencies…In Expressions
Working with more than one record instance at a time
Available in COMPUTE, DEFINE, WHERE
References the field value in the previous record
Works with real and virtual fields
LASTTHE FUNCTION
![Page 40: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/40.jpg)
Copyright 2007, Information Builders. Slide 40
Non-Relational Efficiencies…In Expressions LAST
In COMPUTE
![Page 41: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/41.jpg)
Copyright 2007, Information Builders. Slide 41
Non-Relational Efficiencies…In Expressions LAST
Same as DEFINE
Operates on matrixProcesses same or
fewer records than DEFINE
In COMPUTE
![Page 42: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/42.jpg)
Copyright 2007, Information Builders. Slide 42
Non-Relational Efficiencies…In Expressions LAST
In WHERE
LAST Considerations: The order the data is processed is critical
You may need to presort data to a hold file
Not optimizable to relational databases
DEFINES work with WHERE
COMPUTES work with WHERE TOTAL
In DEFINE In COMPUTE
![Page 43: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/43.jpg)
Non-Relational Efficiencies…In Expressions
Writing to multiple files simultaneously
To create a log of specific data values readWhich customers placed orders?
To create of record of calculated valuesWill I go out of stock on any these orders?
To create additional outputWhat page number will this item be on?
PUTDDREC
![Page 44: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/44.jpg)
Non-Relational Efficiencies…In Expressions
PUTDDREC syntax: PUTDDREC(ddname, dd_len, record_string, record_len,
outfield)
ddname - ddname assigned by filedef to output filedd_len - length of the ddnamerecord_string - string of characters to write to file or field
containing that stringrecord_len- length of the string to be includedoutfield - return code
Output file must be filedef’dDEFINE/COMPUTE field format always I1
Handles open, write and close
![Page 45: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/45.jpg)
Non-Relational Efficiencies…In Expressions
FILEDEF PUTDD1 DISK putdd1.datTABLE FILE EMPLOYEEPRINT EMP_ID CURR_JOBCODE AS 'JOB' CURR_SALCOMPUTE SALA/A12 = EDIT(CURR_SAL); NOPRINTCOMPUTE EMP1/A50= LAST_NAME|FIRST_NAME|EMP_ID|CURR_JOBCODE|SALA;NOPRINTCOMPUTE OUT1/I1 = PUTDDREC('PUTDD1',6, EMP1, 50, OUT1);BY LAST_NAME BY FIRST_NAMEEND
PUTDDREC in Action
![Page 46: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/46.jpg)
Non-Relational Efficiencies…In Expressions
LAST_NAME FIRST_NAME EMP_ID JOB CURR_SAL OUT1--------- ---------- --------- --- -------- ----BANNING JOHN 119329144 A17 $29,700.00 0BLACKWOOD ROSEMARIE 326179357 B04 $21,780.00 0CROSS BARBARA 818692173 A17 $27,062.00 0GREENSPAN MARY 543729165 A07 $9,000.00 0IRVING JOAN 123764317 A15 $26,862.00 0JONES DIANE 117593129 B03 $18,480.00 0MCCOY JOHN 219984371 B02 $18,480.00 0MCKNIGHT ROGER 451123478 B02 $16,100.00 0ROMANS ANTHONY 126724188 B04 $21,120.00 0SMITH MARY 112847612 B14 $13,200.00 0 RICHARD 119265415 A01 $9,500.00 0STEVENS ALFRED 071382660 A07 $11,000.00 0
Report Created
![Page 47: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/47.jpg)
Non-Relational Efficiencies…In Expressions
BANNING JOHN 19329144A17000000029700BLACKWOOD ROSEMARIE 26179357B04000000021780CROSS BARBARA 18692173A17000000027062GREENSPAN MARY 43729165A07000000009000IRVING JOAN 23764317A15000000026862JONES DIANE 17593129B03000000018480MCCOY JOHN 19984371B02000000018480MCKNIGHT ROGER 51123478B02000000016100ROMANS ANTHONY 26724188B04000000021120SMITH MARY 12847612B14000000013200SMITH RICHARD 19265415A01000000009500STEVENS ALFRED 71382660A07000000011000
Sequential File Created
![Page 48: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/48.jpg)
Non-Relational Efficiencies…In Expressions
PUTDDREC Considerations:
Create fixed data file to fit a specific Master fileCreate comma-delimited data file for loading System and User &variables are available&MDYY, &FOCCPU, &FOCUSER, etc
Write control at any point or multiple pointsWrite to multiple PUTDDREC files in a request
![Page 49: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/49.jpg)
Non-Relational Efficiencies…in Selections
WHERE vs IFWHERE more expensive than IFIF limited to comparison to values – match arrayWHERE allows “expression operator expression”WHERE DecomposedIFs removedWHEREs converted to DEFINE/IF combinationDEFINE expressions converted to polishes
![Page 50: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/50.jpg)
Non-Relational Efficiencies…in Selections
Selection placement Within the request
Before matrixAfter matrix – TOTAL test
Within the dataIndexed or alternative views in TABLEType of comparison
Selection considerations
![Page 51: Non-Relational Efficiencies](https://reader036.vdocuments.us/reader036/viewer/2022062410/56816142550346895dd0b3ce/html5/thumbnails/51.jpg)
Copyright 2007, Information Builders. Slide 51
Non-Relational Efficiencies
Questions?