• LOGIN
  • No products in the cart.

Delphi development in FreeBSD. Increase database performance

This article is aimed mostly on Delphi developers who create their applications in FreeBSD. Database performance is very important thing to consider when you craft your database application. Today I’ll try to describe you the way of how to increase performance of your database.

In this article I’ll use Delphi data access components for SQL Server, PostgreSQL, MySQL, SQLite, Interbase, Oracle. You can download them and try for your self. These components have a number of unique features, which favorably differ Devart Data Access Components from other solutions. The main advantage is Direct Mode that allows direct connection to database avoiding usage of any client library. This approach simplifies significantly Desktop applications deployment to end-user, since there is no more need to install and configure additional libraries required for work with databases. When developing applications for iOS and Android, the main issue is non-existing native client software for the majority of DBMSs, and the only way to work with them is Direct Mode.

It is obvious that the amount of information processed by modern databases is steadily growing nowadays. In this regard, there is an acute problem of database performance. Data insert, update and delete operations must be performed as fast as possible. Hereupon, Devart components provides several capabilities to speed up large data processing. So, for example, to insert large pieces of information in the database, there is the Loader component. Unfortunately, Loader allows data insertion only – it is impossible to update or delete data with its functionality.

The new version of Devart Data Access Components provides the new mechanism to process large amounts of data — Batch Operations. The essence of the approach is execution of a single parametrized SQL query that modifies data. The multiplicity of changes is achieved by the fact that parameters of such query are not single values, but a whole array of values. Such an approach greatly increases the execution speed of data operations. Moreover, in contrast to using Loader, Batch operations can be used not for insertion only, but for updating and deleting data as well.

If we are talking about loading prepared data to a database only, the developer can use a component like Loader. If the prepared data also serves for database modification or for deletion of a large amount of data – Batch operations will be useful at this.

1. Batch_Test table generating scripts

To explore the capabilities of Batch operations, let’s create a table with fields of the most popular data types: an integer field, a field with floating point, a text field, and a date-storing field.  Give the table a name like Batch_Test. While generating the Batch_Test table, let’s try to stick the SQL syntax with minimum of specific features of each supported DBMS. Below are samples of scripts required to generate the Batch_Test table:

For Oracle :

CREATE TABLE BATCH_TEST

(

  ID         NUMBER(9,0),

  F_INTEGER  NUMBER(9,0),

  F_FLOAT    NUMBER(12,7),

  F_STRING   VARCHAR2(250),

  F_DATE    DATE,

  CONSTRAINT PK_BATCH_TEST PRIMARY KEY (ID)

)

For MS SQL Server

CREATE TABLE BATCH_TEST

(

  ID        INT,

  F_INTEGER INT,

  F_FLOAT   FLOAT,

  F_STRING  VARCHAR(250),

  F_DATE    DATETIME,

  CONSTRAINT PK_BATCH_TEST PRIMARY KEY (ID)

)

For PostgreSQL :

CREATE TABLE BATCH_TEST

(

  ID        INTEGER,

  F_INTEGER INTEGER,

  F_FLOAT   DOUBLE PRECISION,

  F_STRING  VARCHAR(250),

  F_DATE    DATE,

  CONSTRAINT PK_BATCH_TEST PRIMARY KEY (ID)

)

For InterBase :

CREATE TABLE BATCH_TEST

(

  ID        INTEGER NOT NULL PRIMARY KEY,

  F_INTEGER INTEGER,

  F_FLOAT   FLOAT,

  F_STRING  VARCHAR(250),

  F_DATE    DATE

)

For MySQL :

CREATE TABLE BATCH_TEST

(

  ID        INT,

  F_INTEGER INT,

  F_FLOAT   FLOAT,

  F_STRING  VARCHAR(250),

  F_DATE    DATETIME,

  CONSTRAINT PK_BATCH_TEST PRIMARY KEY (ID)

)

For SQLite :

CREATE TABLE BATCH_TEST

(

  ID         INTEGER,

  F_INTEGER  INTEGER,

  F_FLOAT    FLOAT,

  F_STRING  VARCHAR(250),

  F_DATE    DATETIME,

  CONSTRAINT PK_BATCH_TEST PRIMARY KEY (ID)

)

2. Batch operations execution

So, the Batch_Test table is created. Let’s populate it with data. First, we will insert one record to Batch_Test. In our case, a traditional single-record inserting query will look like the following:

INSERT INTO BATCH_TEST VALUES (:ID, :F_INTEGER, :F_FLOAT, :F_STRING, :F_DATE),

where :ID, :F_INTEGER, :F_FLOAT, :F_STRING, :F_DATE — query parameters that will be filled in with appropriate values.

When a simple insertion operation is used, the query parameter values look as follows:

