Biblioteca

Negative impact of indices to INSERT, UPDATE and DELETE performance in Firebird SQL

In general, indices are necessary for any serious database, since they are critical to speed up queries with WHERE clause (SELECT, UPDATE. MERGE, DELETE, etc). However, every index has the cost, and the cost is obvious when we compare the speed INSERT/UPDATE/DELETE operations on indexed and non-indexed fields.

In this article we will demonstrate the impact of the index with a few unique keys to the operations' speed.

Test database

CREATE DATABASE “E:\TESTFIREBIRDINDEX.FDB” USER “SYSDBA” PASSWORD “masterkey”;
CREATE TABLE TABLEIND1 (
    I1            INTEGER NOT NULL PRIMARY KEY,
    NAME          VARCHAR(250),
    MANORWOMAN  SMALLINT
);
CREATE GENERATOR G1;
SET TERM ^ ;

create or alter procedure INS1MLN
returns (
    INSERTED_CNT integer)
as
BEGIN
inserted_cnt = 0;
WHILE (inserted_cnt <1000000) DO
BEGIN
 Insert into tableind1(i1, name, manorwoman) values(gen_id(g1,1), 'TEST name', (:inserted_cnt - (:inserted_cnt/2)*2));
 inserted_cnt=inserted_cnt+1;
END
suspend;
END^
SET TERM ; ^
GRANT INSERT ON TABLEIND1 TO PROCEDURE INS1MLN;
GRANT EXECUTE ON PROCEDURE INS1MLN TO SYSDBA;
COMMIT;