[RolphyReyes]

Saludos.
NO es por aguarle la fiesta a nuestro compañero donald shimoda pero este articulo es muy interesante sobre el uso de GUID como campo primario (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:
Código SQL [-] CREATE TABLE M ( ID INTEGER NOT NULL PRIMARY KEY, TXT VARCHAR(30));

Código SQL [-]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:
Código SQL [-] CREATE TABLE M ( ID BIGINT NOT NULL PRIMARY KEY, TXT VARCHAR(30));

Código SQL [-]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:
Código SQL [-] CREATE TABLE M ( ID CHAR(32) NOT NULL PRIMARY KEY, TXT VARCHAR(30));

Código SQL [-]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
Código SQL [-] 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.
Código SQL [-] 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

Código SQL [-]
 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.

Pagina: http://www.firebirdnews.org/?p=998