>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:
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
<*> 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:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
