LINQ Speedup

"From 10 minutes to 10 seconds" -- this is true experience I had. The brief story is, I had to insert a lot of records into the database. The way LINQ does (if you look at the generated query by using some SQL profiler) was to insert the record one-by-one surrounded with some safety check. Pretty straightforward; and it works fine in a small scale. In case you have cascaded data structure with a lot of Foreign keys floating around (which is normal for normal forms) in your database, the time it takes can be arbitrarily long -- Imagine how many tables it requires to lock before any one operation.

The way I found which works faster when inserting a lot of data in one go needs just a few extras.

1. Cache/Serialise the tables to local data structures, if they are read repeatedly and not very large. This can save a lot of SELECT statements generated by LINQ. Preferably, perform a sorting on your local data structure, which can support faster BinarySearch(). (e.g.)
2. Write a stored-procedure that handles Bulk-insert to multiple tables, in which you must have safety check and error handling as well. After all, stored procedures are first class citizen in handling the database operations. (e.g.)
3. Instead of calling LINQ insert, call the stored procedure (registered as function import in .dbml). (e.g.)

All the above are not tricky to be done. But the result can be astonishing and worthwhile.