Windows vs Linux performance on bulk inserts

[Hamish Moffatt]

Hi,

I'm trying to debug a significant performance difference I see between
Linux/MacOS and Windows when bulk inserting records. Windows is 4-5x
slower. The hardware is similar - a few years old, plenty of RAM, NVMe
storage - certainly not 4x slower.


I have a process that bulks inserts records into a brand new database.
One table for example has 380k records to be inserted, with a primary
key, no other indices, and no triggers.

Each full INSERT INTO TABLE (COLUMNS....) VALUES (.....) statement is
prepared and executed separately, using the fbclient C API via the Qt
driver from C++.


All machines are using Firebird 5.0.3, embedded, with default
configuration. Default page size of 8k. Forced writes are disabled after
the database is created (gfix -write async).

I have disabled Windows Defender. I have tried reducing how often we
COMMIT during the bulk insert. I have tried increasing the page size. I
have disabled reserved space. None of this helped.

It's hardly using any CPU - appears to be waiting on I/O, based on what
I can see in the Visual Studio profiler.


What else can I check? Any suggestions?

Thanks,

Hamish

[Dimitry Sibiryakov]

'Hamish Moffatt' via firebird-support wrote 08.07.2026 10:23:
> What else can I check? Any suggestions?

Google suggests that BitLocker can be the problem.

--
WBR, SD.

[Ashley Labuschagne]

Hi Hamish 

Is this on Win 10 or Win 11? 

All the Best 
 ashley 

[Hamish Moffatt]

On 8/7/26 18:34, Ashley Labuschagne wrote:

Hi Hamish 

Is this on Win 10 or Win 11? 

Windows 11 Pro.

Hamish

[Hamish Moffatt]

That seems to be part of it. As an experiment, I set up a RAM disk
(using OSFMount) and created the database on there (and also set
FIREBIRD_TMP/FIREBIRD_LOCK), and it was quicker.

Still 3x Linux time though.  Specifically, the 380k records insert is taking

- Windows ~78 seconds

- Windows to unencrypted NTFS RAM disk ~58 seconds

- Linux ~18 seconds

- Mac ~20 seconds


The Linux and Mac systems also have encrypted disks (LUKS and FileVault,
respectively), fwiw.


thanks

Hamish

[Ashley Labuschagne]

Win 11 is a dud. 
Try same on Win 10 ? 

 -ashley 

On 2026/07/08 10:39, 'Hamish Moffatt' via firebird-support wrote:

Windows 11 Pro.

[Hamish Moffatt]

On 8/7/26 18:56, 'Hamish Moffatt' via firebird-support wrote:
> On 8/7/26 18:33, 'Dimitry Sibiryakov' via firebird-support wrote:
>> 'Hamish Moffatt' via firebird-support wrote 08.07.2026 10:23:
>>> What else can I check? Any suggestions?
>>
>>   Google suggests that BitLocker can be the problem.
>>
> That seems to be part of it. As an experiment, I set up a RAM disk
> (using OSFMount) and created the database on there (and also set
> FIREBIRD_TMP/FIREBIRD_LOCK), and it was quicker.
>
> Still 3x Linux time though.  Specifically, the 380k records insert is
> taking
>
> - Windows ~78 seconds
>
> - Windows to unencrypted NTFS RAM disk ~58 seconds
>
> - Linux ~18 seconds
>
> - Mac ~20 seconds

One more data point: I ran it on WSL2 on the same Windows machine (also
on the Bitlocker disk): 36 seconds


Hamish

[Pieter Bas Hofstede]

Have you tried firebird.conf settings like these (because they seem to have a different defaults per platform) :

MaxUnflushedWrites = -1 # default for posix (non-Windows)
MaxUnflushedWriteTime = -1 # default for posix (non-Windows)

Op woensdag 8 juli 2026 om 12:45:29 UTC+2 schreef ham...@risingsoftware.com:

[Hamish Moffatt]

On 9/7/26 16:56, Pieter Bas Hofstede wrote:
> Have you tried firebird.conf settings like these (because they seem to
> have a different defaults per platform) :
> MaxUnflushedWrites = -1 # default for posix (non-Windows)
> MaxUnflushedWriteTime = -1 # default for posix (non-Windows)
>

Thanks, it helps a bit. For the 380k record insert, time drops from 80
seconds to 58 seconds.


Hamsih

[Pieter Bas Hofstede]

And if you try to setup better firebird.conf settings? 

https://cc.ib-aid.com/dataguardLogin would generate better settings for you.

Maybe using bigger PageBuffers or MaxStatementCacheSize (as you prepare the same statement 380k times (your first message)?) could help

Op donderdag 9 juli 2026 om 13:03:30 UTC+2 schreef ham...@risingsoftware.com:

[Mark Rotteveel]

