Universe Tuning Training Presentation

Tuning Business Object Universes

The Developer’s Dilemma •

Direct control over SQL queries has vanished

•

Business Objects allows you to code SQL fragments, not complete clauses

•

Reduced control over how these fragments are finally combined

•

Tuning SELECT statements is OUT!

•

Tuning SQL fragments, understanding how Business Objects combines these fragments is IN!

2

Possible Areas to Consider Data Modeling

Universe Design

Query Design

We will concentrate on the last two areas ...

3

Topics Covered • Tips on creating better objects, joins, and queries • Real-world examples that illustrate these tips • Emphasis on Oracle, Sybase, Informix

4

Tip 1: Process Less Data For all databases:

• The WHERE clause states certain conditions that must be met before additional processing occurs

• This additional processing usually occurs in the SELECT clause as calculations or complicated expressions.

• More restrictive conditions reduces the amount of data subject to SELECT processing.

5

Example - Running Totals ACCT_BALANCES acct_id month year beginning_balance ending-balance

•

Each row contains one account’s totals for a particular month and year.

•

Restrict this table by creating a Business Objects join asking for the time period up front.

•

This dramatically reduces the number of rows processed by the SELECT clause.

6

Example - Large Fact Tables SALES_TRACKING division sales_mgr salesman period total_sales

•

Data warehouses usually depend on a few large FACT tables that contain summarized information based on a multi-part key.

•

Restrict access to that fact table by providing values for one or more of these primary key fields.

7

Tip 2: Scan Tables Once DB: Oracle, Sybase, Informix

• Use conditional logic (IF-THEN) functions to retrieve specific information from each table row.

• Several of these functions can be placed in the SELECT clause • Applied at the same time, they read the same row once.

8

Oracle Conditional Logic Oracle provides the DECODE function:

Code: DECODE (<expression>. , , , . … <default result>)

9

Translation:

If the Expression equals Test 1. then return Result 1 If it equals Test 2. return Result 2 Else return the Default Result

Example - Oracle DECODE Return only revenues from Texas and Oklahoma customers

sum (DECODE (customers state. ‘TX’, order_lines qty_requested * order_lines price, ‘OK’, order_lines qty_requested * order_lines price, 0))

10

Sybase Conditional Logic •

Sybase does not provide a DECODE function.

•

Instead, several functions must be used based on datatype.

11

T est

R e su lt

F o rm u la

C h ar

N um

sig n ( ch arin d e x (< e xp re ssio n > , < T E S T > )) * R E S U L T

N um

N um

sig n ( < e xp re ssio n > - T E S T ) * R E S U L T

C h ar

C har

N um

C har

su b strin g ( R E S U L T , 1 , ch arin d e x ( < ex p ressio n > , T E S T ) * len g th o f R E S U L T > ) su b strin g ( R E S U L T , 1 . sig n ( < e xp re ssio n - T E S T ) * < le ng th o f R E S U L T > )

Example - Sybase Logic • Return only revenues from Texas and Oklahoma Customers

sum( sign ( charindex(customers.state, ‘TX’) + charindex( customers.state, ‘OK’ ) ) *order_lines.qty_requested * order_lines price)

12

Informix Conditional Logic • • •

Informix does not provide a DECODE function, either Informix has the smallest set of SQL functions to choose from Conditional logic can be emulated through nested TRIMs

Code: TRIM( BOTH LEADING TRAILING , <expression>)

Translation: IF the character is attached to front or rear of the expression, THEN return the expression with the character trimmed off.

13

Example - Informix Logic Return only revenues from 1st quarter

order_amount* LENGTH( TRIM(BOTH ‘2’, TRIM(BOTH ‘3’, TRIM( BOTH ‘4’, quarter_o_order))))

14

Tip 3: Avoid Outer Joins DB: All

•

Outer joins force all rows from one table to be read

•

Adding outer joins in some DB can severely limit Ad-Hoc potential

•

Try adding an additional row within the table that has no matching data

Order 7803 7804

15

Sales SP 1050.25 999 542.00 5

ID 5 999

Name Dan Hunter No Salesperson

Time-Dependant Joins • Some relationships are time-dependant, and cannot be resolved this way

Orders

Requisitions

Receipts

16

Tip 4: Driving Tables DB:

Oracle 6, Oracle 7 with Rule-Based optimization Sybase (After the first four tables joined)

•

The driving table is usually the last table in the FROM clause

•

