Loading large amount of data - running very slowly

[apf]

Hello all, I need some helpful hints. I'm running SQLServer 2000 on a Win2000 box with 2 CPUs.

I have a 10 million record file that I load into an unindexed table on SQL Server using bcp. This runs fine. I have written a stored procedure that reads each record from this table and inserts it into another table based on certain values in the record. This proc starts slow and gets slower, the longer it runs.

Does anyone have any helpful hints as to why the second step gets slower or maybe a suggestion of another way to do what I'm trying to do?

Thanks in advance!

APF

 

[tlbroadbent]

There are probably multiple causes for the slow process.

1) If you are using a cursor to read each record and insert it to the other table, I suggest you revise the procedure to insert sets of data rather than a sequential process of testing one record at a time. Using cursors in SQL Server can be painfully slow.

See the SQL Magazine article "Sequential to Set-Based." Itzik Ben-Gan says, "Forget cursors and get with set-based programming."

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=22431

2) As you insert the records into the other table, the transaction log for the database will grow. This growth is costly in terms of performance. You may want to create batches of inserts that are done within a transaction. Commit the transaction after each batch. This helps limit the log file growth. You may even find it beneficial to backup the transaction log after inserting a batch of records in order to further reduce log file growth. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.