Problem: Bulk inserts into InterBase database are slow Solution: There are a number of things that can cause bulk inserts to be slow. 1) Doing a commit after each insert. This is the general case when bulk inserts are being performed with a BDE enabled tool (Delphi, C++Builder, ...). The workaround is to execute multiple (if not all) inserts under the same transaction. With BDE applications this will require that the SQLPASSTHRU MODE parameter be changed. This is a parameter set in the BDE alias (can be overridden with a TDatabase). The default setting for this parameter is SHARED AUTOCOMMIT. This setting tells the BDE to start/commit a transaction for each SQL statement that is executed. To allow multiple SQL statements to be executed under a single transaction changed the SQLPASSTHRU MODE parameter to one of the other settings (SHARED NOAUTOCOMMIT or NOT SHARED) and control the transactions manually through the use of a TDatabase component. 2) Having lots of expensive constraints can cause bulk inserts to be slow. All constraints must be loaded into memory when the inserts are performed. The presence of multiple constraints may cause excessive disk thrashing to allow for the proper constraints to be in memory when they are required. A workaround for this would be to disable the non-essential constraints during the bulk insert. 3) Having lots of indexes will slow down bulk inserts. For each insert into the database every associated index must be updated to reflect the new record. To workaround this issue inactivate all non-essential indices and re-activate the index when finished. 4) Having indexes with massive numbers of duplicate values will slow down an insert. When the index is to be updated it must be traversed to find the correct location for the new record to be inserted into the index. If there are a massive number of duplicate values the traversal will take a long time to find the correct location for the index insert. If your indexes do have a massive number of duplicate values you may need to evalutate the necessity of the index. Depending on how many records in the indexed table it may be more effiicient to perform the queries without the index. 5) A performance degradation may be incurred if each insert statement is prepared and executed. Each prepare causes the request to be compiled into something that will be executed on the server. This adds extra work that is not required. If you are using an execute immediate function to perform the SQL insert you are incurring this extra overhead. A more efficient way to perform the bulk insert is to prepare the statement once and just execute it multiple times with different input parameters.
Last Modified: 02-OCT-00