|
Increasing Performance |
|
|
|
Increasing Performance |
|
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 TCustomDAUpdateSQL. The performance gain achieved this way can be anywhere from several percent to several times, depending on the situation.
To execute SQL statements a
Using DML Array parameters, combined with query preparing can give a considerable performance gain. For example if you need to perform an insert query plenty of times, it is recommended to do it the following way to get the best performance. At first, set parameter data types, and then prepare the query. After that, set param Lengths and fill them with values. Finally, execute the query. For instance:
OraSQL1.ParamByName('Param1').DataType := ftInteger;
OraSQL1.Prepare;
OraSQL1.ParamByName('Param1').Param[0].Length := 1000;
for i := 1 to 1000
OraSQL1.ParamByName('Param1').Param[0].ItemAsInteger[i] := 123;
OraSQL1.Execute;If you execute many different SELECT statements, you can gain additional performance by setting the TOraSQL.StatementCache property to True. This feature is available only with Oracle 9.2i and higher. The TOraSession.Options.StatementCache property should be set to True to use this feature. But using the StatementCache property you may easy step over maximum open cursors on Oracle server. And thus you must be attentive when using this option.
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 TCustomDADataSet.Options.CompressBlobMode. Sometimes using TOraDataSet.Options.DeferredLobRead can give some additional performance, because Lobs will be read when they are required. You can also set TOraSession.Options.OptimizerMode to adjust the fetch performance.
Oracle optimizer can be tuned to increase performance of SQL statements executing and data fetching.
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.
Specifying update SQL statements in a dataset improves performance because of omitting SQL statements generation and automatic preparation of internal updating datasets that are created for every kind of update SQL statements.
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 T:Devart.Odac.TOraLoader component instead of Insert/Post methods, or execution of the INSERT commands multiple times in a cycle. Sometimes usage of T:Devart.Odac.TOraLoader improves performance several times.