Database Performance and Index Statistics

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

For example, the selectivity for a boolean (yes/no) field and a flat distribution is 50%. For an index on the ZIP code of an address, the selectivity is much higher. So if you query a postcode with a boolean field 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.

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 – must be maintained regularly. Outdated or not created index statistics can lead to the corresponding indices not being used at all, which has a measurable and sometimes very large effect on the performance of certain SQL statements.

In general, the following applies:

• The Vertec DB Konvert tries to recalculate the index statistics after transferring the data to the new database. Firebird always succeeds, SQL Server does not, see the article about the Convert with Microsoft SQL Server .
• When a backup is restored, both database systems recalculate the index statistics.

Customer-specific indices

In Vertec databases, customer-specific indexes can be created after careful analysis and consideration. In order to maintain indexes after a database convert, you must use the prefix CIX_ start.

Custom indices 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 more information is added, there are differences.

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

Example:

CREATE INDEX CIX_INVOICENUMBER ON INVOICE (NUMBER);

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 produce better results: for example, a field with good selectivity but many values like ““ or NULL at the same time may even perform worse. Also, an index only works for a = comparison, not for a LIKE.

Before deciding to add a new index, the effect of the entire application should be thoroughly tested.

Microsoft SQL Server

By default, SQL Server automatically recalculates the index statistics when a table changes “substantially,” i.e. by at least 10%.

This is not always enough. We recommend that the index statistics be compiled with a     Maintenance Job regularly. For this purpose, there is the Stored Procedure:

EXEC sp_updatestats;

which is scheduled or can simply be initiated by hand.

For migrations – i.e. when started with an empty Vertec database and migrating a lot of data – the SQL Server calculates the index statistics independently based on the 10% rule.

Firebird

Firebird does not have a method for manually recalculating index statistics like Microsoft SQL Server (see above).

For a Vertec database convert, the statistics are recalculated. This is done at each Vertec update.

However, during migrations – i.e. when started with an empty Vertec database and migrating a lot of data – the statistics are not calculated. In this case, the index statistics still need to be created. There are the following options:

• Backup – Restore with gbak: The gbak (see article Backup of Vertec data ) creates the indices at the end, after the data is in the database.
• Using a stored procedure: In the Firebird Manager program supplied by Vertec (IBQ.exe or IBOConsole.exe), a stored procedure can be started:
  • The Vertec Firebird databases contain a stored procedure named SP_VTC_UPDATESTATS :

Start with

EXECUTE PROCEDURE sp_vtc_updatestats;

And important: Only the commit performs the actual action:

• The procedure can also be scheduled as follows:

“C:\Program Files\Firebird\isql.exe” -user sysdba -password xyz localhost:\Program Files\Vertec\DB\Vertec.FDB -input C:\Program Files\Vertec\DB\indexstats.txt

In the file Indexstats.txt itself contains the following rows:

EXECUTE PROCEDURE SP_VTC_UPDATESTATS;
COMMIT;

• If the stored procedure is not there, you can create it as follows:

set term ^;

execute block
as
declare variable ix varchar(31);
begin
  for select rdb$indices.rdb$index_name
  from rdb$indices
  into ix
  do
  execute statement 'SET STATISTICS INDEX '||ix ;
end^

set term ;^

The code can simply be copied into the SQL Editor and click on the button as above

Execute and then on Commit clicked: