Non relational efficiencies
This presentation is the property of its rightful owner.
Sponsored Links
1 / 51

Non-Relational Efficiencies PowerPoint PPT Presentation


  • 132 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Non-Relational Efficiencies

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Non relational efficiencies

Non-Relational Efficiencies

Walter F. Blood

Technical Director

Information Builders, Inc


Non relational efficiencies relational efficiencies

Non-Relational EfficienciesRelational Efficiencies

  • Why is Relational Efficiency important?

    • Code optimization for the relational engine

    • Translation of

      • JOINS

      • Aggregation and sorting

      • Record selection

      • Virtual fields

    • Target – reduce answer set returned to minimum to get fastest return

      • Making maximum use of the relational engine

      • Limiting data traffic to a minimum

        .


Non relational efficiencies non relational efficiencies

Non-Relational EfficienciesNon-Relational Efficiencies

  • Non-Relational Efficiency has similar target

    • Target – get answer set in shortest period of time

    • Make optimum use of WebFOCUS engine functionality

  • What is Non-relational?

  • How do you recognize efficiency?

    .


Non relational efficiencies non relational efficiencies1

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?


Non relational efficiencies recognizing efficiency

Non-Relational EfficienciesRecognizing Efficiency

Efficiency ?

.

Efficiency ?

  • Relative

    • Baseline

  • Incremental

    • Vary and Test

  • Measure

    • System

      • CPU

      • IOs

    • WebFOCUS

      • Records

      • Lines


Non relational efficiencies recognizing efficiency1

Non-Relational EfficienciesRecognizing Efficiency

CPU?

From operating system

– process based

ps -u username

time programname


Non relational efficiencies recognizing efficiency2

Non-Relational EfficienciesRecognizing Efficiency

CPU?

  • From WebFOCUS

  • – fine tuning control

-SET &TIME1 = &FOCCPU;

WebFOCUSprocessing

-SET &TIME2 = &FOCCPU;

-SET &CPUTIME=&TIME2 - &TIME1;


Non relational efficiencies recognizing efficiency3

Non-Relational EfficienciesRecognizing Efficiency

I/O?

From operating system

– process based

-device oriented

iostat


Non relational efficiencies recognizing efficiency4

Non-Relational EfficienciesRecognizing Efficiency

I/O?

  • From WebFOCUS

  • – fine tuning control

-TYPE &READS

-TYPE &WRITES

-IF &LINES GT 0 THEN GOTO CONTINUE;

-IF &RECORDS EQ 0 THEN GOTO EXIT;


Non relational efficiencies recognizing efficiency5

Non-Relational EfficienciesRecognizing Efficiency

ELAPSED TIME ?

From operating system

– process based

time programname


Non relational efficiencies recognizing efficiency6

Non-Relational EfficienciesRecognizing Efficiency

ELAPSED TIME?

  • From WebFOCUS

  • – fine tuning control

  • SET &START = HHMMSS(‘A8’);

  • DEFINE FILE ABC

  • TIME1/A8 WITH FLD=HHMMSS(TIME1);


Non relational efficiencies web focus functionality

Non-Relational EfficienciesWeb-FOCUS Functionality

  • … in Structures

  • … in Connections

  • … in Expressions

  • … in Selections

  • … in Sorts

Where in Web-FOCUS?


Non relational efficiencies in structures

Non-Relational Efficiencies…In Structures

Metadata – Master Files - MFDs

Trim out unused fields

Re-Describe data

Index, index, index

McGyver


Non relational efficiencies in structures1

Non-Relational Efficiencies…in Structures

Trim out unused fields

Less parsing

Smaller data buffers

-------------------------------

1. Remove from master

2. Replace with FILLER in MFD


Non relational efficiencies in structures2

Non-Relational Efficiencies…in Structures

Re-Describe data

----------------------

Identify repetitive fields in

master file

&TEST = 128.9


Non relational efficiencies in structures3

Non-Relational Efficiencies…in Structures

Re-Describe data

----------------------

Combine repetitive fields with

OCCURS clause

&TEST = 128.9

&TEST = 128.9


Non relational efficiencies in structures4

Non-Relational Efficiencies…in Structures

Re-Describe data

----------------------

Combine repetitive fields with

OCCURS clause


Non relational efficiencies in structures5

Non-Relational Efficiencies…in Structures

Re-Describe data

-----------------------

Look for data that has

rectype behavior

  • Characterizing data

  • Common position


Non relational efficiencies in structures6

Non-Relational Efficiencies…in Structures

ForJOINing – add Internal index on HOLD

