What would be the fastest way to insert 750 records using Ado.NET?

Question

We have tried it using an orm mapper tool, but it opens en closes the connection 750 times. Then we tried to construct a bulk insert, but that goes even slower...

Edit:

CREATE TABLE [dbo].[DataWarehouse](
[DataWarehouseId] [int] IDENTITY(1,1) NOT NULL,
[ColumnName] [nvarchar](max) NOT NULL,
[ColumnValue] [nvarchar](max) NOT NULL,
[RRN] [nvarchar](50) NOT NULL,
[PackageSessionId] [int] NOT NULL,
[AccessCode] [nvarchar](100) NOT NULL,
[Selectie] [nvarchar](150) NOT NULL,
[Date] [smalldatetime] NOT NULL,
[PackageId] [int] NOT NULL,
[Category] [nvarchar](500) NULL,
[OrderId] [int] NOT NULL,
[Category2] [nvarchar](500) NULL,
[TestCode] [nvarchar](200) NULL,
[Category3] [int] NULL,
[QuestionSpecificCategory] [nvarchar](max) NULL,
 CONSTRAINT [PK_DataWarehouse] PRIMARY KEY CLUSTERED 
(
    [DataWarehouseId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Answer 1

This takes between 200 and 400 ms on my (nowhere near state of the art) machine.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Diagnostics;

namespace InsertSpeedTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var sw = new Stopwatch();
            sw.Start();
            using (var conn = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=SpeedTests;Integrated Security=True;"))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction())
                {
                    using (var cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "insert into [dbo].[DataWarehouse] ( [ColumnName] , [ColumnValue] , [RRN] , [PackageSessionId] , [AccessCode] , [Selectie] , [Date] , [PackageId] , [Category] , [OrderId] , [Category2] , [TestCode] , [Category3] , [QuestionSpecificCategory]) values  ( @ColumnName , @ColumnValue , @RRN , @PackageSessionId , @AccessCode , @Selectie , @Date , @PackageId , @Category , @OrderId , @Category2 , @TestCode , @Category3 , @QuestionSpecificCategory)";
                        cmd.CommandType = CommandType.Text;
                        cmd.Transaction = tran;
                        cmd.Parameters.Add("@ColumnName", SqlDbType.NVarChar,-1);
                        cmd.Parameters.Add("@ColumnValue", SqlDbType.NVarChar, -1);
                        cmd.Parameters.Add("@RRN", SqlDbType.NVarChar, 50);
                        cmd.Parameters.Add("@PackageSessionId", SqlDbType.Int);
                        cmd.Parameters.Add("@AccessCode", SqlDbType.NVarChar, 100);
                        cmd.Parameters.Add("@Selectie", SqlDbType.NVarChar, 150);
                        cmd.Parameters.Add("@Date", SqlDbType.SmallDateTime);
                        cmd.Parameters.Add("@PackageId", SqlDbType.Int);
                        cmd.Parameters.Add("@Category", SqlDbType.NVarChar, 500);
                        cmd.Parameters.Add("@OrderId", SqlDbType.Int);
                        cmd.Parameters.Add("@Category2", SqlDbType.NVarChar, 500);
                        cmd.Parameters.Add("@TestCode", SqlDbType.NVarChar, 200);
                        cmd.Parameters.Add("@Category3", SqlDbType.Int);
                        cmd.Parameters.Add("@QuestionSpecificCategory", SqlDbType.NVarChar, -1);
                        cmd.Prepare();
                        for (int i = 0; i < 750; i++)
                        {
                            cmd.Parameters["@ColumnName"].Value = "Column " + i;
                            cmd.Parameters["@ColumnValue"].Value = "value " + i;
                            cmd.Parameters["@RRN"].Value = "prn" + i;
                            cmd.Parameters["@PackageSessionId"].Value = i;
                            cmd.Parameters["@AccessCode"].Value = "access code" + i;
                            cmd.Parameters["@Selectie"].Value = "selectio " + i;
                            cmd.Parameters["@Date"].Value = DateTime.Now.AddMinutes(i);
                            cmd.Parameters["@PackageId"].Value = i;
                            cmd.Parameters["@Category"].Value = "category " + i;
                            cmd.Parameters["@OrderId"].Value = 100000 + i;
                            cmd.Parameters["@Category2"].Value = "category2 " + i;
                            cmd.Parameters["@TestCode"].Value = "test code " + i;
                            cmd.Parameters["@Category3"].Value = 200000 + i;
                            cmd.Parameters["@QuestionSpecificCategory"].Value = "whatever " + i;
                            cmd.ExecuteNonQuery();
                        }
                        tran.Commit();
                    }
                }
            }
            sw.Stop();
            Debug.WriteLine(sw.ElapsedMilliseconds);
        }
    }
}

Answer 2

Fastest:

• Schedule inserts of blocks of like 32 items. Use Workitem for that.
• In every handler, open connection, submit ONE sql statement with all 32 inserts.

Result:

• Fewer round trips. The syncroneous nature of a request makes submitting a request a little expensive - submitting 32 in one batch is faster than submitting 32 one by one.
• Usage of parallel inserts if your SQL Server is properly configured (not many are, sadly). it also means multiple requests hit the server without waiting for the next batch.

Answer 3

Using a simple SqlCommand should be fast enough for 750 rows, unless you have some really heavy fields, or some very expensive indexes/constraints in the database:

var insert = new SqlCommand("INSERT INTO ...", connection);
var fooParam = insert.Parameters.Add("Foo", SqlType.Int);

for (int i = 0; i < 750; i++)
{
    fooParam.Value = i;
    insert.ExecuteNonQuery();
}