Posted by Erland Sommarskog on 10/02/86 11:44

(JSParker1@hotmail.co.uk) writes:
> 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.

1) Indexes does add overhead to inserts, that cannot be denied.

2) That depends a little on the answer to the question you did not ask.
But a standard reply would be: you shouldn't.

3) A lot of things: network, CPU, disk etc.

4) I guess that st some point, it may pay off to set up partitioned
views over partitioned servers, but with 1.6 million rows you are
not there yet.

But you did not ask the most important question: how do I insert many
rows into SQL Server effeciently.

If you are sending INSERT statements that look like:

INSERT result (id, iid, sid, pb, tid, stid, cid, start, ec)
VALUES(9, 9, 9, '99999', 9, 9, 9, '20060408 12:12:12', 9)

you have chosen the slowest option available.

If you use a parameterised query, you will be better off, and probably
even a little better if you use a stored procedure.

But since you would still be sending one row at a time, there is a lot
of network overhead, so if it's possible to use some bulk mechanism,
there is a lot to gain. I don't know if JDBC exposeses any bulk-copy
facilities, but that can very well be an option. Using XML as suggested
in one post is also an option.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]