Update 'Blob sub_type 0' in FB 3/4/5 is so much slower than in FB 2.5

[PhotoSoft]

Hello, I am developing a large desktop application in C# .NET Framework 4.8, where I use Firebird 2.5. Some of my database clients have databases exceeding 100GB. I considered migrating to Firebird 4.0 for performance boost, but during testing, I noticed that image updates in the database are significantly slower.

The problem is easy to reproduce. You just need a simple table:

CREATE TABLE FILES
(
  ID Integer,
  BLOB_0 Blob sub_type 0 DEFAULT '',
  PRIMARY KEY (ID)
)

And a regular UPDATE statement:

UPDATE files SET blob_0=@blob_0 WHERE id=@id

I wrote a C# application specifically for this purpose, which performs a test by executing an UPDATE for 1000 rows with image of size 500KB. I’ve shared the source code here (testing code is in FirebirdClient.cs): https://github.com/PhotoSoft/FirebirdTest

I conducted tests on the following Firebird versions, installed with default settings on Windows 10:

• Firebird-2.5.9.27139_0_x64
• Firebird-3.0.11.33703_0_x64
• Firebird-4.0.4.3010-0-x64
• Firebird-5.0.0.1306-0-windows-x64

After uninstalling one version of Firebird and installing another, I rebooted the system. Then, in my application, I ran a test, ignoring the first 5 results because they could vary significantly. After that, the results became consistent.

The results I obtained are as follows:

• Firebird 2.5 Best execution time: 10s Max SSD usage: 48MB/s

• Firebird 3.0 Best execution time: 16s Max SSD usage: 31MB/s

• Firebird 4.0 Best execution time: 18s Max SSD usage: 29MB/s

• Firebird 5.0 Best execution time: 18s Max SSD usage: 29MB/s

To ensure consistency, I repeated the test on a laptop, and the performance regression was the same. As you can see, newer versions exhibit significantly lower disk utilization, resulting in longer UPDATE execution times.

What’s going on here? Is this some kind of issue in the newer Firebird versions?

[hvlad]

Didn't run your test yet, but in the source code I noted that you use connections pool:

https://github.com/PhotoSoft/FirebirdTest/blob/bbfb83249b61f3662dd979749fad9dee7860d91e/FirebirdClient.cs#L119

Is it correct to expect that UpdateTest() uses the same attachment that was created database (in CreateDatabase()) ? If yes, could you try without connctions pool ?

[PhotoSoft]

With Pooling = false; the results are exactly the same.

I performed additional tests for _Blob subtype 1 and Varchar(8000) between Firebird 2.5 and 4.0. The results are as follows:

Blob sub_type 1

CREATE TABLE FILES
(
  ID Integer,
  TEXT Blob sub_type 1 DEFAULT '',
  PRIMARY KEY (ID)
)

Update 1000 rows with text file 492KB:

• FB 2.5 - 10s and 48MB/s
• FB 4.0 - 18s and 29MB/s

Exactly the same results as with _Blob subtype 0.

Varchar(8000)

CREATE TABLE FILES
(
  ID Integer,
  TEXT VARCHAR(8000) DEFAULT '',
  PRIMARY KEY (ID)
)

Update 20000 rows with string 8000 chars:

• FB 2.5 - 19s and 69MB/s
• FB 4.0 - 23s and 51MB/s

As you can see, there is less disk usage in newer versions of Firebird, which is probably responsible for the longer update execution time.

[PhotoSoft]

Will someone take care of my issue report?

[hvlad]

I can confirm some slowdown while not as big as in your numbers. More investigations required, so don't expect for fast solution.