Quick SQL inserts?

Question

I am dealing with pieces of data, each of which contains 50 thousand rows. I insert them into the SQL database using LINQ:

for(int i=0;i<50000;i++) { DB.TableName.InsertOnSubmit ( new TableName { Value1 = Array[i,0], Value2 = Array[i,1] } ); } DB.SubmitChanges(); 

It takes about 6 minutes, and I want it to be much less if possible. Any suggestions?

Answer 1

if you are reading in a file, you would be better off using BULK INSERT (Transact-SQL) and if you are writing this many (50K lines) at a time from memory, you might be better off writing to a flat file first and then using Bulk Insert in this file.

Answer 2

As you make a simple insertion and do not get much from using LinqToSql, look at SqlBulkCopy , this will remove most of the round trips and reduce the overhead on the Sql server side. You will need to make very few changes to the encoding to use it.

Also, pay attention to pre-sorting your data by the column into which the table is indexed, as this will lead to better cache deletion when SQL-Server updates the table.

Also consider whether to load data into a temporary staging table that is not indexed, and then a stored process to insert into the main table using a single sql statement. This may allow SqlServer to extend the indexing work to all your processors.

Answer 3

There are many things you need to check / do.

• How much disk space is allocated to the database? Is it free enough to make all the inserts without automatically increasing the size? If not, increase the size of the database file as it must stop every so many inserts in order to automatically resize the db itself.

• DO NOT make separate insertions. They take too much time. Instead, use either tabular parameter values ​​(sql 2008), or a bulk copy of sql, or a single insert statement (in that order of preference).

• drop any indexes in this table before and recreate them after loading. With this many inserts, they are likely to be hell-blasted anyway.

• If you have triggers, consider dropping them before the download completes.

• Do you have enough RAM on the database server? Do you need to check the server itself to see if it consumes ALL available RAM? If so, you might want to consider rebooting before loading ... The sql server tends to just consume and hold everything it can handle.

• In the lines of RAM, we like to store enough RAM on the server to store the entire database in memory. I'm not sure if this is possible for you or not.

• What is disk speed? Is the queue depth quite long? In addition to replacing equipment, there are not many.