Modeling tip 5 : READ and Performance 2

Better performance with DB Denormalization

You'll learn:

- how performance can be improved by denormalization of the DB

- how to denormalize the Database without changing the Data Model

Question:

How can you  reduce the number of tables accessed in the DB?

We will show you how the number of tables accessed can be reduced by demonstrating a simple example.

In the DM the Entity Type Customer has a relationship Places with the ET Order.

Both Entity types have attributes.

The user would like to see customer and order attributes on the window.

How can you build a READ statement with customer and order attributes that will access only one table in the DB?

Example solution:

1. Retrieve Customer and Order data

In the Modeling tip 4 you can read how you can predict which tables will be read and how many
tables will be used.

The principle is:

- CA Gen will read from the DB all attributes mentioned in the Entity Action view of the
Entity Types being READ.
- When an attribute has been implemented in more than one table, CA Gen will try to
optimize the read action in a way that a minimum number of tables will be touched.

If performance is important, the implementation of attributes in more than the 'original' table may result in performance improvement.

2. The Data Model and Data Structure List

In the DM the two ETs Customer and Order are described in the figure below. Both own

4 attributes. There is one relationship. In the Data Structure List the customer attributes are implemented in the customer table. To implement the relationship 'places', the customer number attribute has been implemented in the order table too.




3. The Entity Action Views

Using the DM and DSL, described above, the SQL is generated for the following Read statement:

READ EACH customer

order

WHERE DESIRED order is_placed_by DESIRED customer

This statement will be generated using two different situations of the Entity Action Views.



The following examples show what SQL is generated in the situations A and B.

4. Denormalization of the Data Structure List.

In the Data Structure List the DB design can be denormalized. The attributes causing SELECT of two tables, can be implemented in the order table in the same way as the customer number has been implemented in the order table.

The customer attributes name and address are implemented in the order table as two 'Dnrm columns'. The DSL before and after the denormalization is shown in the figure below.

This denormalization will change the generated SQL in the two situations.

Result: Denormalization may reduce the number of tables in the SELECT.

5. READ versus UPDATE

An update of the customer data, however, will cause both tables to be updated.

Situation B has been described in Modeling tip 4 comprehensively: only one table is used.

Situation A shows that both tables are accessed, due to the attributes which are only present in the customer table.