Subject Re: R: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2

Do not change to a SSD! Corruption will occur.

Em 27/09/2014 11:16, "Doychin Bondzhev doychin@... [firebird-support]" <> escreveu:

Hi Costantino,

I did some experimenting before one year and I found that Firebird is
much faster when you use page size = cluster size on the file system.

So if your file system is with 4K cluster I suggest to use page size of 4K.

This is very helpful when you have Forced Write = ON.

Performance gain with insert only scenario is more then 10-15% from 16K
page on Windows 7 with RAID 10.

another thing to look for is to try to minimize the number of
transactions you create.

Try to put as many as possible statements into single transaction. So
for this check do you use autocommit on every statement or you wrap all
statements executed while processing single file in one transaction.

Also when you process your lines in the input file try to group as many
as possible selects into single select.

for example:

select field1, filed2, filed3, field4 from table1 where field1 = ? and
field2 = ?

into :

select field1, filed2, filed3, field4 from table1 where (field1 = ? and
field2 = ?) or (field1 = ? and field2 = ?) or (field1 = ? and field2 =
?) ..........

this way you will check for multiple values at once and that means less
selects to execute on the database.

If you do your query on single field then you can use IN instead of =

Check also you have proper index setup on the tables.

Usually execution that is IO heavy does not get much better performance
by just changing the hardware. If you move from HDD to SSD this can
speed up much more but HDD performance is not very different in the last
10 years.

Also another thing to note is that for DB scenarios I prefer to use Read
Caching and no Write caching. This gives me better guarantee that I will
not end with broken database in case of power failure.

Have a nice day.

Doychin Bondzhev
dSoft-Bulgaria Ltd.
PowerPro - billing & provisioning solution for Service providers
PowerStor - Warehouse & POS
Mobile: +359888243116

[Non-text portions of this message have been removed]