Modeling tip 4 : READ and Performance 1

READ of attributes of two Entity Types

You'll learn:
- how the DM and DSL are used by READ-statements
- which SQL code is generated by a READ-statement
- how many tables are involved in case of a READ of two entity types 

Do you use READ statements to retrieve data from the DB? Then be careful, because clumsy modeling of READ-statements often causes a bad performance. 
Many factors influence the performance of an application reading data from a database.
Among them are:
- the structure of the Data Model
- the structure of the tables, indexes etc of the database
- the views in the Action Diagram
- the structure of the READ-statement(s)

In this modeling tip 4 is discussed how the code is generated by Gen from the specifications in the Gen model. 
Be aware that the READ-statement uses the entity types and attributes in the Data Model. The generated code uses the table and column names of the generated database.

The following topics are discussed:
1. the Data Model (DM) and the Data Structure List (DSL)
2. the READ-statements and the views of the action diagram retrieving 
a. Customer data 
b. Order data and
c. Customer-Order data

1. Data Model and Data Structure List
The Data Model is a model of the business data, not necessarily the structure of the tables in the database. Transformation creates Data Structure List (DSL) from the DM. The DSL shows the structure of the database tables storing the data. 

Each element in the DM is implemented by a corresponding element in the DSL. Some of the most important elements are mentioned in the figure below.

This principle is identical for each type of DBMS. Figure 1 shows how the DM of two entity types is transformed into the DSL.



figure 1.

Every attribute is implemented  as a column in a table. 
The 1:n relationship is implemented by including the identifier of the entity type of the 1-site as the foreign-key column in the table of the n-site. See figure 2 below.


figure 2.

2. Retrieve Customer data
To retrieve data of the entity type Customer from the database a READ-statement is used:  READ customer.
Generation of the code of a READ-statement results in a SQL-statement. READ customer will produce a SELECT .... FROM CUSTOMER. 
Not all attributes of the read entity type are delivered to the entity action view of the user application. Only the identifier(s) and the attributes included in the entity action view are retrieved. See figure 3 below.


figure 3.

N.B. Do you want to see the generated SQL code? Select in the Generation window the line of the action diagram and then select Diagram, Show, Source. Depending on your generation environment you might see a static or dynamic type of SQL.

3. Retrieve Order data
READ order generates a similar SELECT for the order table. If all attributes are present in the entity action view, all attribute values will be read from the table and delivered to the application.

SELECT
NUMBER
ORDER_DATE
......
......
FROM
ORDER


4. Retrieve Customer and Order data
Retrieve data of Customer and Order data can be done in two ways:
1. Two READ statements: READ Customer followed by READ Order
After generation there are two Select statements, one for Customer and one for Order.
Both tables are read in the way discussed under 2. above.
2. A combined READ statement: READ Customer, Order
After generation there is only one Select statement.
Whether this Select will access one or two tables depends on several factors. 
The order table in figure 2 contains a foreign key column of the Customer number.
If Customer number is the only attribute to be retrieved (= only number in the Customer
entity action view),  the Customer number can be read from the Customer table or from the
Order table. CA Gen optimizes the SQL in a way that only the Order table will be read.
The READ of two entity types accesses only the Order table.
The READ of two entity types results in a Select of only one table.
See figure 4 below.



figure 4.

N.B. After checking the generated code it is often not obvious which READ stuation will be the best. In the situations where performance is not an issue, do not spend much time on the subject. In case performance of the READ is important you probably need more information about the generated SQL. Size of the table, fill factor, indexes and other aspects must be taken into account to make the right choice. Often after discussion with the DBA. However, good knowledge about the way SQL is generated from the READ statement is important for the developer to be able to take the right action.

You may consider that the Database Administrator has several possibilities to change the DB-design in Gen. However, not every DBA has enough knowledge about this aspect. For instance, denormalization of the database can be done in Gen to improve the performance, but not all DBAs know how to do that.