Obviously the logic of my application is not the best, but it needs to pass
through a special interface, that transforms classic cobol read/write
routines, in sql statements. This brings to the need to have a single
commit after a single insert command of a single record. I know this is not
good, but knowing this and knowing that I cannot change this, I only want to
know if I can get the best for my Firebird Installation of 2.5.3 SuperServer
onto Windows Server 2012 R2 x64.
I'm trying several settings, some others have suggested in this thread.
About your test (thanks for that), I have created the table, than copied and
pasted the SQL loop, but I get this error:
Starting transaction...
Preparing statement: SELECT r.ID, r.MYINT
FROM TEST r
Statement prepared (elapsed time: 0.001s).
Field #01: TEST.ID Alias:ID Type:INTEGER
Field #02: TEST.MYINT Alias:MYINT Type:INTEGER
PLAN (R NATURAL)
Executing statement...
Statement executed (elapsed time: 0.000s).
47 fetches, 0 marks, 0 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 11 index, 0 seq.
Delta memory: 12064 bytes.
Total execution time: 0.021s
Script execution finished.
Preparing statement: execute block returns (i integer) as
declare variable i2 integer
Error: *** IBPP::SQLException ***
Context: Statement::Prepare( execute block returns (i integer) as
declare variable i2 integer )
Message: isc_dsql_prepare failed
SQL Message : -104
Invalid token
Engine Code : 335544569
Engine Message :
Dynamic SQL Error
SQL error code = -104
Unexpected end of command - line 2, column 21
-----Messaggio originale-----
Da: firebird-***@yahoogroups.com
[mailto:firebird-***@yahoogroups.com]
Inviato: domenica 28 settembre 2014 16:54
A: firebird-***@yahoogroups.com
Oggetto: Re: [firebird-support] How to improve Firebird 2.5.3 Disk I/O on
Windows server 2012 R2
Post by 'Costantino Molinari' ***@tiscali.it [firebird-support]Hi Sean, thanks for the contribution.
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.
Post by 'Costantino Molinari' ***@tiscali.it [firebird-support]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.
Post by 'Costantino Molinari' ***@tiscali.it [firebird-support]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 !!
Post by 'Costantino Molinari' ***@tiscali.it [firebird-support]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:
CREATE TABLE TEST
(
ID INTEGER NOT NULL,
MYINT INTEGER,
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;
begin
i = 0;
i2 = 0;
while (i < 1000000) do
begin
i = i+1;
i2 = i2+i;
while (i2 > 100000) do
i2 = i2 - 99991;
update or insert into test(id, myint)
values (:i2, :i);
end
suspend;
end
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.
HTH,
Set
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item on the
main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at
http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
------------------------------------
Posted by: "Costantino Molinari" <***@tiscali.it>
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-***@yahoogroups.com
firebird-support-***@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/