Firebird & Devart, how can we improve the performance?
Posted: 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;
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;