4

I've got a DB Express TSimpleDataset connected to a Firebird database. I've just added several thousand rows of data to the dataset, and now it's time to call ApplyUpdates.

Unfortunately, this results in several thousand database hits as it tries to INSERT each row individually. That's a bit disappointing. What I'd really like to see is the dataset generate a single transaction with a few thousand INSERT statements in it and send the whole thing at once. I could set that up myself if I had to, but first I'd like to know if there's any method for it built in to the dataset or the DBX framework.

Mason Wheeler
  • 82,511
  • 50
  • 270
  • 477
  • ClientDataSets, or rather, DataSetProviders start a transaction only if there is no open transaction already (`.SqlConnection.BeginTransaction`). I'm a little confused though, I thought an 'ApplyUpdates' started 'one' transaction only. – Sertac Akyuz Jun 06 '11 at 19:01
  • @Sertac: Not sure about any of that--I haven't looked into it too much--but I do know that if I just call ApplyUpdates, it ends up sending each `INSERT` statement individually, which adds tons of overhead and slows things down severely. I'm basically trying to get a bulk insert job here. – Mason Wheeler Jun 06 '11 at 19:09
  • Ok, not related with transactions then.. As far as I can trace, ultimately, the 'UpdateTree' of the 'CustomResolver' of the 'DataSetProvider' loops through the 'Delta' generating a statement for each record. I can see no way out of that.. – Sertac Akyuz Jun 06 '11 at 19:29

1 Answers1

1

Don't know if it is possible with a TSimpleDataset (never used it), but surely you can if you use a TClientDataset + TDatasetProvider + <put your db dataset here>. You can write a BeforeUpdateRecord to handle the apply process yourself. Basically, it allows you to bypass the standard apply process, access the dataset delta with changes made to records, and then use your own code and components to apply changes to the database. For example you could call stored procedures to modify data, and so on.

However, there is a difference between a transaction and what is called "array DML", "bulk insert" or the like. Even if you use a single transaction (and an "apply" AFAIK happens in a single transaction), within the transaction you may still need to send "n" INSERTs. Some databases supports a way of sending a single INSERT (or update, delete) with an array of parameters to be inserted, reducing the number of single statements to be used - but that may be very database specific and AFAIK dbExpress/Datasnap do not support it - you still could use the BeforeUpdateRecord event to take advantage of specific database capabililties.