IBExpert Database Performance Newsletter

Did you ever thought about possibilities to improve your database performance? Sure, a Database System like Interbase or Firebird is able to speed up typical operations internally, but in a lot of cases, there are very easy but powerful improvements.

Here is one more example:

Use the right Datatype!

Due to some customers ideas, we wanted to know how many influence the changes between GUID and Int32 or Int64 Primary Keys will havein the database design regarding performance.

So we created 3 different databases on a windows machine.

Each will have two simple tables (m for master, d for detail).

Here is the DB structure for Int32 IDs:

CREATE TABLE M (
    ID   INTEGER NOT NULL PRIMARY KEY,
    TXT  VARCHAR(30));

CREATE TABLE D (
    ID    INTEGER  NOT NULL PRIMARY KEY,
    M_ID  INTEGER REFERENCES M(ID),
    TXT  VARCHAR(30));

Here is the DB structure for Int64 IDs:

CREATE TABLE M (
    ID   BIGINT NOT NULL PRIMARY KEY,
    TXT  VARCHAR(30));

CREATE TABLE D (
    ID    BIGINT  NOT NULL PRIMARY KEY,
    M_ID  BIGINT REFERENCES M(ID),
    TXT  VARCHAR(30));

Here is the DB structure for GUIDs:

CREATE TABLE M (
    ID   CHAR(32) NOT NULL PRIMARY KEY,
    TXT  VARCHAR(30));

CREATE TABLE D (
    ID    CHAR(32)  NOT NULL PRIMARY KEY,
    M_ID  CHAR(32) REFERENCES M(ID),
    TXT  VARCHAR(30));

For creating the GUID, we are using a UDF from www.ibexpert.com/download/udf/uuidlibv12.zip

DECLARE EXTERNAL FUNCTION GUID_CREATE
CSTRING(36) CHARACTER SET NONE
RETURNS PARAMETER 1
ENTRY_POINT 'fn_guid_create' MODULE_NAME 'uuidlib';


In the next step we will just show you how to create the stored procedure to generate the data in the GUID DB.

CREATE PROCEDURE INITDATA (ANZ INTEGER)
AS
declare variable m varchar(40);
declare variable d varchar(40);
declare variable dx integer;
begin
while (anz>0) do
begin
m=guid_create();
m=strreplace(m,'-','');
insert into m(id,txt) values (:m,current_timestamp);
dx=10;
while (dx>0) do
begin
select guid_create() from rdb$database into :d;
d=strreplace(d,'-','');
insert into d(id,txt,m_id) values (:d,current_timestamp,:m);
dx=dx-1;
end
anz=anz-1;
end
end


A Procedure to create the Integer ID Data is much easier using a generator. After we have created all 3 databases with the param 500000 (which means 500000 master and 5 milllion detail records are created), we disconnect and reconnect again to see that any cache influence will not change the results.

To do the typical SQL Operation, we start a select that joins all records from all tables

select count(*) from m join d on d.m_id=m.id

Here are the results over all

Operation/Info          Int32           Int64           GUID
DB Size                      505 MB     550 MB  1030 MB
INITDATA(500000)        271s            275s            420s
Backup                  49s             54s             90s
Restore                 124s            127s            144s
Select                  22s             22s             49s

Resume

The changes between Int64 and Int32 can almost be ignored, but the changes to a GUID is a problematic design. The integer datatypes will give you better performance.

To see more hints for your database, just open IBExpert´s menu Tools-Stored Procedure/Trigger/View Analyzer and press F9. This analyzes all objects and shows parts that do not use an index in red color. To modify these objects, just double click the line. A well designed database should have no red line.

This is not available in the Personal Edition, but the Trial Edition from www.ibexpert.com allows you free tests on you database for 45 days.

IBExpert Full Version gives you unlimited access to these performance tuning tools and is available for just 179 Euro on www.ibexpert.com.