How do i improve performance when inserting large amount of data

How do i improve performance when inserting large amount of data

Post by Robert Knab » Sun, 28 Jul 2002 01:08:07



We have a performance problem when inserting many rows into a table that
already keeps a very large amount of data on an SQL Server 2000 system. The
table has four indexes (but no CLUSTERED).

Currently i am using an ADO Recordset Batchupdate but this has not been an
improvement compared with the previously used precompiled INSERT statement
using an ADO Command object.

A few day ago i tied to improve performance using the Index Tuning Wizard
but as a result our application was much slower than before. Tuning indexes
does not seem to be a solution.

Does anyone have an idea what can be done to improve the server performance?
Which is the fastest way to get a large amount of data into a table?

Thanks in advance
Robert Knabe

 
 
 

How do i improve performance when inserting large amount of data

Post by Nico De Gree » Sun, 28 Jul 2002 03:11:08


Tuning indexes won't help you, more indexes will slow you down because sql
server has to update the table and all the indexes.
A better idea is to remove all the indexes and add them again after the
query.

A more common solution is bulk insert
BULK INSERT statement or bcp utility if you're data comes from a file.

Another way is the
INSERT INTO DestinationTable
   SELECT a, b, c
   FROM SourceTable


Quote:> We have a performance problem when inserting many rows into a table that
> already keeps a very large amount of data on an SQL Server 2000 system.
The
> table has four indexes (but no CLUSTERED).

> Currently i am using an ADO Recordset Batchupdate but this has not been an
> improvement compared with the previously used precompiled INSERT statement
> using an ADO Command object.

> A few day ago i tied to improve performance using the Index Tuning Wizard
> but as a result our application was much slower than before. Tuning
indexes
> does not seem to be a solution.

> Does anyone have an idea what can be done to improve the server
performance?
> Which is the fastest way to get a large amount of data into a table?

> Thanks in advance
> Robert Knabe


 
 
 

How do i improve performance when inserting large amount of data

Post by Stu » Tue, 30 Jul 2002 19:13:08


youll want to turn the indexes off b4 updates.  rebuild them once done

stu


Quote:> We have a performance problem when inserting many rows into a table that
> already keeps a very large amount of data on an SQL Server 2000 system.
The
> table has four indexes (but no CLUSTERED).

> Currently i am using an ADO Recordset Batchupdate but this has not been an
> improvement compared with the previously used precompiled INSERT statement
> using an ADO Command object.

> A few day ago i tied to improve performance using the Index Tuning Wizard
> but as a result our application was much slower than before. Tuning
indexes
> does not seem to be a solution.

> Does anyone have an idea what can be done to improve the server
performance?
> Which is the fastest way to get a large amount of data into a table?

> Thanks in advance
> Robert Knabe