The DB processes the driving table first, retrieving data from other tables via joins

•

Pick the smallest, most restrictive table as your driving table

17

Cash Accounts

Journal Entries

10 Rows

3 Million Rows

Example - Driving Tables •

Move to the LIBRARY subdirectory of Business Objects

•

Set HIDE_BOPTIMIZE=NO in ORA7.PRM (SYB10 for Sybase)

•

Open a BO universe as a developer

•

New menu option available - Optimize

•

Can now assign “weights” to tables

•

Lowest numbered table in query becomes the driving table

18

Tip 5: Rules for Indexed Columns DB: All

•

Indexes are meant to accelerate the retrieval of rows based on column conditions

•

Certain SQL coding standards will “disable” indexes

•

The following rules indicate where indexes are disabled

•

Use the alternative coding techniques listed to re-enable indexes

19

Rule 1 - No Calculations Do not use calculations or functions on indexed columns

20

D isables Index

U ses Index

Last_N am e LIK E …

Last_N am e LIK E … and First_N am e LIK E …

First_N am e

Salary/12>2000

Salary>2000*12

TRU N C (Sales_D ate) > TRU N C (sysdate)

Sales_D ate BETW EEN R U N C(sysdate) and TR U N C (sysdate) + .9999

Rule 2 - No Open Patterns Avoid patterns that begin with ‘%’

Disables Index

Last_Name LIKE ‘‘% ING’

21

Disables Index (but runs faster !)

Last_Name LIKE ‘_ING ’ OR Last_Name LIKE ‘_ING ’ OR Last_Name LIKE ‘ _ING ’

Rule 3 - No NULL Comparisons Avoid IS NULL, IS NOT NULL Comparisons

Disables Index

Uses Index (but runs faster !)

Product _ID IS null

Product_ID > 0 (See Note Below)

NOTE - NULL Product ID must replaced with nonsense value

22

Rule 4 - No NOT Comparisons Avoid beginning a comparison with NOT

Disables Index

Use Index

Amount !=0

Amount > 0 (See Note)

Note: Assumes amount field should be positive

23

Tip 6: Use ROWID DB: Oracle, Informix

•

ROWID is the fastest way to retrieve any row

•

It acts like the ultimate primary key, pointing to the physical location of a row

•

ROWID can be used as a field, and thought of as the primary key for uniquely indexed tables.

•

Especially helpful for tables with combination primary keys

24

Example - Using ROWID •

Counting table rows the normal way: COUNT (Employees.Last_Name)

•

Faster count - Count on an indexed field: COUNT (Employees. Employee_ID)

•

Fastest count - Count the ROWID: COUNT (Employees ROWID)

25

Tip 7: Expand Table Indexes DB: All

•

Add commonly-retrieved columns to a table index

•

Additional columns should be short in length

•

If the index columns all requested information, the DB can skip reading the table

26

Example - Expanding Indexes LOOKUPS code type description

•

The LOOKUPS table contains a Code, Type and Description

•

If the Type is normally retrieved with the code, add the Type field to the Lookups index

27

Tip 8: Mark Indexed Columns DB: All

•

Users have no idea which columns are indexed.

•

Help them by assigning a special character (like *) to the name of indexed columns

•

For combination keys, only mark the leading (first) indexed column this way

•

DO NOT mark any objects whose SQL fragments disable indexes.

28

Example - Making Indexed Columns EMPLOYEES employee_id last_name first_name city

EMPLOYEES_INDEX2 last_name first_name

S Q L F rag m en t

P o s s ib le O b je c t N a m e

la s t_ n a m e

* E m p lo y e e L a s t N a m e

firs t_ n a m e

E m p lo y e e F irs t N a m e

la s t_ n a m e

29

EMPLOYEES_INDEX1 employee_id

‘ ,’

firs t_ n a m e

E m p lo y e e F u ll N a m e

Tip 9: Postpone Processing DB: All

•

Certain database processing tasks slow down query execution

•

These same tasks could be removed from the query, and saved for Business Objects

•

Powerful post-processing allows quicker data retrieval

30

Example - Sort Data Locally •

Sorts can be removed from any query, and applied locally using the ReportWriter

•

One Drawback - Should not remove a sort from a query when Partial Results option set

31

Example - Postpone Calculations •

Calculations and concatenations within the SELECT clause slows down the query

•

The most recent version of Business Objects contains over 80 report functions

•

Remove calculations from the query, saving them for Business Objects

32

End of presentation