Possibility of Improving the speed with which CDS.Applyupdates inserts record ...
Posted: Wed 06 Jun 2007 11:47
Hi there,
we are in a situation where we have to insert several hundred records as fast as possible.
We don't use a Remote-AppServer for the ClientDataSet, just a CDS, a SQLDataSet and a TSQLConnection.
With the usual way (Appending records at a CDS and call CDS.ApplyUpdates) we found out that it seems that the insertstatements generated are processed one after one. The TSQLConnection makes it possible to log every SQL-Command and it looks like there is a callback from the dbExpress-Driver fired to call this event. So we assume that this way every insert-statement is executing one after one.
Than we found out that generating Insert-Statements 'barefoot' and execute them with Connection.ExecuteDirect is extremly faster than using the approach described above. (300 Insert Operations into a mysql-Sever
in the network (outthere) within 0.3s, the approach above takes nearly 50s).
My question now is, is there are switch (within dbExpress or the CDS/SQLDataSet) to transmit the gerated Insert-Statements at once or is the last approach just so much faster?
To Put it another way: of course we would like to keep using our application framework for inserting records, but when executing raw insert-statements in a bunch is so much faster we think about going this way.
Thanks for helping,
with regards,
Christian
we are in a situation where we have to insert several hundred records as fast as possible.
We don't use a Remote-AppServer for the ClientDataSet, just a CDS, a SQLDataSet and a TSQLConnection.
With the usual way (Appending records at a CDS and call CDS.ApplyUpdates) we found out that it seems that the insertstatements generated are processed one after one. The TSQLConnection makes it possible to log every SQL-Command and it looks like there is a callback from the dbExpress-Driver fired to call this event. So we assume that this way every insert-statement is executing one after one.
Than we found out that generating Insert-Statements 'barefoot' and execute them with Connection.ExecuteDirect is extremly faster than using the approach described above. (300 Insert Operations into a mysql-Sever
in the network (outthere) within 0.3s, the approach above takes nearly 50s).
My question now is, is there are switch (within dbExpress or the CDS/SQLDataSet) to transmit the gerated Insert-Statements at once or is the last approach just so much faster?
To Put it another way: of course we would like to keep using our application framework for inserting records, but when executing raw insert-statements in a bunch is so much faster we think about going this way.
Thanks for helping,
with regards,
Christian