1 / 51

Non-Relational Efficiencies

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

kass
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Non-Relational Efficiencies Walter F. Blood Technical Director Information Builders, Inc

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

  3. 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? .

  4. 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?

  5. Non-Relational EfficienciesRecognizing Efficiency Efficiency ? . Efficiency ? • Relative • Baseline • Incremental • Vary and Test • Measure • System • CPU • IOs • WebFOCUS • Records • Lines

  6. Non-Relational EfficienciesRecognizing Efficiency CPU? From operating system – process based ps -u username time programname

  7. Non-Relational EfficienciesRecognizing Efficiency CPU? • From WebFOCUS • – fine tuning control -SET &TIME1 = &FOCCPU; WebFOCUSprocessing -SET &TIME2 = &FOCCPU; -SET &CPUTIME=&TIME2 - &TIME1;

  8. Non-Relational EfficienciesRecognizing Efficiency I/O? From operating system – process based -device oriented iostat

  9. 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;

  10. Non-Relational EfficienciesRecognizing Efficiency ELAPSED TIME ? From operating system – process based time programname

  11. Non-Relational EfficienciesRecognizing Efficiency ELAPSED TIME? • From WebFOCUS • – fine tuning control • SET &START = HHMMSS(‘A8’); • DEFINE FILE ABC • TIME1/A8 WITH FLD=HHMMSS(TIME1);

  12. Non-Relational EfficienciesWeb-FOCUS Functionality • … in Structures • … in Connections • … in Expressions • … in Selections • … in Sorts Where in Web-FOCUS?

  13. Non-Relational Efficiencies…In Structures Metadata – Master Files - MFDs Trim out unused fields Re-Describe data Index, index, index McGyver

  14. Non-Relational Efficiencies…in Structures Trim out unused fields Less parsing Smaller data buffers ------------------------------- 1. Remove from master 2. Replace with FILLER in MFD

  15. Non-Relational Efficiencies…in Structures Re-Describe data ---------------------- Identify repetitive fields in master file &TEST = 128.9

  16. Non-Relational Efficiencies…in Structures Re-Describe data ---------------------- Combine repetitive fields with OCCURS clause &TEST = 128.9 &TEST = 128.9

  17. Non-Relational Efficiencies…in Structures Re-Describe data ---------------------- Combine repetitive fields with OCCURS clause

  18. Non-Relational Efficiencies…in Structures Re-Describe data ----------------------- Look for data that has rectype behavior • Characterizing data • Common position

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

  20. Non-Relational Efficiencies…in Structures For homework: FOCALPOINT or techsupport McGyver

  21. Non-Relational Efficiencies…In Connections Two Modes of Connecting Nested Loop - JOIN Sort and Merge - MATCH

  22. Non-Relational Efficiencies…In Connections Nested Loop A B C

  23. Non-Relational Efficiencies…In Connections Sort Merge B A C

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

  25. Non-Relational Efficiencies…JOINs

  26. Non-Relational Efficiencies…JOINs

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

  28. Non-Relational EfficienciesConnect Based on Range of Values

  29. Non-Relational EfficienciesConnect Based on Expression • Connect files that have no apparent connection Employee Salary History New Car Finance Packages JOIN?

  30. Non-Relational EfficienciesConnect Based on Expression Employee Salary History New Car Finance Packages JOIN?

  31. Non-Relational EfficienciesConnect Based on Expression Employee Salary History New Car Finance Packages JOIN?

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

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

  34. 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?

  35. Non-Relational Efficiencies…In Expressions DATA IN MATRIX DATA IN DEFINE IF WHERE BY COMPUTE IF TOTAL WHERE TOTAL BY TOTAL

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

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

  38. Non-Relational Efficiencies…In Expressions • Working with more than one record instance at a time • Writing to multiple files simultaneously

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

  40. Non-Relational Efficiencies…In Expressions LAST In COMPUTE

  41. Non-Relational Efficiencies…In Expressions LAST • Same as DEFINE • Operates on matrix • Processes same or fewer records than DEFINE In COMPUTE

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

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

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

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

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

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

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

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

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

More Related