relational efficiencies l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Relational Efficiencies PowerPoint Presentation
Download Presentation
Relational Efficiencies

Loading in 2 Seconds...

play fullscreen
1 / 45

Relational Efficiencies - PowerPoint PPT Presentation


  • 204 Views
  • Uploaded on

Relational Efficiencies. Renee Teatro June 2008. Relational Efficiencies Agenda. Optimization Overview JOIN Scenarios Sort Scenarios Aggregation Scenarios Selection Scenarios. Relational Efficiencies: Optimization Overview Optimization Hierarchy. JOIN. If the JOIN optimization fails,

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Relational Efficiencies' - ostinmannual


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
relational efficiencies

Relational Efficiencies

Renee Teatro

June 2008

relational efficiencies agenda
Relational EfficienciesAgenda
  • Optimization Overview
  • JOIN Scenarios
  • Sort Scenarios
  • Aggregation Scenarios
  • Selection Scenarios
relational efficiencies optimization overview optimization hierarchy
Relational Efficiencies: Optimization OverviewOptimization Hierarchy

JOIN

If the JOIN optimization fails,

so will the SORT and

AGGREGATION.

SORT

If the SORT optimization fails,

so will AGGREGATION.

AGGREGATION will occur only if

JOIN and SORT operations were

optimized (and AGGREGATION

requested).

AGGREGATION

slide4

Relational Efficiencies: Optimization Overview

Data Adapter TRACE Facility

  • SET TRACEON=component//destination
  • Component
    • SQLDI - FSTRACE - All Adapter-RDBMS activity
    • SQLAGGR - FSTRACE3 - Optimization messages
    • STMTRACE - FSTRACE4 - SQL only
    • SQLCALL - commands and data exchange between the physical and the logical layers of the data adapter
  • Destination
    • FSTRACE - allocation for the ddname of FSTRACE
    • CLIENT - displays client session to the screen

NOTE: Make sure to SET TRACEUSER=OFF afterwards

slide5

Relational Efficiencies: Optimization Overview

Data Adapter TRACE Facility

SET TRACEOFF=ALL

SET TRACEUSER=CLIENT

SET TRACEON=SQLAGGR//CLIENT

SET TRACEON=STMTRACE//CLIENT

SET TRACESTAMP=OFF

SET XRETRIEVAL=OFF

TABLE FILE …

  • SET XRETRIEVAL=[ON | OFF]
    • ON – the data adapter sends the request to the RDBMS and it processes the request
    • OFF – the data adapter attempts to optimize the request and trace output is generated but no RDBMS processing is done
slide6

Relational Efficiencies: JOIN ScenariosJOIN Optimization

  • Interface attempts to generate ONE SELECT statement to JOIN all tables
  • Applies to dynamic or embedded JOINs
    • One OPEN cursor operation
    • JOIN optimized more readily by RDBMS
    • An optimized JOIN enables sorts and aggregations to be passed
    • Limits interface <==> RDBMS communications
  • If JOIN is not passed to RDBMS
    • Termed: FOCUS-managed JOIN
      • One SELECT statement for EACH table
slide7

Relational Efficiencies: JOIN ScenariosOptimized JOIN vs. Non-Optimized JOIN

JOIN F1SSN IN TABLE1 TO F2SSN IN TABLE2

TABLE FILE TABLE1

PRINT F2SSN

END

Optimized JOIN:

SELECT T1.F1SSN,T2.F2SSN FROM PROD.TABLE1 T1, PROD.TABLE2 T2 WHERE (T2.F2SSN = T1.F1SSN)

FOR FETCH ONLY;

Non-Optimized JOIN:

(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):

SELECT T1.F1SSN FROM PROD.TABLE1 T1 FOR FETCH ONLY;

SELECT T2.F2SSN FROM PROD.TABLE2 T2 WHERE (T2.F2SSN = ?) FOR FETCH ONLY;

relational efficiencies join scenarios performance related to keys attribute
Relational Efficiencies: JOIN Scenarios Performance related to KEYS attribute

JOIN CLEAR *

JOIN AU_ID IN AUTHORS TO ALL AU_ID IN TITLEAUTHOR AS J1

TABLE FILE AUTHORS

SUM TITLE_ID AU_ORD ROYALTYPER

BY AU_LNAME

BY AU_FNAME

BY AU_ID

END

FOC2510 - FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):

FOC2505 - A SEGMENT IN THE STRUCTURE IS NON-KEYED: : AUTHORS

FOC2517 - FST. OR LST. WHERE SORT FIELDS DO NOT COVER KEY

FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:

FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED

SELECT T1."au_id",T1."au_lname",T1."au_fname" FROM

pubs.dbo.authors T1;

SELECT T2."au_id",T2."title_id",T2."au_ord",T2."royaltyper"

FROM pubs.dbo.titleauthor T2 WHERE (T2."au_id" = ?) ORDER BY

T2."au_id",T2."title_id";

