Database performance and index statistics

An SQL database not only needs indexes on important fields, so that a SQL query can run efficiently, but also information on how selective a certain index is, so that the best tactic can be chosen before executing an SQL statement.

For example, the selectivity of a Boolean field (yes/no) and a flat distribution is 50%, and the selectivity of an index on the ZIP code of an address is much higher. So if you query a Boolean field and a ZIP code at the same time, the selectivity tells the query analyzer that it is better to search for the ZIP code first and then for the Boolean field later.

For this purpose, so-called index statistics are created, which save information about the value distribution of the indexed columns. Index statistics – just like the indices themselves – need to be maintained regularly. Outdated or uncreated index statistics can lead to the associated indices not being used at all, which has a measurable and sometimes very large effect on the performance of certain SQL statements.

Recalculation of index statistics

When updating Vertec, the database convert recalculates the index statistics after the data is transferred to the new database. When restoring a backup, both database systems automatically recalculate the index statistics. For the rest of the time, the recalculation of the index statistics must be carried out regularly and it is best to automate.

Python feature for recalculation

As of Vertec 6.8, the Python feature vtcapp.renewdatabaseindexstatistics() is available, which recalculates the index statistics.

Scheduled task for recalculation

As of Vertec 6.8, there is a planned task of type Recalculation of index statistics in Vertec.

It executes the above-mentioned Python feature and automatically recalculates the index statistics overnight.

• By default, the scheduled task is delivered with the execution time every day at 23:30.  
  • The exact time is then rolled if it has other scheduled tasks during the time window, see article Planned Tasks.
• The scheduled task is activated by default with Firebird. On-Premises customers using Microsoft SQL Server can activate the task manually, unless an automation has already been set up on the MS SQL Server.

Customer-specific indices (On-Premises)

In Vertec databases, customer-specific indexes can be created after careful analysis and consideration. In order for indexes to be preserved after a database convert, you must use the prefix CIX_ start. They are created as follows:

CREATE [UNIQUE] INDEX indexname ON tablename (column1, column2, column3)

The syntax for Firebird and SQL for creating simple indexes is similar, but when additional information is added there are differences.

• For more information on creating indexes on Firebird databases, visit https://firebirdsql.org/refdocs/langrefupd20-create-index.html
• For more information about indexing SQL Server databases, visit https://docs.microsoft.com/de-de/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15

Example:

CREATE INDEX CIX_INVOICENUMBER ON RECHNUNG (NUMMER);

Creating indices can improve performance, but there are also risks. For example, inserting or updating a record takes more time if an index needs to be edited, and even with a SELECT index, an index does not always lead to a better result: for example, a field with good selectivity but many values like ““ or NULL may even have worse performance. Also, an index only works with a = comparison, not with a LIKE.

Before deciding to add a new index, you should test the effect on the entire application.