Parameters

:ID

:F_INTEGER

:F_FLOAT

:F_STRING

:F_DATE

1

100

2.5

String Value 1′

01.09.2015

Thus, after execution of the following code:

uses DateUtils;

begin

  // describe SQL query 

  Query1.SQL.Text := ‘INSERT INTO BATCH_TEST VALUES (:ID, :F_INTEGER, :F_FLOAT, :F_STRING, :F_DATE)’;

     // declare parameter types passed to the query:

     Query1.Params[0].DataType := ftInteger;

     Query1.Params[1].DataType := ftInteger;

     Query1.Params[2].DataType := ftFloat;

     Query1.Params[3].DataType := ftString;

     Query1.Params[4].DataType := ftDateTime;

     // set parameter values

     Query1.Params[0].AsInteger := 1;

     Query1.Params[1].AsInteger := 100;

     Query1.Params[2].AsFloat := 2.5;

     Query1.Params[3].AsString := ‘Value 1’;

     Query1.Params[4].AsDateTime := EncodeDateTime(2015, 09, 01, 0, 0, 0, 0);

  end;

  Query1.Execute;

end;

a record will be added to the BATCH_TEST table.

When using Batch operations, the query and its parameters remain unchanged. However, parameter values will be enclosed in an array:

Parameters

:ID

:F_INTEGER

:F_FLOAT

:F_STRING

:F_DATE

1

100

2.5

String Value 1′

01.09.2015

2

200

3.15

String Value 2′

01.01.2000

3

300

5.08

String Value 3′

09.09.2010

4

400

7.5343

String Value 4′

10.10.2015

5

500

0.4555

String Value 5′

01.09.2015

Now, 5 records are inserted into the table at a time on query execution.

How to implement a Batch operation in the code?

3. Batch INSERT operation sample

Let’s try to insert 1000 rows to the BATCH_TEST table using a Batch Insert operation.

First, we describe the inserting data:

  • we will change the value of the primary key (the ID field) from 1 to 1000;
  • the value of the F_INTEGER field will equal the sum of the ID field value and a number 2000;
  • F_FLOAT will contain the ID value divided by 12;
  • the F_STRING text field will contain a string ‘Values’ with the ID field value;
  • the F_DATE field will take the current date.

var

  i: Integer;

begin

  // describe the SQL query

  Query1.SQL.Text := ‘INSERT INTO BATCH_TEST VALUES (:ID, :F_INTEGER, :F_FLOAT, :F_STRING, :F_DATE)’;

   // define the parameter types passed to the query :

  Query1.Params[0].DataType := ftInteger;

  Query1.Params[1].DataType := ftInteger;

  Query1.Params[2].DataType := ftFloat;

  Query1.Params[3].DataType := ftString;

  Query1.Params[4].DataType := ftDateTime;

   // specify the array dimension:

  Query1.Params.ValueCount := 1000;

   // populate the array with parameter values:

  for i := 0 to Query1.Params.ValueCount – 1 do begin

    Query1.Params[0][i].AsInteger := i + 1;

    Query1.Params[1][i].AsInteger := i + 2000 + 1;

    Query1.Params[2][i].AsFloat := (i + 1) / 12;

    Query1.Params[3][i].AsString := ‘Values ‘ + IntToStr(i + 1);

    Query1.Params[4][i].AsDateTime := Now;

  end;

   // insert 1000 rows into the BATCH_TEST table

  Query1.Execute(1000);

end;

This command will insert 1000 rows to the table with one SQL query using the prepared array of parameter values. The number of inserted rows is defined in the Iters parameter of the Execute(Iters: integer; Offset: integer = 0) method. In addition, you can pass another parameter – Offset (0 by default) – to the method. The Offset parameter points the array element, which the Batch operation starts from.

We can insert 1000 records into the BATCH_TEST table in 2 ways.

All 1000 rows at a time:

Query1.Execute(1000);

2×500 rows:

// insert first 500 rows

Query1.Execute(500, 0);

// insert next 500 rows

Query1.Execute(500, 500);

500 rows, then 300, and finally 200:

// insert 500 rows

Query1.Execute(500, 0);

// insert next 300 rows starting from 500

Query1.Execute(300, 500);

// insert next 200 rows starting from 800

Query1.Execute(200, 800);

4. Batch UPDATE operation sample

   Using Batch operations, you can modify all the previously inserted 1000 rows in the same easy way. Thus, your BATCH_TEST table fields will take the following values:

  • the F_INTEGER field will be reduced by 2000;
  • F_FLOAT will be updated by the ID value divided by 100;
  • the F_STRING text field will contain a string ‘New Values’ with the ID value;
  • the F_DATE field will still store the date.