On 08-07-2026 10:23, 'Hamish Moffatt' via firebird-support wrote:
> I'm trying to debug a significant performance difference I see between
> Linux/MacOS and Windows when bulk inserting records. Windows is 4-5x
> slower. The hardware is similar - a few years old, plenty of RAM, NVMe
> storage - certainly not 4x slower.
>
>
> I have a process that bulks inserts records into a brand new database.
> One table for example has 380k records to be inserted, with a primary
> key, no other indices, and no triggers.
>
> Each full INSERT INTO TABLE (COLUMNS....) VALUES (.....) statement is
> prepared and executed separately, using the fbclient C API via the Qt
> driver from C++.

Do you mean that you are preparing a new statement for each row you
insert? Have you considered using a prepared statement, and the batch
API? (Though using batch inserts would require the C++/OO API.)

You might also want to consider using an external table to load the data
and insert with INSERT ... SELECT instead.

Mark
--
Mark Rotteveel

[Hamish Moffatt]

On 9/7/26 21:26, Pieter Bas Hofstede wrote:
> And if you try to setup better firebird.conf settings?
> https://cc.ib-aid.com/dataguardLogin would generate better settings
> for you.
>
> Maybe using bigger PageBuffers or MaxStatementCacheSize (as you
> prepare the same statement 380k times (your first message)?) could help
>

It's the same structure but the statement contains the values rather
than binding them. It's not easy for us to change.

thanks

Hamish

[Attila Molnár]

Hi!

some tips:

- performance-focused cores vs efficiency-oriented cores (P/E cores): check Firebird uses which cores; you can control Firebirs CPU usage; also check Windows power management

- IPv4 vs IPv6 : disabling IPv6 support on OS level might help

- file system block size vs DB page size : DB page size should integer multiple of FS block size

- if Windows is a DC, disc cache is turned off : https://dyemanov.blogspot.com/2012/03/firebird-vs-windows-file-system-caching.html?showComment=1342194083132#c1637827408911776301

[Lester Caine]

On 10/07/2026 08:58, Attila Molnár wrote:
>
> I'm trying to debug a significant performance difference I see between
> Linux/MacOS and Windows when bulk inserting records. Windows is 4-5x
> slower. The hardware is similar - a few years old, plenty of RAM, NVMe
> storage - certainly not 4x slower.

While I have a backup Firebird 5.0.3 running on the remaining windows
machine, I've not used windows as the main server platform for many
years now. Windows has always been a large factor slower then Linux
running the same database on the same hardware, and my commercial
systems often had a Linux server behind the windows machines that the
clients preferred as front facing access, but the load was low enough
that the windows machines coped most of the time.

Looking at my posts from the early 2010's I think that perhaps the
question is HAS windows actually had any improvement since NT, XP and
perhaps W7 days? Having to switch TO W7 on my council sites added the
need to take more care than on the XP machines with scheduled reboots to
keep things stable. My W10 machine has the same need, but then it's only
looked at perhaps 30 minutes a week :) Since that machine is not allowed
access to W11, it's never going to change now ...

--
Lester Caine
------------

[Hamish Moffatt]

On 9/7/26 22:46, 'Mark Rotteveel' via firebird-support wrote:
> On 08-07-2026 10:23, 'Hamish Moffatt' via firebird-support wrote:
>>
>> I have a process that bulks inserts records into a brand new
>> database. One table for example has 380k records to be inserted, with
>> a primary key, no other indices, and no triggers.
>>
>> Each full INSERT INTO TABLE (COLUMNS....) VALUES (.....) statement is
>> prepared and executed separately, using the fbclient C API via the Qt
>> driver from C++.
>
>
> Do you mean that you are preparing a new statement for each row you
> insert? Have you considered using a prepared statement, and the batch
> API? (Though using batch inserts would require the C++/OO API.)

Yes, each statement contains values in literals with no bound
parameters. The SQL is extracted from a working master db using
IBExpert, and we get one statement per row. It's not using much CPU at
all, so I don't think the prepare time is important. It seems to be all I/O.

It is about 2.5x faster if we put batches of 100 inserts into EXECUTE
BLOCK, which is a change we can implement fairly easily.


So far I have the following improvements

- Use embedded rather than XNET to connect - 40-50% faster

- Set MaxUnflushedWrites=-1, MaxUnflushedWriteTime=-1 - another 25%

- Batch inserts inside blocks - another 40% - and this helps on linux
too, though not as much


Still takes 3x longer on Windows, but it's bearable now.