External index – for static data

Multi-dimensional index – REBUILD

Create dimension file to act as index - JOIN

Index, index, index


Non relational efficiencies in structures7

Non-Relational Efficiencies…in Structures

For homework:

FOCALPOINT or techsupport

McGyver


Non relational efficiencies in connections

Non-Relational Efficiencies…In Connections

Two Modes of Connecting

Nested Loop - JOIN

Sort and Merge - MATCH


Non relational efficiencies in connections1

Non-Relational Efficiencies…In Connections

Nested Loop

A

B

C


Non relational efficiencies in connections2

Non-Relational Efficiencies…In Connections

Sort Merge

B

A

C


Non relational efficiencies in connections3

Non-Relational Efficiencies…In Connections

JOIN Efficiency Considerations

  • Equality based JOIN

    • Most direct connection

    • Multiple fields

  • Conditional JOIN

    • Less efficient

    • Provides additional functionality

    • Expression controls connection/efficiency


Non relational efficiencies joins

Non-Relational Efficiencies…JOINs


Non relational efficiencies joins1

Non-Relational Efficiencies…JOINs


Non relational efficiencies connect based on range of values

Non-Relational EfficienciesConnect Based on Range of Values

EMPLOYEE

TAX RATE

EMPLOYEE ID

LAST NAME

FIRST NAME

HIRE DATE

CURRENT SALARY

TAX YEAR

TAX RATE

MINIMUM SALARY

MAXIMUM SALARY

  • Connection can also be based upon range -

    • LE, LT, GE, GT, NE or FROM /TO

JOIN FILE file AT field TO ALL FILE file AT field AS name

WHERE condition


Non relational efficiencies connect based on range of values1

Non-Relational EfficienciesConnect Based on Range of Values


Non relational efficiencies connect based on expression

Non-Relational EfficienciesConnect Based on Expression

  • Connect files that have no apparent connection

Employee

Salary

History

New Car

Finance

Packages

JOIN?


Non relational efficiencies connect based on expression1

Non-Relational EfficienciesConnect Based on Expression

Employee

Salary

History

New Car

Finance

Packages

JOIN?


Non relational efficiencies connect based on expression2

Non-Relational EfficienciesConnect Based on Expression

Employee

Salary

History

New Car

Finance

Packages

JOIN?


Non relational efficiencies

Non-Relational EfficienciesMATCH Output

MATCH Efficiency – variety of outputs

  • 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.


Non relational efficiencies

Non-Relational EfficienciesMATCH Output

  • 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 efficiencies connect based on expression3

Non-Relational EfficienciesConnect Based on Expression

Xref File

Host File

JOIN or

MATCH

What types of files? How large?

Where are the fields you are selecting on?

Is the connection equality or conditional ?

If conditional how complex is the expression ?

Where are short paths expected?


Non relational efficiencies in expressions

Non-Relational Efficiencies…In Expressions

DATA

IN

MATRIX

DATA

IN

DEFINE

IF

WHERE

BY

COMPUTE

IF TOTAL

WHERE TOTAL

BY TOTAL


Non relational efficiencies in expressions1

Non-Relational Efficiencies…In Expressions

  • For virtual fields, timing is key

    • DEFINE

      • On initial read of data

      • Maximum data volume

    • COMPUTE

      • On exit from matrix

      • Equal or smaller volume

    • Efficiency – evaluate on smaller volume


Non relational efficiencies in expressions2

Non-Relational Efficiencies…In Expressions

  • For expressions, not all subroutines are equal

    • Referencing other data

      • DECODE function

      • DBLOOKUP subroutine

      • -READFILE dialogue manager command

      • JOIN with LOOKUP/FIND

    • Changing data

      • CTRAN – 1 character

      • STRREP – string of characters

      • GETTOK/SUBSTR – positional change

    • Efficiency – check possibilities for optimum


Non relational efficiencies in expressions3

Non-Relational Efficiencies…In Expressions

  • Working with more than one record instance at a time

  • Writing to multiple files simultaneously


Non relational efficiencies in expressions4

Non-Relational Efficiencies…In Expressions

  • Working with more than one record instance at a time

LAST

THE FUNCTION

  • Available in COMPUTE, DEFINE, WHERE

  • References the field value in the previous record

  • Works with real and virtual fields


Non relational efficiencies in expressions5

Non-Relational Efficiencies…In Expressions

LAST

In COMPUTE


Non relational efficiencies in expressions6

Non-Relational Efficiencies…In Expressions

LAST

  • Same as DEFINE

  • Operates on matrix

  • Processes same or fewer records than DEFINE

