MS SQL Server Bulk Insert configuration

SWW · Post by **SWW** » Fri May 20, 2005 9:17 am

We are running jobs which bulk insert several tables ranging from 5 million to 20 million records each day. For narrow tables we average a load rate of about 750 records/sec. The row buffers are set to 512Kb, In process. The commit size is 10000. The source data is remote to the target server across a T1 line. How can we improve overall performance on the bulk inserts? Are there SQL Server parameters we should also "tweak" on the target server?

roy · Post by **roy** » Mon May 23, 2005 2:35 am

Hi,
As said before (search this forum for complete coverage)
best performance will be using native bulk load.

is your SQLServer on the same machine as your DS Server?
if so try invoking it via command line or user defined SQL.

Tweeking SQLServer parameters is a question for your DBAs.

I would storngly recommend that you'll test the load performance using SQLServer's bulk load mechanism without using DS to get the aproximate thru-put you can get.
In other words if SQLSerer can only give you 750 rows/second loading from a sequential ascii file there is no real chance DS will do it faster.
(depending both are configured for maximum performance).

IHTH,