Search code examples
c#sql-serversql-server-2008importsqlclr

Fastest way to bulk insert in SQL Table from C# SQLCLR Stored Procedure

I have a C# SQLCLR Stored Procedure in which first data is filled in some dictionaries and is computed and then the output is stored in some other dictionaries.

This computation is done very fast because of the usage of Dictionaries and completely justifies my need of using CLR Stored Proc instead of a normal SQL Stored Proc.

However, I have to save the data in certain tables in SQL from these output Dictionaries, and this part takes so much time and fails my need of the whole SQLCLR proc to be faster.

I have to iterate on each key of every output dictionary and then have to create Insert Query and then have to run ExecuteNonQuery in the following way: enter image description here

So how can I improve my this approach so that it does not take time in inserting the data. I can't use SqlBulkCopy as it does not accept the in-process Context Connection (i.e. "Context Connection = true;") as the connectionString. So is there in other faster approach available? Thanks in advance.

Solution

  • You have a few options:

    1. Create a User-Defined Table Type (UDTT) and a T-SQL stored procedure that accepts that UDTT as a parameter (i.e. a Table-Valued Parameter (TVP)). Since you already have the data in a collection, absolutely do NOT create a separate DataTable to transfer that data into as that is just a waste of time, memory, and CPU. You can stream the data in its native collection directly into the TVP by creating a method that returns IEnumerable<SqlDataRecord>, uses yield return;, and is used as the value for the SqlParameter that represents the UDTT. I have two examples of this here on SO:

    2. If there is only 1 table to populate (hence 1 result set), then you can output the collection as a result set from the stored procedure and use it as follows:

      INSERT INTO schema.table (column_list)
  EXEC schema.SQLCLR_stored_procedure @param1, ....;

    3. If there is only 1 table to populate (hence 1 result set), and if you aren't doing anything over the Context Connection that violates the rules of scalar functions, then you can try changing the SQLCLR stored procedure into a TVF, return IEnumerable, and iterate over the return via yield return; to stream it out. I wrote an article years ago with an example of this on SQL Server Central: CLR Table-Valued Function Example with Full Streaming (STVF / TVF)