Subject Re: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on Windows server 2012 R2
Author Svein Erling Tysvær
>Hi Sean, thanks for the contribution.
>Some answers to your requests:
>1) Logic of application.
>It is a Microfocus Cobol legacy application, with latest (2014) x64 runtime. The long time is obviously not relative to a simple sql command, but to the overall execution.
>We have developed a dedicated interface (a Delphi x64 service and dll), cobol uses to read from vision indexed cobol files, read into Firebird table if record exist and than
>insert the record. It is to populate new tables, in order to use a different application with firebird database. The same application runs on windows server 2003 32bit,
>windows server 2008 64 and SLES 11 SP1 x64. The interface and dll’s are the same, just like the cobol program.
>The problem is related to the fact that I expected a real big difference between old or very old hardware in RAID 1 configuration, and this brand new hardware, instead
>it gained only 20/30 %. Consider this: another application, that runs only in cobol environment, without database, has passed from 2 hrs to 15 minutes !!
>Just to say, that surely we can improve our legacy application or the Delphi interface. But if I compare the identical application, with very different machines, I see a
>little improvement dispite the big difference in hardware (Firebird is always 2.5)

I'm not surprised, I think Firebird SuperServer will only use one core.

I created a table:

CONSTRAINT PK_TEST PRIMARY KEY (ID) --for this test an important primary key

Then I executed the following query on the empty table:

execute block returns (i integer) as
declare variable i2 integer;
i = 0;
i2 = 0;
while (i < 1000000) do
i = i+1;
i2 = i2+i;
while (i2 > 100000) do
i2 = i2 - 99991;
update or insert into test(id, myint)
values (:i2, :i);

16.5 seconds later the query had looped and inserted or updated 1 millon times, 49999 rows where inserted, the rest of the times things were updated. Maybe you should add half a second for the commit afterwards. This on a computer that is a few years old and nothing special. The database is Firebird 2.5, don't remember whether it is 2.5.1 or 2.5.2.

Now, this is very different from your import from a text file. Still, I hope it is enough to show that 9 minutes to check and insert 35000 records is more than what is normally neccessary. You could either continue to try to improve the environment and maybe get the import to finish within 5 or 7 minutes after some further optimization. Or you could try to discover and fix the real problem through telling us more about what is actually going on (what does your SQL look like, what PLANs are used, which indexes are used, do you use prepared statements or create 35000 separate statements and how many transactions?) and hopefully get the import to finish in less than one minute.