var

  i: Integer;

begin

  // describe the SQL query

  Query1.SQL.Text := ‘UPDATE BATCH_TEST SET F_INTEGER=:F_INTEGER, F_FLOAT=:F_FLOAT, F_STRING=:F_STRING, F_DATE=:F_DATE WHERE ID=:OLDID’;

   // define parameter types passed to the query:

  Query1.Params[0].DataType := ftInteger;

  Query1.Params[1].DataType := ftFloat;

  Query1.Params[2].DataType := ftString;

  Query1.Params[3].DataType := ftDateTime;

  Query1.Params[4].DataType := ftInteger;

   // specify the array dimension:

  Query1.Params.ValueCount := 1000;

   // populate the array with parameter values:

  for i := 0 to 1000 – 1 do begin

    Query1.Params[0][i].AsInteger := i – 2000 + 1;

    Query1.Params[1][i].AsFloat := (i + 1) / 100;

    Query1.Params[2][i].AsString := ‘New Values ‘ + IntToStr(i + 1);

    Query1.Params[3][i].AsDateTime := Now;

    Query1.Params[4][i].AsInteger := i + 1;

  end;

   // update 1000 rows in the BATCH_TEST table

  Query1.Execute(1000);

end;

5. Batch DELETE operation sample

Deleting 1000 rows from the BATCH_TEST table looks like the following operation:

var

  i: Integer;

begin

  // describe the SQL query

  Query1.SQL.Text := ‘DELETE FROM BATCH_TEST WHERE ID=:ID’;

   // define parameter types passed to the query:

  Query1.Params[0].DataType := ftInteger;

   // specify the array dimension

  Query1.Params.ValueCount := 1000;

   // populate the arrays with parameter values

  for i := 0 to 1000 – 1 do

    Query1.Params[0][i].AsInteger := i + 1;

   // delete 1000 rows from the BATCH_TEST table

  Query1.Execute(1000);

end;

 In this case, we will use a single parameter :ID that contains the value of the primary key.

6. Performance comparison

The example with BATCH_TEST table allows to analyze execution speed of normal operations with a database and Batch operations:

DAC Name

Operation Type

25 000 records

Standard Operation (sec.)

Batch Operation (sec.)

ODAC / UniDAC (with OracleUniProvider)

Insert

17.64

0.59

Update

18.28

1.20

Delete

16.19

0.45

LiteDAC / UniDAC (with SQLiteUniProvider)

Insert

2292

0.92

Update

2535

2.63

Delete

2175

0.44

PgDAC / UniDAC (with PostgreSQLUniProvider)

Insert

346.7

1.69

Update

334.4

4.59

Delete

373.7

2.05

IBDAC / UniDAC (with InterBaseUniProvider)

Insert

55.4

3.03

Update

81.9

3.58

Delete

61.3

0.91

MyDAC / UniDAC (with MySQLUniProvider)

Insert

1138

11.02

Update

1637

26.72

Delete

1444

17.66

SDAC / UniDAC (with SQLServerUniProvider)

Insert

19.19

3.09

Update

20.22

7.67

Delete

18.28

3.14

The less, the better.

It should be noted, that the retrieved results may differ when modifying the same table on different database servers. This is due to the fact that operations execution speed may differ depending on the settings of a particular server, its current workload, throughput, network connection, etc.

Thing you shouldn’t do when accessing parameters in Batch operations!

When populating the array and inserting records, we accessed query parameters by index. It would be more obvious to access parameters by name:

  for i := 0 to 9999 do begin

  Query1.Params.ParamByName(‘ID’)[i].AsInteger := i + 1;

  Query1.Params.ParamByName(‘F_INTEGER’)[i].AsInteger := i + 2000 + 1;

  Query1.Params.ParamByName(‘F_FLOAT’)[i].AsFloat := (i + 1) / 12;

  Query1.Params.ParamByName(‘F_STRING’)[i].AsString := ‘Values ‘ + IntToStr(i + 1);

  Query1.Params.ParamByName(‘F_DATE’)[i].AsDateTime := Now;

end;

However, the parameter array would be populated slower, since you would have to define the ordinal number of each parameter by its name in each loop iteration. If a loop is executed 10000 times –performance loss can become quite significant.

I hope this article was helpful for you. If you want to redo these tests please download data access components for any database you like. If you have any questions about article or components please forward them to the components developers forum

February 19, 2016

1
Leave a Reply

avatar
1 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
0 Comment authors
In Other BSDs for 2016/02/27 – DragonFly BSD Digest Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  Subscribe  
newest oldest most voted
Notify of
trackback

[…] Delphi development in FreeBSD. […]

© HAKIN9 MEDIA SP. Z O.O. SP. K. 2013