relational efficiencies join performance related to keys attribute
Relational EfficienciesJOIN: Performance related to KEYS attribute

The .acx (ACCESS FILE DESCRIPTION) should have KEYS= with a

NON-ZERO value to indicate the PRIMARY KEY for the table in

question. The PRIMARY KEY is made up of the FIRST n fields

in the .mas (MASTER FILE DESCRIPTION).

SEGNAME=AUTHORS, TABLENAME=pubs.dbo.authors,

CONNECTION=SQLSRV, KEYS=0,$ *** Change to KEYS=1

SEGNAME=TITLEAUTHOR, TABLENAME=pubs.dbo.titleauthor, CONNECTION=SQLSRV, KEYS=2,$

  • AGGREGATION DONE ...
  • SELECT T1."au_lname",T1."au_fname",T1."au_id",
  • MAX(T2."title_id"), SUM(T2."au_ord"), SUM(T2."royaltyper") FROM
  • pubs.dbo.authors T1,pubs.dbo.titleauthor T2 WHERE (T2."au_id" =
  • T1."au_id") GROUP BY T1."au_lname",T1."au_fname",T1."au_id"
  • ORDER BY T1."au_lname",T1."au_fname",T1."au_id";
relational efficiencies join scenarios performance related to keys attribute10
Relational Efficiencies: JOIN Scenarios Performance related to KEYS attribute

Another MFD/AFD by the same name higher in the application

path list with the SAME name as one of the JOINed tables.

WHENCE filename MASTER

WHENCE AUTHORS MASTER

WHENCE TITLEAUTHOR MASTER

C:\ibi\apps\sqlsrv\authors.mas

C:\ibi\apps\summit08\titleauthor.mas

APP PREPENDPATH summit08

WHENCE AUTHORS MASTER

WHENCE TITLEAUTHOR MASTER

C:\ibi\apps\summit08\authors.mas

C:\ibi\apps\summit08\titleauthor.mas

relational efficiencies join scenarios join performance related to multi path
Relational Efficiencies: JOIN Scenarios JOIN: Performance related to MULTI-PATH

SET TRACEOFF=ALL

SET TRACEUSER=CLIENT

SET TRACEON=SQLAGGR//CLIENT

SET TRACEON=STMTRACE//CLIENT

SET TRACESTAMP=OFF

SET XRETRIEVAL=OFF

SET MULTIPATH=COMPOUND

JOIN CLEAR *

JOIN TITLE_ID IN TITLEAUTHOR TO ALL TITLE_ID IN TITLES AS J1

JOIN TITLE_ID IN TITLEAUTHOR TO ALL TITLE_ID IN ROYSCHED AS J2

CHECK FILE TITLEAUTHOR PICT

TABLE FILE TITLEAUTHOR

PRINT TITLE AU_ORD ROYALTYPER PRICE LORANGE HIRANGE

BY TITLE_ID

END

relational efficiencies join scenarios join performance related to multi path optimized
Relational Efficiencies: JOIN Scenarios JOIN: Performance related to MULTI-PATH - Optimized

FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:

FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED

SELECT T1."title_id",T1."au_ord",T1."royaltyper",T2."lorange",

T2."hirange",T3."title",T3."price" FROM pubs.dbo.titleauthor T1,

pubs.dbo.roysched T2,pubs.dbo.titles T3 WHERE (T2."title_id" =

T1."title_id") AND (T3."title_id" = T1."title_id") ORDER BY

T1."title_id";

Prior Behavior

(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):

(FOC2512) MULTI-PATH REQUEST

