MSSQL insert becomes very slow...

[stoymigo]

I have to insert records in one MS SQL 2005 table, I'm using vb6 for importing purposes, reading the file is constant, however the insert slows down.
It takes about 2 min for the first 10000 records (about 100 records/s), however it slows down to 25 records/s half-way through the insert and this import will take over an hour, the file has over 100 000 records.

The file isn't basic, hence why I needed to make a program, plus I learned late that the client has DbaManager....a free version of MS SQL 2000 so now I might be stuck with compatibility issues.

I'd mainly like to know whether this is a slow database issue.

 

[reactor_sa]

Possibly you have a clustered index on the table and the records arent being inserted according to the ordering of the index.

 

[Beachless]

The easiest is to use a sql profiler on the DB and see what the slow transactions are.
It might also be connections or memory issues(need code sample to check that).

 

[stoymigo]

The easiest is to use a sql profiler on the DB and see what the slow transactions are.
It might also be connections or memory issues(need code sample to check that).

The code checks if a record exists, then inserts. The select statement is quite unnecessary bcos it only has to find 100 existing out of the 100 000, but i doubt that's it.

I've already been told it might be due to the table filling up, I'll try an alternative or do profiling.

 

[dequadin]

Also check what recovery mode the SQL is set up on.

It could be thinking that the entire import is one transaction so the log file is growing all the time.

Have to tried batching? Insert 10000 records, close connection. Start again and import next 10000 records etc...?

 

[stoymigo]

Also check what recovery mode the SQL is set up on.

It could be thinking that the entire import is one transaction so the log file is growing all the time.

Have to tried batching? Insert 10000 records, close connection. Start again and import next 10000 records etc...?

I have tried that, insert about a 10th of records, close the connection & excel file then start with the next but it didn't help. But I haven't tried executing that batch using a SQL script.

And that log file thingy, it's something I didn't know about but thanks.

 

[[)roi(]]

I have tried that, insert about a 10th of records, close the connection & excel file then start with the next but it didn't help. But I haven't tried executing that batch using a SQL script.

And that log file thingy, it's something I didn't know about but thanks.

Before I start let me apologize if I misunderstood your coding approach (bear in mind its always difficult to comment correctly when we have not seen a portion of your code)

Ok let me nevertheless try:
Reading from an excel file with such a large record count is imo part of the issue (watch the ram use as you loop through the file), couple this with a check for an existing record + what sounds like record by record inserts? (is this correct?)

To identify problem areas; I suggest you separate things -- in your loop create a single compound SQL command text file that consolidates a single update & single insert statement for all 100K recs. Then exit program recording time.
Note: do not execute any insert / update statements in your loop.

In the end you should have a new text file that contains your single update and single insert statement for all 100K records i.e. 1 statement, rather than 100K statements.
You can the run this statement using the MS SQL console to test the processing time for this. if faster then you have a clue as to where the problem could be (e.g. running 100K inserts statements will be much slower than 1 insert for 100K records).

As an ultimate solution (requires MSQL2005 or newer), if you'd like to significantly ramp up the speed I'd suggest look into:
1. "Bulk insert"ing excel file into temporary table (can be driven direct from stored procedure on the server)
2. Server based stored procedure to then update / insert rec in target table from the temp table (recommend you use a join query to isolate update vs insert recs).

Btw to deal with a large amount of records > 2000K, I'd suggest segmenting the table into months / quarters / other
Then look into UNION keyword i.e. combine two or more tables / queries into a single source.

This approach if designed properly will also allow easy purging, especially if the segmentation is inline with purge requirements.

Hope this helps.

 

[Kosmik]

Bulk insert is the correct way to go. It is highly unlikely that it is the actual insert that is causing the slow down unless there are triggers firing. More likely that the select routine for exisitng records is your problem and that can be index's etc that are not in place. It's also far easier to create a temp table whithin sql, do a bulk insert and then a second query against the temp table and your final table to do a insert into with a join that elimates the duplicates that you would be looking for.

Although I prefer scripts, you could also utilise SSIS in 2005 to import and help if you are more comfortable with a wizard interface.