|
|
Posted by Greg D. Moore \(Strider\) on 10/02/45 11:44
<JSParker1@hotmail.co.uk> wrote in message
news:1144416921.518307.31270@j33g2000cwa.googlegroups.com...
> Summary: Maximum number of records per second that can be inserted into
> SQLServer 2000.
>
"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.
> 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.
I believe JDBC has (had?) some performance issues, so it may not be your
best choice.
>
> 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?
>
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.
> 2) How should I configure SQLServer to maximise the speed of
> inserts?
>
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.
> 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.
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.
>
>
>
> 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
>
Navigation:
[Reply to this message]
|