This topic considers basic stages of working with DataSet and ways to increase performance on each of these stages.
If your application performs Connect/Disconnect operations frequently, additional performance can be gained using pooling mode (TCustomDAConnection.Pooling = True). It reduces connection reopening time greatly (hundreds times). Such situation usually occurs in web applications.
If your application executes the same query several times, you can use the TCustomDADataSet.Prepare method or set the TDADataSetOptions.AutoPrepare property to increase performance. For example, it can be enabled for Detail dataset in Master/Detail relationship or for update objects in TDAUpdateSQL. The performance gain achieved this way can be anywhere from several percent to several times, depending on the situation.
To execute SQL statements a T:Devart.MyDac.TMySQL component is more preferable than TMyQuery. It can give several additional percents performance gain.
If the TCustomDADataSet.Options.StrictUpdate option is set to False, the RowsAffected property is not calculated and becomes equal zero. This can improve performance of query executing, so if you need to execute many data updating statements at once and you don't mind affected rows count, set this option to False.
In some situations you can increase performance a bit by using P:Devart.Dac.TDADataSetOptions.CompressBlobMode. You can also use TMyConnection.Options.Compress. Setting TMyTable.Options.UseHandler can give an additional performance under high server load.
You can also tweak your application performance by using the following properties of TCustomDADataSet descendants:
See the descriptions of these properties for more details and recommendations.
The Locate function works faster when dataset is locally sorted on KeyFields fields. Local dataset sorting can be set with the IndexFieldNames property. Performance gain can be large if the dataset contains a large number of rows.
Lookup fields work faster when lookup dataset is locally sorted on lookup Keys.
Setting the TDADataSetOptions.CacheCalcFields property can improve performance when locally sorting and locating on calculated and lookup fields. It can be also useful when calculated field expressions contain complicated calculations.
Setting the TDADataSetOptions.LocalMasterDetail option can improve performance greatly by avoiding server requests on detail refreshes. Setting the TDADataSetOptions.DetailDelay option can be useful for avoiding detail refreshes when switching master DataSet records frequently.
If your application updates datasets in the CachedUpdates mode, then setting the TCustomDADataSet.Options.UpdateBatchSize option to more than 1 can improve performance several hundred times more by reducing the number of requests to the server.
You can also increase the data sending performance a bit (several percents) by using Dataset.UpdateObject.ModifyObject, Dataset.UpdateObject, etc. Little additional performance improvement can be reached by setting the AutoPrepare property for these objects.
If you are about to insert a large number of records into a table, you should use the TDevart.MyDac.TMyLoader component instead of Insert/Post methods, or execution of the INSERT commands multiple times in a cycle. Sometimes usage of TDevart.MyDac.TMyLoader improves performance several times.