(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:

(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED

  • Adjust JOIN field(s) to create a SINGLE PATH structure
  • SQL Passthru
  • Create relational VIEW on RDBMS – single segment MFD
  • Create HOLD files & JOIN HOLD files vs. FOCUS-managed JOIN
relational efficiencies join scenarios join performance related to multi path13
Relational Efficiencies: JOIN Scenarios JOIN: Performance related to MULTI-PATH

CHECK FILE TITLEAUTHOR PICTURE

relational efficiencies join scenarios join performance related to multi path14
Relational Efficiencies: JOIN Scenarios JOIN: Performance related to MULTI-PATH

JOIN CLEAR *

JOIN TITLE_ID IN TITLEAUTHOR TO ALL TITLE_ID IN TITLES AS J1

JOIN TITLES.TITLE_ID IN TITLEAUTHOR TO ALL TITLE_ID IN ROYSCHED AS J2

Benefits of Single Path:

Avoid encounter with FOC030

(FOC030) ALL VERB OBJECTS MUST BE IN THE SAME PATH AS THEIR SORT FIELDS

relational efficiencies join scenarios join performance related to multi path15
Relational Efficiencies: JOIN Scenarios JOIN: Performance related to MULTI-PATH

JOIN CLEAR *

JOIN TITLE_ID IN TITLEAUTHOR TO TITLE_ID IN TITLES AS J1

JOIN TITLE_ID IN TITLEAUTHOR TO ALL TITLE_ID IN ROYSCHED AS J2

relational efficiencies join scenarios join multiplicative effect
Relational Efficiencies: JOIN Scenarios JOIN: Multiplicative Effect

JOIN CLEAR *

JOIN TITLE_ID IN TITLES TO ALL TITLE_ID IN TITLEAUTHOR AS J1

TABLE FILE TITLES

SUM ROYALTY AU_ORD ROYALTYPER

BY TITLE

BY TITLE_ID

END

FOC2506 - INTERFACE-MANAGED NATIVE JOIN SELECTED FOR THE FOLLOWING REASON:

FOC2516 - MULTIPLICATIVE EFFECT WOULD ENSUE

FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:

FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED

SELECT T1."title_id",T1."title",T1."royalty",T2."au_id",

T2."title_id",T2."au_ord",T2."royaltyper" FROM pubs.dbo.titles

T1,pubs.dbo.titleauthor T2 WHERE (T2."title_id" =

T1."title_id") ORDER BY T1."title_id",T2."au_id",T2."title_id";

relational efficiencies join scenarios join multiplicative effect17
Relational Efficiencies: JOIN Scenarios JOIN: Multiplicative Effect

JOIN CLEAR *

JOIN TITLE_ID IN TITLES TO ALL TITLE_ID IN TITLEAUTHOR AS J1

TABLE FILE TITLES

SUM MAX.ROYALTY AU_ORD ROYALTYPER

BY TITLE

BY TITLE_ID

END

AGGREGATION DONE ...

SELECT T1."title",T1."title_id", MAX(T1."royalty"),

SUM(T2."au_ord"), SUM(T2."royaltyper") FROM pubs.dbo.titles T1,

pubs.dbo.titleauthor T2 WHERE (T2."title_id" = T1."title_id")

GROUP BY T1."title",T1."title_id" ORDER BY T1."title",

T1."title_id";

relational efficiencies sort scenarios sort management by the rdbms19
Relational Efficiencies: Sort Scenarios Sort Management by the RDBMS
  • BY and ACROSS equate to SQL ORDER BY clause
  • BY / FOR ROWS OVER syntax will translate
  • JOIN TITLE_ID IN TITLES TO ALL TITLE_ID IN ROYSCHED AS J1
  • TABLE FILE TITLES
  • SUM ADVANCE LORANGE HIRANGE BY PUB_ID
  • FOR TITLE_ID
  • 'PS2091' OVER
  • 'PS2106' OVER
  • 'MC3021' OVER
  • 'BU1111' OVER
  • 'PC8888'
  • END
  • AGGREGATION DONE ...
  • SELECT T1."pub_id",T1."title_id", SUM(T1."advance"),
  • SUM(T2."lorange"), SUM(T2."hirange") FROM pubs.dbo.titles T1,
  • pubs.dbo.roysched T2 WHERE (T2."title_id" = T1."title_id") AND
  • (T1."title_id" IN('BU1111', 'MC3021', 'PC8888', 'PS2091',
  • 'PS2106')) GROUP BY T1."pub_id",T1."title_id" ORDER BY
  • T1."pub_id",T1."title_id";
relational efficiencies sort scenarios sort management by focus webfocus21
Relational Efficiencies: Sort Scenarios Sort Management by FOCUS / WebFOCUS
  • DEFINE FILE TITLES
  • SHORT_TITLE/A10=EDIT(TITLE,'9999999999');
  • PRICE_INC/P21.2=PRICE * .10;
  • END
  • TABLE FILE TITLES
  • PRINT PRICE
  • BY TITLE_ID
  • BY PRICE_INC
  • -*BY SHORT_TITLE
  • END
  • FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
  • FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
  • SELECT T1."title_id",T1."price" FROM pubs.dbo.titles T1;
  • FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
  • FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
  • SELECT T1."title_id",T1."title",T1."price" FROM pubs.dbo.titles T1;
relational efficiencies sort scenarios sort management by the rdbms22
Relational Efficiencies: Sort Scenarios Sort Management by the RDBMS
  • DEFINE FILE TITLES
  • SHORT_TITLE/A10=EDIT(TITLE,'9999999999');
  • PRICE_INC/P21.2=PRICE * .10;
  • END
  • TABLE FILE TITLES
  • SUM PRICE BY TITLE_ID
  • BY PRICE_INC
  • -*BY SHORT_TITLE
  • END
  • AGGREGATION DONE ...
  • SELECT T1."title_id",(T1."price" * .1), SUM(T1."price") FROM
  • pubs.dbo.titles T1 GROUP BY T1."title_id",(T1."price" * .1)
  • ORDER BY T1."title_id",(T1."price" * .1);
  • AGGREGATION DONE ...
  • SELECT T1."title_id",(SUBSTRING(T1."title", 1, 10)),
  • SUM(T1."price") FROM pubs.dbo.titles T1 GROUP BY T1."title_id",
  • (SUBSTRING(T1."title", 1, 10)) ORDER BY T1."title_id",
  • (SUBSTRING(T1."title", 1, 10));
relational efficiencies sort scenarios sort management by the rdbms23
Relational Efficiencies: Sort Scenarios Sort Management by the RDBMS
  • DEFINE FILE TITLES
  • MONTH_COMPONENTA/A3=HNAME(PUBDATE,'MONTH','A3');
  • MONTH_COMPONENTI/I2=HPART(PUBDATE,'MONTH','I2');
  • END
  • TABLE FILE TITLES
  • SUM PRICE
  • -*BY MONTH_COMPONENTA
  • BY MONTH_COMPONENTI
  • -*BY PUBDATE
  • END

Tracing via DEFINE field MONTH_COMPONENTI or MONTH_COMPONENTA sort:

  • FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
  • FOC2609 - CANNOT AGGREGATE BY NON-SQLIZABLE SORT KEY: MONTH_COMPONENTI
  • SELECT T1."price",T1."pubdate" FROM pubs.dbo.titles T1;

Tracing via REAL field ORD_DATE sort:

  • AGGREGATION DONE ...
  • SELECT T1."pubdate", SUM(T1."price") FROM pubs.dbo.titles T1
  • GROUP BY T1."pubdate" ORDER BY T1."pubdate";
relational efficiencies sort scenarios sort management by the rdbms24
Relational Efficiencies: Sort Scenarios Sort Management by the RDBMS
  • TABLE FILE TITLES
  • SUM PRICE
  • -*BY MONTH_COMPONENTA
  • BY MONTH_COMPONENTI
  • -*BY PUBDATE
  • END
  • AGGREGATION DONE ...
  • SELECT T1."pubdate", SUM(T1."price") FROM pubs.dbo.titles T1
  • GROUP BY T1."pubdate" ORDER BY T1."pubdate";
  • FILENAME=TITLES, SUFFIX=SQLMSS , $
  • SEGMENT=TITLES, SEGTYPE=S0, $
  • FIELDNAME=TITLE_ID, ALIAS=title_id, USAGE=A6, ACTUAL=A6, $
  • FIELDNAME=TITLE, ALIAS=title, USAGE=A80, ACTUAL=A80, $
  • FIELDNAME=PUBDATE, ALIAS=pubdate, USAGE=HYYMDs, ACTUAL=HYYMDs, $
  • FIELDNAME=MONTH_COMPONENTI,ALIAS=pubdate,USAGE=M,ACTUAL=DATE, $
  • FIELDNAME=MONTH_COMPONENTA,ALIAS=pubdate,USAGE=MT,ACTUAL=DATE, $
relational efficiencies sort scenarios using tablef
Relational Efficiencies: Sort Scenarios Using TABLEF
  • From a performance standpoint, consider using TABLEF instead of TABLE for requests that you know are being returned in the requested sort order. This will free FOCUS / WebFOCUS from having to verify the sort order by loading its internal matrix. FOCUS / WebFOCUS will act directly on the data returned by the RDBMS.
  • ACROSS phrases are not supported.
  • It must be verified that an ORDER BY statement has been generated in the SQL for the correct columns prior to using TABLEF.
  • Multi-verb requests are not supported.
  • RETYPE is not available.
  • TABLEF can hold locks on data pages until the data adapter issues a COMMIT (usually when the report displays). Locks may prevent access to the data by other applications. (online core FOCUS)
  • Certain prefix operators are not available (TOT., PCT.)
  • Computes that use prefix operators are not supported.
slide26

Relational Efficiencies: Aggregation Scenarios Translatable Aggregation

Type of expressions

Expression components

Examples

Arithmetic Valued(Expressions that return a single number)

DEFINE FILE ORAEMP

NEW_SAL/D12.2=(CSAL * 0.10)

+ CSAL ;

END

  • Real fields of datatype I, P, D, or F
  • Numeric constants
  • Arithmetic operators (+, -, *, / )
  • Real fields of datatype A
  • String constants
  • Concatenation operator (I)
  • EDIT of alphanumeric fields

Character String Valued(Expressions that return a character string)

DEFINE FILE ORAEMP

NAME/A18=EDIT(FN,‘9.$’)|LN;

END

  • Verbs: SUM, COUNT, WRITE
  • Direct operators: MIN., MAX., AVE.
  • Aggregating DEFINEd fields:
    • Constant DEFINEd fields translated with CNT.

The following defined expressions can be translated

relational efficiencies aggregation scenarios key points for aggregation translation
Relational Efficiencies: Aggregation ScenariosKey Points for Aggregation Translation
  • All IF or WHERE statements in the request are translated to SQL WHERE clause.
  • FOCUS / WebFOCUS must generate a single SQL statement
  • Only these aggregation commands or operators are used: SUM, COUNT, WRITE, MIN., MAX., AVE., SUM., DST., CNT. or CNT.DST.
relational efficiencies aggregation scenarios sum on alphanumeric field
Relational Efficiencies: Aggregation ScenariosSUM on ALPHANUMERIC Field

JOIN STOR_ID IN STORES TO ALL STOR_ID IN SALES AS J1

TABLE FILE STORES

SUM STOR_NAME QTY

ORD_DATE TITLE_ID

BY STOR_ID

END

FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:

FOC2591 - AVE ON ALPHA, DATE OR DATETIME CANNOT BE AGGREGATED

SELECT T1."stor_id",T1."stor_name",T2."stor_id",T2."ord_num",

T2."title_id",T2."ord_date",T2."qty" FROM pubs.dbo.stores T1,

pubs.dbo.sales T2 WHERE (T2."stor_id" = T1."stor_id")

ORDER BY T1."stor_id",T2."stor_id",T2."ord_num",T2."title_id";

relational efficiencies aggregation scenarios sum on alphanumeric field29
Relational Efficiencies: Aggregation ScenariosSUM on ALPHANUMERIC Field
  • JOIN STOR_ID IN STORES TO ALL STOR_ID IN SALES AS J1
  • TABLE FILE STORES
  • SUM STOR_NAME QTY
  • MAX.ORD_DATE MIN.TITLE_ID
  • BY STOR_ID
  • -*BY TITLE_ID
  • -*BY ORD_DATE
  • END
  • FOC2589 - AGGREGATION DONE BUT MAY PRODUCE INCONSISTENT RESULTS
  • FOC2612 - OVERFLOW MAY OCCUR WHEN SUMMING AN I2 FIELD WITH USAGE I, D OR P
  • SELECT T1."stor_id", MAX(T1."stor_name"), SUM(T2."qty"),
  • MAX(T2."ord_date"), MIN(T2."title_id") FROM pubs.dbo.stores T1,
  • pubs.dbo.sales T2 WHERE (T2."stor_id" = T1."stor_id") GROUP BY
  • T1."stor_id" ORDER BY T1."stor_id";
  • FOC2589 - AGGREGATION DONE BUT MAY PRODUCE INCONSISTENT RESULTS
  • FOC2612 - OVERFLOW MAY OCCUR WHEN SUMMING AN I2 FIELD WITH USAGE I, D OR P
  • SELECT T1."stor_id",T2."title_id",T2."ord_date",
  • MAX(T1."stor_name"), SUM(T2."qty") FROM pubs.dbo.stores T1,
  • pubs.dbo.sales T2 WHERE (T2."stor_id" = T1."stor_id") GROUP BY
  • T1."stor_id",T2."title_id",T2."ord_date" ORDER BY T1."stor_id",
  • T2."title_id",T2."ord_date";
relational efficiencies aggregation scenarios if wheres translated for aggregation
Relational Efficiencies: Aggregation ScenariosIF/WHEREs Translated for Aggregation
  • JOIN STOR_ID IN STORES TO ALL STOR_ID IN SALES AS J1
  • TABLE FILE STORES
  • SUM QTY BY STOR_ID BY STOR_NAME
  • WHERE STOR_ID GT '7130'
  • WHERE ZIP LIKE '9%'
  • WHERE QTY EQ EDIT('10')
  • -*WHERE QTY GT 10
  • WHERE PAYTERM LIKE 'Net %'

END

  • FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : WHERE expression
  • FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
  • FOC2596 - ONE OR MORE EXPRESSION(S) CAN NOT BE TRANSLATED TO SQL
  • SELECT T1."stor_id",T1."stor_name",T1."zip",T2."qty",
  • T2."payterms" FROM pubs.dbo.stores T1,pubs.dbo.sales T2 WHERE
  • (T2."stor_id" = T1."stor_id") AND (T1."zip" LIKE '9%') AND
  • (T1."stor_id" > '7130') AND (T2."payterms" LIKE 'Net %') ORDER
  • BY T1."stor_id",T1."stor_name";

  • WHERE (T2."stor_id" = T1."stor_id") AND (T1."zip" LIKE '9%') AND (T1."stor_id" >
  • '7130') AND (T2."payterms" LIKE 'Net %') AND (T2."qty" > 10) GROUP BY T1."stor_id",T1."stor_name" ORDER BY T1."stor_id", T1."stor_name";
relational efficiencies aggregation scenarios define field logical expression optimized
Relational Efficiencies: Aggregation ScenariosDEFINE Field Logical Expression: Optimized
  • JOIN STOR_ID IN STORES TO ALL STOR_ID IN SALES AS J1
  • DEFINE FILE STORES
  • LOGICAL_NUM/I1=IF QTY GT 20 THEN 1 ELSE 0;
  • LOGICAL_CHR/A1=IF QTY GT 20 THEN 'Y' ELSE 'N';
  • END
  • TABLE FILE STORES
  • SUM QTY
  • LOGICAL_NUM
  • -*LOGICAL_CHR
  • BY STOR_ID BY STOR_NAME
  • END
  • FOC2589 - AGGREGATION DONE BUT MAY PRODUCE INCONSISTENT RESULTS
  • FOC2612 - OVERFLOW MAY OCCUR WHEN SUMMING AN I2 FIELD WITH USAGE I, D OR P
  • SELECT T1."stor_id",T1."stor_name", SUM(T2."qty"), SUM((CASE
  • WHEN (T2."qty" > 20) THEN 1 ELSE 0 END)) FROM pubs.dbo.stores
  • T1,pubs.dbo.sales T2 WHERE (T2."stor_id" = T1."stor_id") GROUP
  • BY T1."stor_id",T1."stor_name" ORDER BY T1."stor_id",
  • T1."stor_name";

Message in earlier releases:

(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:

(FOC2597) USE OF DEFINE FIELD THAT CANNOT BE AGGREGATED : LOGICAL

slide32

Relational Efficiencies: Selection Scenarios

Non-Translatable Screening Conditions

Example

Expressions using

DEFINE... FNL/I3 = ARGLEN(15,LN,FNL);TABLE... IF FNL LE 6

User-written subroutines

DEFINE... CPT/I2=CPT+1;TABLE... IF CPT NE 0

Self-referential expressions

EDIT for field format conversions

WHERE EDIT(ID) GT 20

DEFINE... NAME/A27=FN||(‘ ‘ | LN);TABLE... IF NAME EQ ‘DANIEL VALINO’

Strong concatenation (II)

DEFINE... DEVISION/A11=DECODE CDIV (‘CORP’ ‘CORPORATE’ ‘NE’ ‘NORTH-EAST’ ELSE ‘NA’);TABLE... IF DIVISION EQ ‘CORPORATE’ OR ‘NORTH-EAST’

DECODE function

Non-SQL relational operators(INCLUDES, EXCLUDES)

IF LN INCLUDES ‘VALINO’

FOCUS subroutines(ABS, INT, MAX, MIN, LOG, SQRT)

WHERE SQRT(CSAL) GT 260

Expressions using fields withACTUAL=DATE

DEFINE... HDAT2/YYMD=HDAT+365;TABLE... IF HDAT2 GT ‘1990/03/01’

relational efficiencies selection scenarios variable with subroutine reference
Relational Efficiencies: Selection ScenariosVariable with Subroutine Reference
  • -DEFAULT &EMPVAL='pma42628m';
  • DEFINE FILE EMPLOYEE
  • EMPTEST/A9=UPCASE(9,'&EMPVAL','A9');
  • END
  • TABLE FILE EMPLOYEE
  • PRINT JOB_ID JOB_LVL HIRE_DATE
  • BY EMP_ID
  • WHERE EMP_ID EQ UPCASE(9,'&EMPVAL','A9')
  • -*WHERE EMP_ID EQ EMPTEST
  • END
  • FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : WHERE expression
  • FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
  • FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
  • SELECT T1."emp_id",T1."job_id",T1."job_lvl",T1."hire_date"
  • FROM pubs.dbo.employee T1 ORDER BY T1."emp_id";
relational efficiencies selection scenarios variable with subroutine reference34
Relational Efficiencies: Selection ScenariosVariable with Subroutine Reference
  • -DEFAULT &EMPVAL='pma42628m';
  • -SET &UPEMPVAL=UPCASE(9,'&EMPVAL.EVAL','A9');
  • DEFINE FILE EMPLOYEE
  • EMPTEST/A9 WITH EMP_ID='&UPEMPVAL';
  • END
  • TABLE FILE EMPLOYEE
  • PRINT JOB_ID JOB_LVL HIRE_DATE
  • BY EMP_ID
  • WHERE EMP_ID EQ '&UPEMPVAL'
  • -*WHERE EMP_ID EQ EMPTEST
  • END
  • FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
  • FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED
  • SELECT T1."emp_id",T1."job_id",T1."job_lvl",T1."hire_date"
  • FROM pubs.dbo.employee T1 WHERE (T1."emp_id" = 'PMA42628M')
  • ORDER BY T1."emp_id";
relational efficiencies selection scenarios dates
Relational Efficiencies: Selection ScenariosDATEs

DEFINE FILE EMPLOYEE

Hire_Year/YY=HPART(HIRE_DATE,'YEAR','YY');

END

TABLE FILE EMPLOYEE

PRINT COMPUTE NAME/A55=LNAME || ', ' | FNAME | ' ' |MINIT; HIRE_DATE

BY LNAME NOPRINT BY FNAME NOPRINT BY MINIT NOPRINT

WHERE Hire_Year GE 1990 and Hire_Year LT 1991

END

FILENAME=EMPLOYEE, SUFFIX=SQLMSS , $

SEGMENT=EMPLOYEE, SEGTYPE=S0, $

FIELDNAME=HIRE_DATE,ALIAS=hire_date,USAGE=HYYMDs,ACTUAL=HYYMDs, $

FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : Hire_Year

FOC2598 - FOCUS IF/WHERE TEST CANNOT BE PASSED TO SQL : Hire_Year

FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:

FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED

SELECT T1."fname",T1."minit",T1."lname",T1."hire_date" FROM

pubs.dbo.employee T1 ORDER BY T1."lname",T1."fname",T1."minit";

relational efficiencies selection scenarios dates36
Relational Efficiencies: Selection ScenariosDATEs

TABLE FILE EMPLOYEE

PRINT COMPUTE NAME/A55=LNAME || ', ' | FNAME | ' ' |MINIT; HIRE_DATE

BY LNAME NOPRINT BY FNAME NOPRINT BY MINIT NOPRINT

WHERE Hire_Year GE 1990 and Hire_Year LT 1991

END

FILENAME=EMPLOYEE, SUFFIX=SQLMSS , $

SEGMENT=EMPLOYEE, SEGTYPE=S0, $

FIELDNAME=HIRE_DATE,ALIAS=hire_date,USAGE=HYYMDs,ACTUAL=HYYMDs, $

FIELDNAME=HIRE_YEAR,ALIAS=Hire_Year,USAGE=YY,ACTUAL=DATE, $

FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:

FOC2594 - AGGREGATION IS NOT APPLICABLE TO THE VERB USED

SELECT T1."fname",T1."minit",T1."lname",T1."hire_date",

T1."Hire_Year" FROM pubs.dbo.employee T1 WHERE (T1."Hire_Year"

< '19910101') AND (T1."Hire_Year" >= '19900101') ORDER BY

T1."lname",T1."fname",T1."minit";

relational efficiencies selection scenarios case sensitive insensitive selection coming soon
Relational Efficiencies: Selection ScenariosCase Sensitive/Insensitive Selection: Coming Soon
  • WebFOCUS / FOCUS Selection values are case sensitive
  • RDBMS environment could allow for case insensitive selection
  • Answer set returned by RDBMS could allow for all permutations resulting in case insensitive values
  • WebFOCUS / FOCUS will reapply selection so that final result will be case sensitive
  • Workarounds
    • Direct SQL Passthru
    • UPCASE both the FIELD and VALUE in WHERE clause
      • Selection will not be optimized and will impact aggregation if aggregation is part of the request
  • New feature to allow for user to specify if selection should be case sensitive or case insensitive
relational efficiencies appendix
Relational EfficienciesAppendix

FILENAME=EMPLOYEE, SUFFIX=SQLMSS , $

SEGMENT=EMPLOYEE, SEGTYPE=S0, $

FIELDNAME=EMP_ID, ALIAS=emp_id, USAGE=A9, ACTUAL=A9, $

FIELDNAME=FNAME, ALIAS=fname, USAGE=A20, ACTUAL=A20, $

FIELDNAME=MINIT, ALIAS=minit, USAGE=A1, ACTUAL=A1,

MISSING=ON, $

FIELDNAME=LNAME, ALIAS=lname, USAGE=A30, ACTUAL=A30, $

FIELDNAME=JOB_ID, ALIAS=job_id, USAGE=I6, ACTUAL=I2, $

FIELDNAME=JOB_LVL, ALIAS=job_lvl, USAGE=I6, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=PUB_ID, ALIAS=pub_id, USAGE=A4, ACTUAL=A4, $

FIELDNAME=HIRE_DATE, ALIAS=hire_date, USAGE=HYYMDs, ACTUAL=HYYMDs, $

SEGNAME=EMPLOYEE, TABLENAME=pubs.dbo.employee, CONNECTION=SQLSRV, KEYS=1,$

relational efficiencies appendix40
Relational EfficienciesAppendix

FILENAME=AUTHORS, SUFFIX=SQLMSS , $

SEGMENT=AUTHORS, SEGTYPE=S0, $

FIELDNAME=AU_ID, ALIAS=au_id, USAGE=A11, ACTUAL=A11, $

FIELDNAME=AU_LNAME, ALIAS=au_lname, USAGE=A40, ACTUAL=A40, $

FIELDNAME=AU_FNAME, ALIAS=au_fname, USAGE=A20, ACTUAL=A20, $

FIELDNAME=PHONE, ALIAS=phone, USAGE=A12, ACTUAL=A12, $

FIELDNAME=ADDRESS, ALIAS=address, USAGE=A40, ACTUAL=A40,

MISSING=ON, $

FIELDNAME=CITY, ALIAS=city, USAGE=A20, ACTUAL=A20,

MISSING=ON, $

FIELDNAME=STATE, ALIAS=state, USAGE=A2, ACTUAL=A2,

MISSING=ON, $

FIELDNAME=ZIP, ALIAS=zip, USAGE=A5, ACTUAL=A5,

MISSING=ON, $

FIELDNAME=CONTRACT, ALIAS=contract, USAGE=I11, ACTUAL=I4, $

SEGNAME=AUTHORS, TABLENAME=pubs.dbo.authors, CONNECTION=SQLSRV, KEYS=1,$

relational efficiencies appendix41
Relational EfficienciesAppendix

FILENAME=TITLES, SUFFIX=SQLMSS , $

SEGMENT=TITLES, SEGTYPE=S0, $

FIELDNAME=TITLE_ID, ALIAS=title_id, USAGE=A6, ACTUAL=A6, $

FIELDNAME=TITLE, ALIAS=title, USAGE=A80, ACTUAL=A80, $

FIELDNAME=TYPE, ALIAS=type, USAGE=A12, ACTUAL=A12, $

FIELDNAME=PUB_ID, ALIAS=pub_id, USAGE=A4, ACTUAL=A4,

MISSING=ON, $

FIELDNAME=PRICE, ALIAS=price, USAGE=P21.4, ACTUAL=P10,

MISSING=ON, $

FIELDNAME=ADVANCE, ALIAS=advance, USAGE=P21.4, ACTUAL=P10,

MISSING=ON, $

FIELDNAME=ROYALTY, ALIAS=royalty, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=YTD_SALES, ALIAS=ytd_sales, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=NOTES, ALIAS=notes, USAGE=A200, ACTUAL=A200,

MISSING=ON, $

FIELDNAME=PUBDATE, ALIAS=pubdate, USAGE=HYYMDs, ACTUAL=HYYMDs, $

SEGNAME=TITLES, TABLENAME=pubs.dbo.titles, CONNECTION=SQLSRV, KEYS=1, $

relational efficiencies appendix42
Relational EfficienciesAppendix

FILENAME=TITLEAUTHOR, SUFFIX=SQLMSS , $

SEGMENT=TITLEAUTHOR, SEGTYPE=S0, $

FIELDNAME=AU_ID, ALIAS=au_id, USAGE=A11, ACTUAL=A11, $

FIELDNAME=TITLE_ID, ALIAS=title_id, USAGE=A6, ACTUAL=A6, $

FIELDNAME=AU_ORD, ALIAS=au_ord, USAGE=I6, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=ROYALTYPER, ALIAS=royaltyper, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

SEGNAME=TITLEAUTHOR, TABLENAME=pubs.dbo.titleauthor, CONNECTION=SQLSRV, KEYS=2, $

relational efficiencies appendix43
Relational EfficienciesAppendix

FILENAME=ROYSCHED, SUFFIX=SQLMSS , $

SEGMENT=ROYSCHED, SEGTYPE=S0, $

FIELDNAME=TITLE_ID, ALIAS=title_id, USAGE=A6, ACTUAL=A6, $

FIELDNAME=LORANGE, ALIAS=lorange, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=HIRANGE, ALIAS=hirange, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=ROYALTY, ALIAS=royalty, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

SEGNAME=ROYSCHED, TABLENAME=pubs.dbo.roysched, CONNECTION=SQLSRV, KEYS=1,$

relational efficiencies appendix44
Relational EfficienciesAppendix

FILENAME=STORES, SUFFIX=SQLMSS , $

SEGMENT=STORES, SEGTYPE=S0, $

FIELDNAME=STOR_ID, ALIAS=stor_id, USAGE=A4, ACTUAL=A4, $

FIELDNAME=STOR_NAME, ALIAS=stor_name, USAGE=A40, ACTUAL=A40,

MISSING=ON, $

FIELDNAME=STOR_ADDRESS, ALIAS=stor_address, USAGE=A40, ACTUAL=A40,

MISSING=ON, $

FIELDNAME=CITY, ALIAS=city, USAGE=A20, ACTUAL=A20,

MISSING=ON, $

FIELDNAME=STATE, ALIAS=state, USAGE=A2, ACTUAL=A2,

MISSING=ON, $

FIELDNAME=ZIP, ALIAS=zip, USAGE=A5, ACTUAL=A5,

MISSING=ON, $

SEGNAME=STORES, TABLENAME=pubs.dbo.stores, CONNECTION=SQLSRV, KEYS=1, $

relational efficiencies appendix45
Relational EfficienciesAppendix

FILENAME=SALES, SUFFIX=SQLMSS , $

SEGMENT=SALES, SEGTYPE=S0, $

FIELDNAME=STOR_ID, ALIAS=stor_id, USAGE=A4, ACTUAL=A4, $

FIELDNAME=ORD_NUM, ALIAS=ord_num, USAGE=A20, ACTUAL=A20, $

FIELDNAME=TITLE_ID, ALIAS=title_id, USAGE=A6, ACTUAL=A6, $

FIELDNAME=ORD_DATE, ALIAS=ord_date, USAGE=HYYMDs, ACTUAL=HYYMDs, $

FIELDNAME=QTY, ALIAS=qty, USAGE=I6, ACTUAL=I2, $

FIELDNAME=PAYTERMS, ALIAS=payterms, USAGE=A12, ACTUAL=A12, $

SEGNAME=SALES, TABLENAME=pubs.dbo.sales, CONNECTION=SQLSRV, KEYS=3, $