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

**DickChristoph** · Apr 7 '06, 02:35 PM

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

Hi

Is it possible to set this up so that you use DTS to do this insert? Write
the data to a text file and then run the DTS package. Perhaps start a
scheduled job that runs the DTS Package.

This would be considerably faster than individual insert statements.

--
-Dick Christoph
<JSParker1@hotm ail.co.uk> wrote in message
news:1144416921 .518307.31270@j 33g2000cwa.goog legroups.com...[color=blue]
> 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:
>
> "Insufficen t 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
>[/color]

**figital** · Apr 7 '06, 04:05 PM

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

You could also pass in an XML file and directly insert its contents
into a table. I've found this method several times faster (in my case,
8x) than calling an stored procedure for each record.

**Greg D. Moore \(Strider\)** · Apr 8 '06, 12:45 AM

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

<JSParker1@hotm ail.co.uk> wrote in message
news:1144416921 .518307.31270@j 33g2000cwa.goog legroups.com...[color=blue]
> Summary: Maximum number of records per second that can be inserted into
> SQLServer 2000.
>[/color]

"Quite a few". I don't know what the limits aer and Id oubt anyone can say
for sure. But you can look up the TPC benchmarks for ideas.

[color=blue]
> 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:
>
> "Insufficen t Memory......"
>
> And very few records were inserted and no errors where sent back via
> the JDBC.[/color]

I believe JDBC has (had?) some performance issues, so it may not be your
best choice.
[color=blue]
>
> 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?
>[/color]

Well, not sure they are "definitely " to blame, but they will slow down DML
statements since they increase the overhead.

But there's ways around this.
[color=blue]
> 2) How should I configure SQLServer to maximise the speed of
> inserts?
>[/color]

Well, for one thing, "how do you need to do it" BULK INSERT or BCP will be
far faster than individual inserts.

Inserting a row at a time will be slower than "N". What's N? It depends.
To many and the commits will take too long and slow things down. To few and
you're committing more often than needed.

You can try putting your indexes on a different set of disks.
Aslo, pay VERY close attention to your disk setup. Hardware RAID over
Software RAID, RAID 10 is probably going to be better for RAID 5. Keep in
mind the logging has to be synchronous, so often that's where the disk
bottle neck will be.

Take advantage of perfmon to track disk queues and other metrics.

[color=blue]
> 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.[/color]

SQL doesn't necessarily do load balancing as you may think.

But again, is this constant inserts over the course of the day or a bulk
insert?

I do a quartly load of millions of records (somewhat wide) and can insert
and rebuild the indices in about 2-3 hours.

Hope some of this helps.

[color=blue]
>
>
>
> 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
>[/color]

**Erland Sommarskog** · Apr 8 '06, 09:25 AM

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

(JSParker1@hotm ail.co.uk) writes:[color=blue]
> 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.[/color]

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@sommarsk og.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

**Robert Klemme** · Apr 10 '06, 03:45 PM

Re: Maximum number of records per second that can be inserted intoSQLServer 2000.

JSParker1@hotma il.co.uk wrote:[color=blue]
> 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:
>
> "Insufficen t Memory......"[/color]

Did you maybe misconfigure your system? You might have set up SQL
Server to use more mem than you have virtual mem in your machine.
Otherwise I don't see how SQL Server should bail out with this error.
[color=blue]
> And very few records were inserted and no errors where sent back via
> the JDBC.[/color]

Hint: use batch mode if you don't yet. Alternatives: bcp, DTS.

Kind regards

robert

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

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

Comment

Comment

Comment

Comment

Comment