Firebird & Devart, how can we improve the performance?

Firebird & Devart, how can we improve the performance?

Postby LuizMoura » Sun 21 Aug 2011 04:14

Hi,
I have to migrate a system that was written using the BDE.
But I'm in big trouble!
The system can receive data updates daily. With thousands of new records.
Several tables, and started a test for one that works this way.

Real example of one table:
1 - Receives 4212 records from a file txt.
2 - It is verified through the contents of 3 fields, if the record already exists
3 - If the recording does exist, not performe INSERT and follows with read line new in TXt file
4 - if the record not exist make INSERT, into table of Firebird
5 - Follow this way until the end of the txt

Now my big problem.
With BDE this procedure takes only 2.6 seconds
With Firebird this procedure takes 47 seconds !
Earlier Firebird already took one minute and 10 seconds, look what I did to get in 47 seconds.

1- I put the connection as Prepared = true.
2- I put the tSqlQuery as Prepared = true.
3- I placed the three fields that are used for research, with the composite index, with simple indices, and also disabled, and the processing time is not changed appreciably.
4- The contents of these fields is quite varied, and everyone is type string.
5- Put the property getMetadata of datasets like false.
6- In the SQL statement in the dataset, do not use, * from , appoint field by field
7- And still import, several test I did, I noticed that there is not important a amount of records that is created or not.The main problem is that the research / verification of 4212 records

So I ask, the desktop databases are very fast of the client-server DBMS for this type of operation ?
Can you tell me if he still has some important procedure that I have not done.
The big problem is that this is only one of the tables that have to update every day, has another 11, with the DBE that it took a few seconds, with Firebird looks like it will take several minutes.!

Grateful if you can help.
Regarts,

Luiz Moura
Brazil


//Fragment of source code search
with dmAcxI.cdsPrdfat1 do begin
close;
fetchparams;
params.parambyname('NFISCAL').AsString := sFatNfiscal ;
params.parambyname('REF').AsString := sMostra;
params.parambyname('CSUBTT').AsString := sSubTt ;
open;
end;
LuizMoura
 
Posts: 7
Joined: Sun 21 Aug 2011 02:47

Postby AndreyZ » Tue 23 Aug 2011 10:21

Hello,

Our tests show that our dbExpress driver for InterBase & Firebird is much faster than BDE. That's why please try creating a small sample that demonstrates the problem with performance and send it to andreyz*devart*com, including a script to create a table.
AndreyZ
 

Postby LuizMoura » Fri 26 Aug 2011 01:40

yes I'll provide it.
grateful
LuizMoura
 
Posts: 7
Joined: Sun 21 Aug 2011 02:47

AndreyZ, Thanks for the help

Postby LuizMoura » Sat 17 Sep 2011 02:10

AndreyZ, very Thanks for the help !

To begin working with the Firebird, I bought several books to study, but none wrote that the correct use of transactions to be very important for performance.
"When have routines that make massive changes in the data."

Configured as:
tr.IsolationLevel := xilREADCOMMITTED;

Now my test routines with the Firebird / Devart have a performance fully compatible with other DBMSs such as MySQL and Oracle, and more fast than PostgreSQL.

Thanks.
LuizMoura
 
Posts: 7
Joined: Sun 21 Aug 2011 02:47

Postby AndreyZ » Mon 19 Sep 2011 08:56

It's good to see that the problem was solved. If any other questions come up, please contact us.
AndreyZ
 


Return to dbExpress driver for InterBase & Firebird