increasing performance on insert queries

[joe]

Does anyone know how to improve performance on insert statements. I have to run a query of several thousand insert statements, but it just takes too long. Does anyone know of any good tips to improve performance?

joe

[Paul]

Indexing - make sure it is efficiently indexed.

------------
joe at 3/5/01 5:20:39 PM


Does anyone know how to improve performance on insert statements. I have to run a query of several thousand insert statements, but it just takes too long. Does anyone know of any good tips to improve performance?

joe

[Joe R.]

If your table is constantly being hit with huge insert statements, make sure your fill-factor on the indexes is sufficient or else page splitting would slow down the query.

------------
Paul at 3/6/01 6:04:24 AM


Indexing - make sure it is efficiently indexed.

------------
joe at 3/5/01 5:20:39 PM


Does anyone know how to improve performance on insert statements. I have to run a query of several thousand insert statements, but it just takes too long. Does anyone know of any good tips to improve performance?

joe

[Dale Shaw]

Hi

The previous two answers are correct ... indexing and transaction logs are usually the problem.

If you have large numbers of indexes this can slow performance, particularly if you are using a clustered index that sorts the data in such a way that new records will try to be inserted somewhere in the middle.

Where are the records coming from? Is it from an exteral file? You may want to investigate the BULK INSERT statement and BCP's fast imports. Both of these will speed things up considerably by allowing you to bypass the transaction log (may have to drop and rebuild indexes, however).

The other alternative may be to import in smaller pieces. This lowers the strain on the transaction log.


Dale