Should I consider using a bulk insert?

[delphidestructor]

I have a stored procedure that inserts records into a table that is about 600,000 records in size at this point. This stored procedure is called recursively from a middleware application that reads a text file and calls this procedure for every line of the file that has valid information. At first (table size small) it would execute very quickly, regardless of the text data file size. Now that the table size has grown it takes about 0.5 seconds per insert. With a file that may have up to 1000 lines of valid data or possibly more this takes some time. Would it be worth the time to reprogram the middleware application to write the valid data to another file and then call a bulk insert on the newly created file?

I’m at a loss here. The table record size is only 32 bytes and there are only 2 indexes. The only thing that is any different about this table is the index that isn’t the PK has a bit value in addition to the fields in the PK. I wouldn’t think that this should cause much problem.


Mike

 

[SQLSister]

Do you by chance have a clustered index on this table? MAybe it is taking so long becasue it has to physically rearrange the records. Or are there any triggers?

ALso recuve processing of records is not generally a good idea. Can;t you insert al in one set-based statment? What are you doing that you think you need to process line by line?

Questions about posting. See faq183-874

 

[donutman]

Isn't the default for SQL Server to make a primary key a clustered index? I'll bet SQLSister is right and that the primary key isn't an identity column, but rather a meaningful value which forces a reordering.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[delphidestructor]

No, there are no trigers and yes, the prinmary key is clustered. Would it be better if it wasn't clustered or had a different PK? The primary key is (tinyint, int, int, smallint). Should I have an identity column as the PK and make the primary a secondary key? I would have no use for an identity column but if this would speed up inserts I'm all for it.

Mike

 

[donutman]

If new records are added with a sequencial PK (in ascending order) then it doesn't hurt to have it be a clustered index. If they aren't sequencial then you need to remove the clustered index. I'm not sure if just unchecking clustered index is all you need to do...you may have to drop the index and rebuild it as a non-clustered index.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[delphidestructor]

That is a good idea. Try the simple things first. I will do some playing around with the indexes. Much better than reprogramming to do a bulk insert. I will let you know if it makes a difference.

Mike