In COMPUTE


Non relational efficiencies in expressions7

Non-Relational Efficiencies…In Expressions

LAST

  • 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

In WHERE


Non relational efficiencies in expressions8

Non-Relational Efficiencies…In Expressions

Writing to multiple files simultaneously

  • To create a log of specific data values read

    Which customers placed orders?

  • To create of record of calculated values

    Will I go out of stock on any these orders?

  • To create additional output

    What page number will this item be on?

PUTDDREC


Non relational efficiencies in expressions9

Non-Relational Efficiencies…In Expressions

PUTDDREC syntax:

PUTDDREC(ddname, dd_len, record_string, record_len,

outfield)

ddname - ddname assigned by filedef to output file

dd_len- length of the ddname

record_string-string of characters to write to file or field containing that string

record_len- length of the string to be included

outfield - return code

Output file must be filedef’d

DEFINE/COMPUTE field format always I1

Handles open, write and close


Non relational efficiencies in expressions10

Non-Relational Efficiencies…In Expressions

PUTDDREC in Action

FILEDEF PUTDD1 DISK putdd1.dat

TABLE FILE EMPLOYEE

PRINT EMP_ID CURR_JOBCODE AS 'JOB' CURR_SAL

COMPUTE SALA/A12 = EDIT(CURR_SAL); NOPRINT

COMPUTE EMP1/A50= LAST_NAME|FIRST_NAME|EMP_ID|CURR_JOBCODE|SALA;

NOPRINT

COMPUTE OUT1/I1 = PUTDDREC('PUTDD1',6, EMP1, 50, OUT1);

BY LAST_NAME BY FIRST_NAME

END


Non relational efficiencies in expressions11

Non-Relational Efficiencies…In Expressions

LAST_NAME FIRST_NAME EMP_ID JOB CURR_SAL OUT1

--------- ---------- --------- --- -------- ----

BANNING JOHN 119329144 A17 $29,700.00 0

BLACKWOOD ROSEMARIE 326179357 B04 $21,780.00 0

CROSS BARBARA 818692173 A17 $27,062.00 0

GREENSPAN MARY 543729165 A07 $9,000.00 0

IRVING JOAN 123764317 A15 $26,862.00 0

JONES DIANE 117593129 B03 $18,480.00 0

MCCOY JOHN 219984371 B02 $18,480.00 0

MCKNIGHT ROGER 451123478 B02 $16,100.00 0

ROMANS ANTHONY 126724188 B04 $21,120.00 0

SMITH MARY 112847612 B14 $13,200.00 0

RICHARD 119265415 A01 $9,500.00 0

STEVENS ALFRED 071382660 A07 $11,000.00 0

Report Created


Non relational efficiencies in expressions12

Non-Relational Efficiencies…In Expressions

Sequential File Created

BANNING JOHN 19329144A17000000029700

BLACKWOOD ROSEMARIE 26179357B04000000021780

CROSS BARBARA 18692173A17000000027062

GREENSPAN MARY 43729165A07000000009000

IRVING JOAN 23764317A15000000026862

JONES DIANE 17593129B03000000018480

MCCOY JOHN 19984371B02000000018480

MCKNIGHT ROGER 51123478B02000000016100

ROMANS ANTHONY 26724188B04000000021120

SMITH MARY 12847612B14000000013200

SMITH RICHARD 19265415A01000000009500

STEVENS ALFRED 71382660A07000000011000


Non relational efficiencies in expressions13

Non-Relational Efficiencies…In Expressions

PUTDDREC Considerations:

  • Create fixed data file to fit a specific Master file

  • Create comma-delimited data file for loading

  • System and User &variables are available

    • &MDYY, &FOCCPU, &FOCUSER, etc

  • Write control at any point or multiple points

  • Write to multiple PUTDDREC files in a request


Non relational efficiencies in selections

Non-Relational Efficiencies…in Selections

WHERE vs IF

  • WHERE more expensive than IF

  • IF limited to comparison to values – match array

  • WHERE allows “expression operator expression”

  • WHERE Decomposed

    • IFs removed

    • WHEREs converted to DEFINE/IF combination

    • DEFINE expressions converted to polishes


Non relational efficiencies in selections1

Non-Relational Efficiencies…in Selections

Selection considerations

  • Selection placement

    • Within the request

      • Before matrix

      • After matrix – TOTAL test

    • Within the data

  • Indexed or alternative views in TABLE

  • Type of comparison


Non relational efficiencies1

Non-Relational Efficiencies

Questions?


  • Login