|
|
Posted by DickChristoph on 10/02/46 11:44
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@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.
>
> 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
>
Navigation:
[Reply to this message]
|