community.borland.com

Article #25274: Bulk Inserts with InterBase

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