Big SQL inserts TVF and BULK inserts

Question

What is the fastest way to insert a huge array (10M elements) from a C # application?

So far I have used volumetric insertion. A C # application creates a large text file, and I load it with the BULK INSERT . Out of curiosity, I wrote a simple user defined CLR value function.

 [SqlFunction(Name = "getArray", FillRowMethodName = "FillRow")] public static IEnumerable getArray(String name) { return my_arrays[name]; // returns the array I want to insert into db } public static void FillRow(Object o, out SqlDouble sdo) { sdo = new SqlDouble((double)o); } 

And this request:

 INSERT INTO my_table SELECT data FROM dbo.getArray('x'); 

It works almost 2 times faster than the volume equivalent. Exact results:

BULK - 330 (write to disc + insert) TVF - 185s

Of course, this is due to write overhead, but I don't know if there is a BULK insert in the memory equivalent.

So my question is: is it better to bind TVF to BULK (which is created for huge inserts), or I don’t see something here. Is there any third alternative?

Answer 1

I use SqlBulkCopy when I really need the last drop of performance, so you can skip overhead, all on disk.

SqlBulkCopy accepts an IDataReader, which you must implement, but only a few interface methods. What I always do is simply create a class MyBulkCopySource : IDataReader , click on the "Implementation Interface" and pass it to BulkCopy, and also see how the method is called. Implement this, try again, etc. You only need to implement three of the four, the rest will never be called.

AFAIK is the fastest way to transfer data from a C # program to SqlDB.

GJ

Answer 2

• Use SqlBulkCopy
• From multiple threads with blocks such as 30,000 rows each time.
• NOT to the final table, but to the temporary table
• From which you copy using a connection setting that does not respect locks.

This completely places the smallest lock on the destination table.