{"id":3312,"date":"2019-03-25T17:43:42","date_gmt":"2019-03-25T20:43:42","guid":{"rendered":"http:\/\/xexeu.elipse.com.br\/pt\/structure-query-language-sql-chapter-5-best-practices-for-setting-up-historics-and-queries\/"},"modified":"2020-02-12T15:49:39","modified_gmt":"2020-02-12T18:49:39","slug":"structure-query-language-sql-chapter-5-best-practices-for-setting-up-historics-and-queries","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/structure-query-language-sql-chapter-5-best-practices-for-setting-up-historics-and-queries\/","title":{"rendered":"Structure Query Language (SQL): Chapter 5 – Best practices for setting up Historics and Queries."},"content":{"rendered":"
\n

Historics <\/b>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.
\n<\/u><\/b><\/p>\n

Primary Key<\/p>\n

The Primary Key<\/b> 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.\u00a0 There are two types of primary keys: Simple <\/b>or Compound<\/b>.<\/p>\n

A Simple primary key<\/b> is a field that uniquely identifies each record in a table. A Compound primary key<\/b> 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.<\/p>\n

\"\"
\nFigure 1<\/b>: Primary Key icon<\/span>\"\"
\nFigure 2<\/b>: Primary Key<\/span><\/div>\n

Indexes <\/b><\/u><\/p>\n

Data can be accessed by SQL in two different ways:<\/p>\n

Via table scan:<\/b>
\nScans all pages\u00a0 from the beginning of the table and extracting the Query’s request.<\/p>\n

Via indexes:<\/b>
\nIndexes are fields (or groups of fields) previously sorted out by the database in order to improve the performance of queries using these indexes.\u00a0 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<\/i>). To insert an index into a historic, you can access its icon during the historic’s configuration, as seen below.<\/p>\n

\"\"
\nFigure 3<\/b>: Indexes icon<\/span>\"\"
\nFigure 4<\/b>: Indexes<\/span><\/div>\n

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 <\/b>field. If another field in the table also has a search clause, another index must be inserted.<\/p>\n

Instructions for setting up a historic and a query<\/u><\/b><\/p>\n

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:<\/p>\n

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

When using indexes and primary keys with Insert<\/i>, Delete<\/i>, and Update <\/i>commands, the following occurs:<\/p>\n