Structure Query Language (SQL): Chapter 5 – Best practices for setting up Historics and Queries.

Historics are the modules responsible for storing application data in a Database for further analysis. You can create as many Historics as needed, each containing several Tags or expressions. Each Historic can independently create or use a table inside the Database, whose storage can be set up per time or per event. Next, we will analyze some practices to be adopted when setting up a historic.

Primary Key

The Primary Key is a field (or a group of fields) which identifies each table record uniquely. After a field has been set up as the table’s primary key, the Database will make sure no duplicated data will be inserted in this field. For example, if you try to place an order whose number is the same as a pre-existing one’s, the order won’t be placed, and an error message will be displayed.  There are two types of primary keys: Simple or Compound.

A Simple primary key is a field that uniquely identifies each record in a table. A Compound primary key can comprise two or more of the table’s fields. To insert a primary key into a historic, you can either access its icon or select the checkbox of the desired field, as seen below.

Figure 1: Primary Key icon
Figure 2: Primary Key

Indexes

Data can be accessed by SQL in two different ways:

Via table scan:
Scans all pages  from the beginning of the table and extracting the Query’s request.

Via indexes:
Indexes are fields (or groups of fields) previously sorted out by the database in order to improve the performance of queries using these indexes.  They are used to quickly find records with a specific value in a column. Without an index, the Database needs to start with the first record and then read the whole table up until the relevant data is met. If the table has an index for the columns being mentioned, the Database can quickly retrieve a position to look for in the data file without having to scan all records. This does not mean that indexes must be inserted in all columns, only where there are search clauses (Where). To insert an index into a historic, you can access its icon during the historic’s configuration, as seen below.

Figure 3: Indexes icon
Figure 4: Indexes

When setting up a historic, you must identify which type of filter will be applied to the table, and then insert indexes in the fields that will be used in the search. For example, if you have one filter per table, you must add an index to E3TimeStamp field. If another field in the table also has a search clause, another index must be inserted.

Instructions for setting up a historic and a query

Depending on the amount of data returned by a query, or on the number of objects with linked queries, the excessive data buildup can cause screens and data from the query to become slower. In most cases, to improve the application’s performance significantly, just follow these guidelines:

1. Set up the Query’s CursorLocation property in all objects (E3Browser, E3Chart, Report) as clClient (= 1).
2. In the table(s), create index(es) in all fields being filtered and add a primary key (whenever necessary).
3. Check whether the Query is returning the fields not being used.
4. Insert a short time frame in the initial values of the variables filtering by date.
5. When using an E3Chart loaded with several pens, select the thinnest line width available (0) in each pen’s Style option.

When using indexes and primary keys with Insert, Delete, and Update commands, the following occurs:

• An index or primary key will cause Insert command to run slower.
• The Delete and Update commands become faster when they have a search clause.

Please notice that using indexes is extremely recommended to improve Queries’ performance.