title: Table-valued parameter insert performing poorly
body: <p>I have a TVP+SP insert strategy implemented as i need to insert big amounts of rows (probably concurrently) while being able to get some info in return like <code>Id</code> and stuff. Initially I'm using EF code first approach to generate the DB structure. My entities:</p>
  
  <p>FacilityGroup</p>
  
  <pre><code>public class FacilityGroup
  {
      public int Id { get; set; }
  
      [Required]
      public string Name { get; set; }
  
      public string InternalNotes { get; set; }
  
      public virtual List&lt;FacilityInstance&gt; Facilities { get; set; } = new List&lt;FacilityInstance&gt;();
  }
  </code></pre>
  
  <p>FacilityInstance</p>
  
  <pre><code>public class FacilityInstance
  {
      public int Id { get; set; }
  
      [Required]
      [Index("IX_FacilityName")]
      [StringLength(450)]
      public string Name { get; set; }
  
      [Required]
      public string FacilityCode { get; set; }
  
      //[Required]
      public virtual FacilityGroup FacilityGroup { get; set; }
  
      [ForeignKey(nameof(FacilityGroup))]
      [Index("IX_FacilityGroupId")]
      public int FacilityGroupId { get; set; }
  
      public virtual List&lt;DataBatch&gt; RelatedBatches { get; set; } = new List&lt;DataBatch&gt;();
  
      public virtual HashSet&lt;BatchRecord&gt; BatchRecords { get; set; } = new HashSet&lt;BatchRecord&gt;();
  }
  </code></pre>
  
  <p>BatchRecord</p>
  
  <pre><code>public class BatchRecord
  {
      public long Id { get; set; }
  
      //todo index?
      public string ItemName { get; set; }
  
      [Index("IX_Supplier")]
      [StringLength(450)]
      public string Supplier { get; set; }
  
      public decimal Quantity { get; set; }
  
      public string ItemUnit { get; set; }
  
      public string EntityUnit { get; set; }
  
      public decimal ItemSize { get; set; }
  
      public decimal PackageSize { get; set; }
  
      [Index("IX_FamilyCode")]
      [Required]
      [StringLength(4)]
      public string FamilyCode { get; set; }
  
      [Required]
      public string Family { get; set; }
  
      [Index("IX_CategoryCode")]
      [Required]
      [StringLength(16)]
      public string CategoryCode { get; set; }
  
      [Required]
      public string Category { get; set; }
  
      [Index("IX_SubCategoryCode")]
      [Required]
      [StringLength(16)]
      public string SubCategoryCode { get; set; }
  
      [Required]
      public string SubCategory { get; set; }
  
      public string ItemGroupCode { get; set; }
  
      public string ItemGroup { get; set; }
  
      public decimal PurchaseValue { get; set; }
  
      public decimal UnitPurchaseValue { get; set; }
  
      public decimal PackagePurchaseValue { get; set; }
  
      [Required]
      public virtual DataBatch DataBatch { get; set; }
  
      [ForeignKey(nameof(DataBatch))]
      public int DataBatchId { get; set; }
  
      [Required]
      public virtual FacilityInstance FacilityInstance { get; set; }
  
      [ForeignKey(nameof(FacilityInstance))]
      [Index("IX_FacilityInstance")]
      public int FacilityInstanceId { get; set; }
  
      [Required]
      public virtual Currency Currency { get; set; }
  
      [ForeignKey(nameof(Currency))]
      public int CurrencyId { get; set; }
  }
  </code></pre>
  
  <p>DataBatch</p>
  
  <pre><code>public class DataBatch
  {
      public int Id { get; set; }
  
      [Required]
      public string Name { get; set; }
  
      public DateTime DateCreated { get; set; }
  
      public BatchStatus BatchStatus { get; set; }
  
      public virtual List&lt;FacilityInstance&gt; RelatedFacilities { get; set; } = new List&lt;FacilityInstance&gt;();
  
      public virtual HashSet&lt;BatchRecord&gt; BatchRecords { get; set; } = new HashSet&lt;BatchRecord&gt;();
  }
  </code></pre>
  
  <p>And then my SQL Server related code, TVP Structure:</p>
  
  <pre><code>CREATE TYPE dbo.RecordImportStructure 
  AS TABLE (
  ItemName VARCHAR(MAX),
  Supplier VARCHAR(MAX),
  Quantity DECIMAL(18, 2),
  ItemUnit VARCHAR(MAX),
  EntityUnit VARCHAR(MAX),
  ItemSize DECIMAL(18, 2),
  PackageSize DECIMAL(18, 2),
  FamilyCode VARCHAR(4),
  Family VARCHAR(MAX),
  CategoryCode VARCHAR(MAX),
  Category VARCHAR(MAX),
  SubCategoryCode VARCHAR(MAX),
  SubCategory VARCHAR(MAX),
  ItemGroupCode VARCHAR(MAX),
  ItemGroup VARCHAR(MAX),
  PurchaseValue DECIMAL(18, 2),
  UnitPurchaseValue DECIMAL(18, 2),
  PackagePurchaseValue DECIMAL(18, 2),
  FacilityCode VARCHAR(MAX),
  CurrencyCode VARCHAR(MAX)
  );
  </code></pre>
  
  <p>Insert stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE dbo.ImportBatchRecords (
      @BatchId INT,
      @ImportTable dbo.RecordImportStructure READONLY
  )
  AS
  SET NOCOUNT ON;
  
  DECLARE     @ErrorCode  int  
  DECLARE     @Step  varchar(200)
  
  --Clear old stuff?
  --TRUNCATE TABLE dbo.BatchRecords; 
  
  INSERT INTO dbo.BatchRecords (
      ItemName,
      Supplier,
      Quantity,
      ItemUnit,
      EntityUnit,
      ItemSize,
      PackageSize,
      FamilyCode,
      Family,
      CategoryCode,
      Category,
      SubCategoryCode,
      SubCategory,
      ItemGroupCode,
      ItemGroup,
      PurchaseValue,
      UnitPurchaseValue,
      PackagePurchaseValue,
      DataBatchId,
      FacilityInstanceId,
      CurrencyId
  )
      OUTPUT INSERTED.Id
      SELECT
      ItemName,
      Supplier,
      Quantity,
      ItemUnit,
      EntityUnit,
      ItemSize,
      PackageSize,
      FamilyCode,
      Family,
      CategoryCode,
      Category,
      SubCategoryCode,
      SubCategory,
      ItemGroupCode,
      ItemGroup,
      PurchaseValue,
      UnitPurchaseValue,
      PackagePurchaseValue,
      @BatchId,
      --FacilityInstanceId,
      --CurrencyId
      (SELECT TOP 1 f.Id from dbo.FacilityInstances f WHERE f.FacilityCode=FacilityCode),
      (SELECT TOP 1 c.Id from dbo.Currencies c WHERE c.CurrencyCode=CurrencyCode) 
      FROM    @ImportTable;
  </code></pre>
  
  <p>And finally my quick, test only solution to execute this stuff on .NET side.</p>
  
  <pre><code>public class BatchRecordDataHandler : IBulkDataHandler&lt;BatchRecordImportItem&gt;
  {
      public async Task&lt;int&gt; ImportAsync(SqlConnection conn, SqlTransaction transaction, IEnumerable&lt;BatchRecordImportItem&gt; src)
      {
          using (var cmd = new SqlCommand())
          {
              cmd.CommandText = "ImportBatchRecords";
              cmd.Connection = conn;
              cmd.Transaction = transaction;
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.CommandTimeout = 600;
  
              var batchIdParam = new SqlParameter
              {
                  ParameterName = "@BatchId",
                  SqlDbType = SqlDbType.Int,
                  Value = 1
              };
  
              var tableParam = new SqlParameter
              {
                  ParameterName = "@ImportTable",
                  TypeName = "dbo.RecordImportStructure",
                  SqlDbType = SqlDbType.Structured,
                  Value = DataToSqlRecords(src)
              };
  
              cmd.Parameters.Add(batchIdParam);
              cmd.Parameters.Add(tableParam);
  
              cmd.Transaction = transaction;
  
              using (var res = await cmd.ExecuteReaderAsync())
              {
                  var resultTable = new DataTable();
                  resultTable.Load(res);
  
                  var cnt = resultTable.AsEnumerable().Count();
  
                  return cnt;
              }
          }
      }
  
      private IEnumerable&lt;SqlDataRecord&gt; DataToSqlRecords(IEnumerable&lt;BatchRecordImportItem&gt; src)
      {
          var tvpSchema = new[] {
              new SqlMetaData("ItemName", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("Supplier", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("Quantity", SqlDbType.Decimal),
              new SqlMetaData("ItemUnit", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("EntityUnit", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("ItemSize", SqlDbType.Decimal),
              new SqlMetaData("PackageSize", SqlDbType.Decimal),
              new SqlMetaData("FamilyCode", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("Family", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("CategoryCode", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("Category", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("SubCategoryCode", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("SubCategory", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("ItemGroupCode", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("ItemGroup", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("PurchaseValue", SqlDbType.Decimal),
              new SqlMetaData("UnitPurchaseValue", SqlDbType.Decimal),
              new SqlMetaData("PackagePurchaseValue", SqlDbType.Decimal),
              new SqlMetaData("FacilityInstanceId", SqlDbType.VarChar, SqlMetaData.Max),
              new SqlMetaData("CurrencyId", SqlDbType.VarChar, SqlMetaData.Max),
          };
  
          var dataRecord = new SqlDataRecord(tvpSchema);
  
          foreach (var importItem in src)
          {
              dataRecord.SetValues(importItem.ItemName,
                  importItem.Supplier,
                  importItem.Quantity,
                  importItem.ItemUnit,
                  importItem.EntityUnit,
                  importItem.ItemSize,
                  importItem.PackageSize,
                  importItem.FamilyCode,
                  importItem.Family,
                  importItem.CategoryCode,
                  importItem.Category,
                  importItem.SubCategoryCode,
                  importItem.SubCategory,
                  importItem.ItemGroupCode,
                  importItem.ItemGroup,
                  importItem.PurchaseValue,
                  importItem.UnitPurchaseValue,
                  importItem.PackagePurchaseValue,
                  importItem.FacilityCode,
                  importItem.CurrencyCode);
  
              yield return dataRecord;
          }
      }
  }
  </code></pre>
  
  <p>Import entity structure:</p>
  
  <pre><code>public class BatchRecordImportItem
  {
      public string ItemName { get; set; }
  
      public string Supplier { get; set; }
  
      public decimal Quantity { get; set; }
  
      public string ItemUnit { get; set; }
  
      public string EntityUnit { get; set; }
  
      public decimal ItemSize { get; set; }
  
      public decimal PackageSize { get; set; }
  
      public string FamilyCode { get; set; }
  
      public string Family { get; set; }
  
      public string CategoryCode { get; set; }
  
      public string Category { get; set; }
  
      public string SubCategoryCode { get; set; }
  
      public string SubCategory { get; set; }
  
      public string ItemGroupCode { get; set; }
  
      public string ItemGroup { get; set; }
  
      public decimal PurchaseValue { get; set; }
  
      public decimal UnitPurchaseValue { get; set; }
  
      public decimal PackagePurchaseValue { get; set; }
  
      public int DataBatchId { get; set; }
  
      public string FacilityCode { get; set; }
  
      public string CurrencyCode { get; set; }
  }
  </code></pre>
  
  <p>Please don't mind useless reader at the end, doesn't really do much. So without the reader inserting 2.5kk rows took around 26 minutes while <code>SqlBulkCopy</code> took around 6+- minutes. Is there something I'm doing fundamentally wrong? I’m using <code>IsolationLevel.Snapshot</code> if this matters. Using SQL Server 2014, free to change DB structure and indices.</p>
  
  <p><strong>UPD 1</strong>  </p>
  
  <hr>
  
  <p>Done a couple of adjustments/improvement attempts described by @Xedni, specifically:</p>
  
  <ol>
  <li>Limited all string fields that didn't have a max length to some fixed length</li>
  <li>Changed all TVP members from <code>VARCHAR(MAX)</code> to <code>VARCHAR(*SomeValue*)</code></li>
  <li>Added a unique index to FacilityInstance->FacilityCode</li>
  <li>Added a unique index to Curreency->CurrencyCode</li>
  <li>Tried adding WITH RECOMPILE to my SP</li>
  <li>Tried using <code>DataTable</code> instead of <code>IEnumerable&lt;SqlDataRecord&gt;</code></li>
  <li>Tried batchinng data into smaller buckets, 50k and 100k per SP execution instead of 2.5kk</li>
  </ol>
  
  <p>My structure is now like this:</p>
  
  <pre><code>CREATE TYPE dbo.RecordImportStructure 
  AS TABLE (
  ItemName VARCHAR(4096),
  Supplier VARCHAR(450),
  Quantity DECIMAL(18, 2),
  ItemUnit VARCHAR(2048),
  EntityUnit VARCHAR(2048),
  ItemSize DECIMAL(18, 2),
  PackageSize DECIMAL(18, 2),
  FamilyCode VARCHAR(16),
  Family VARCHAR(512),
  CategoryCode VARCHAR(16),
  Category VARCHAR(512),
  SubCategoryCode VARCHAR(16),
  SubCategory VARCHAR(512),
  ItemGroupCode VARCHAR(16),
  ItemGroup VARCHAR(512),
  PurchaseValue DECIMAL(18, 2),
  UnitPurchaseValue DECIMAL(18, 2),
  PackagePurchaseValue DECIMAL(18, 2),
  FacilityCode VARCHAR(450),
  CurrencyCode VARCHAR(4)
  );
  </code></pre>
  
  <hr>
  
  <p>So far no noticeable performance gains unfortunately, 26-28 min as before</p>
  
  <hr>
  
  <p><strong>UPD 2</strong><br>
  Checked the execution plan - indices are my bane? <a href="https://i.stack.imgur.com/l3AOV.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/l3AOV.png" alt="EXE_PLAN"></a></p>
  
  <hr>
  
  <p><strong>UPD 3</strong><br>
  Added <code>OPTION (RECOMPILE);</code> at the end of my SP, gained a minor boost, now sitting at ~25m for 2.5kk</p>