Maximum Number Of Records Per Second That Can Be Inserted Into SQLServer 2000.

Summary: Maximum number of records per second that can be inserted into
SQLServer 2000.

I am trying to insert hundreds (preferably even thousands) of records
per second in to SQLServer table (see below) but I am getting the
following error in the Windows Event Viewer Application log file:

"Insufficent Memory......"

And very few records were inserted and no errors where sent back via
the JDBC.

By removing the indexes on the table we have stopped getting the error
message and have managed to load the table at 300 records per second.
However I have couple of questions:

1) Are the indexes definitely to blame for this error and is there
anyway of getting around this problem i.e. keeping the indexes in place
when inserting?

2) How should I configure SQLServer to maximise the speed of
inserts?

3) What is the limiting factor for inserting into SQLServer?

4) Does anyone know of any metrics for inserting records? At want
point should we consider load balancing across DBs.



I currently populate 1.6 million records into this table. Once again
thanks for the help!!


CREATE TABLE [result] (

[id] numeric(20,0) NOT NULL,

[iid] numeric(20,0) NOT NULL,

[sid] numeric(20,0) NOT NULL,

[pn] varchar(30) NOT NULL,

[tid] numeric(20,0) NOT NULL,

[stid] numeric(6,0) NOT NULL,

[cid] numeric(20,0) NOT NULL,

[start] datetime NOT NULL,

[ec] numeric(5,0) NOT NULL,

)

GO

CREATE INDEX [ix_resultstart]

ON [dbo].[result]([start])

GO

CREATE INDEX [indx_result_1]

ON [dbo].[result]([id], [sid], [start], [ec])

GO

CREATE INDEX [indx_result_3]

ON [dbo].[result]([id], [sid], [stid], [start])

GO

CREATE INDEX [indx_result_2]

ON [dbo].[result]([id], [sid], [start])

GO