Replying to some of the other suggestions: check P/E cores (Windows test
PC only has P cores), check IPv6 (we're using embedded), block size (16k
made it worse than 8k default), DC (it's Windows Pro).


thanks

Hamish

[Mark Rotteveel]

On 13-07-2026 07:23, 'Hamish Moffatt' via firebird-support wrote:
> On 9/7/26 22:46, 'Mark Rotteveel' via firebird-support wrote:
>> On 08-07-2026 10:23, 'Hamish Moffatt' via firebird-support wrote:
>>>
>>> I have a process that bulks inserts records into a brand new
>>> database. One table for example has 380k records to be inserted, with
>>> a primary key, no other indices, and no triggers.
>>>
>>> Each full INSERT INTO TABLE (COLUMNS....) VALUES (.....) statement is
>>> prepared and executed separately, using the fbclient C API via the Qt
>>> driver from C++.
>>
>>
>> Do you mean that you are preparing a new statement for each row you
>> insert? Have you considered using a prepared statement, and the batch
>> API? (Though using batch inserts would require the C++/OO API.)
>
>
> Yes, each statement contains values in literals with no bound
> parameters. The SQL is extracted from a working master db using
> IBExpert, and we get one statement per row. It's not using much CPU at
> all, so I don't think the prepare time is important. It seems to be all
> I/O.

That sounds like a scenario where inserting the data from the master
into an external table, and then using an external table with the same
definition and data file in your target database with `insert ... select
.. from <the external table>` would be a lot faster.

If you have direct access to the master from the target database or vice
versa, then using (FOR) EXECUTE STATEMENT ... ON EXTERNAL ... would also
perform better

Also the overhead of a prepare is a combination of I/O and CPU,
especially avoidable I/O and CPU, so just because the CPU is low, is not
a reason to not use a parameterized prepared statement.



--
Mark Rotteveel

[Vlad Khorsun]

Still takes 3x longer on Windows, but it's bearable now.

Replying to some of the other suggestions: check P/E cores (Windows test
PC only has P cores), check IPv6 (we're using embedded), block size (16k
made it worse than 8k default), DC (it's Windows Pro).

  Domain Controller explicitly disables disk write cache on physical drive with DC data. 

Regards,

Vlad

[Tommi Prami]

" That sounds like a scenario where inserting the data from the master
into an external table, and then using an external table with the same
definition and data file in your target database with `insert ... select
.. from <the external table>` would be a lot faster.  "

How this external table process makes bulk insert faster. It sounds like an extra step, but don't know enough to say more, than get this feeling from that it would not affect at all. 

Hope to hear why that is fastr, and happily stand corrected of the my guess.

-tee-

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/9dd35553-bbd8-411f-a713-651baaa0377an%40googlegroups.com.

[Mark Rotteveel]

On 13-07-2026 12:11, Tommi Prami wrote:
> How this external table process makes bulk insert faster. It sounds like
> an extra step, but don't know enough to say more, than get this feeling
> from that it would not affect at all.
>
> Hope to hear why that is fastr, and happily stand corrected of the my guess.

Firebird will reads and inserts the records in one stream, in the same
format it already uses internally, without having to parse, prepare and
execute a lot of statements.


Also, consider the current steps:

1. Master DB: Query data from master and generate/create SQL scripts
2. Target DB: Execute those SQL scripts

You'll now get

1. Master DB: create external table(s)
2. Master DB: insert into <external table> ... select ... from <source
table>
3. Target DB: create external table(s) referencing same file(s) as (1)
(or a copy)
4. Target DB: insert into <target table> ... select ... from <external
table>

Those may look like more steps, but then you're discounting the
complexity and work needed for and server overhead of those 2 "current"
steps.

Mark

--
Mark Rotteveel

[Tim Crawford]

I didn't see a response to modifying PageBuffers anywhere?

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/2580c06c-a6f8-4940-9fb1-53ad0ec3e619n%40googlegroups.com.

[Hamish Moffatt]

On 14/7/26 02:07, Tim Crawford wrote:
> I didn't see a response to modifying PageBuffers anywhere?

I don't see any change. I tried a few different values for
DefaultDbCachePages in databases.conf. The database file does not have
any value set in the header.


thanks

Hamish

[Tommi Prami]

> Have you tried firebird.conf settings like these (because they seem to
> have a different defaults per platform) :
> MaxUnflushedWrites = -1 # default for posix (non-Windows)
> MaxUnflushedWriteTime = -1 # default for posix (non-Windows)
>

Thanks, it helps a bit. For the 380k record insert, time drops from 80
seconds to 58 seconds.

My tests I did not see any speedup settings those  settings  to  -1.

Can some FB developers explain why disabling might give a speed boost. That sounds counter intuitive to me, I see the Unflushed writes as kind of cache. But now thinking about it, it is for me kind of redundant cache, because there is most likely always Filesystem cache in Windows, if not explicitly disabled.

And disabling those settings would make writing to the DB bit safer, or at least I see it this way.

-tee-

[Dmitry Yemanov]

These MaxUnflushed* parameters work only if ForcedWites = OFF for the
database. In this case, flushing is performed by OS but it happens
unpredictably. -1 means exactly this -- leave everything up to OS (read:
flush rarely). Any (reasonable) explicit value means "flush more often"
and of course this may affect the performance (note that it's the commit
time which is affected, not the insertion time).


Dmitry

[Hamish Moffatt]

On 13/7/26 16:51, 'Mark Rotteveel' via firebird-support wrote:
>
> That sounds like a scenario where inserting the data from the master
> into an external table, and then using an external table with the same
> definition and data file in your target database with `insert ...
> select .. from <the external table>` would be a lot faster.

Yes, this would be better.

Currently our workflow is: content developer works in our product to
generate new records, records are extracted to individual SQL INSERT
statements using IBExpert, committed to source control, and new master
database is generated. The database generation is the slow step and this
is mainly a developer experience issue.

We could change this to keep the master data in CSV files and process
them with your ext-table-gen for example.


Hamish