title: SQL Server insert performance
body: <p>I have an insert query that gets generated like this</p>
  
  <pre><code>INSERT INTO InvoiceDetail (LegacyId,InvoiceId,DetailTypeId,Fee,FeeTax,Investigatorid,SalespersonId,CreateDate,CreatedById,IsChargeBack,Expense,RepoAgentId,PayeeName,ExpensePaymentId,AdjustDetailId) 
  VALUES(1,1,2,1500.0000,0.0000,163,1002,'11/30/2001 12:00:00 AM',1116,0,550.0000,850,NULL,@ExpensePay1,NULL); 
  DECLARE @InvDetail1 INT; SET @InvDetail1 = (SELECT @@IDENTITY);
  </code></pre>
  
  <p>This query is generated for only 110K rows.</p>
  
  <p>It takes 30 minutes for all of these query's to execute</p>
  
  <p>I checked the query plan and the largest % nodes are</p>
  
  <p>A Clustered Index Insert at 57% query cost
  which has a long xml that I don't want to post.</p>
  
  <p>A Table Spool which is 38% query cost</p>
  
  <pre><code>&lt;RelOp AvgRowSize="35" EstimateCPU="5.01038E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Eager Spool" NodeId="80" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0466109"&gt;
    &lt;OutputList&gt;
      &lt;ColumnReference Database="[SkipPro]" Schema="[dbo]" Table="[InvoiceDetail]" Column="InvoiceId" /&gt;
      &lt;ColumnReference Database="[SkipPro]" Schema="[dbo]" Table="[InvoiceDetail]" Column="InvestigatorId" /&gt;
      &lt;ColumnReference Column="Expr1054" /&gt;
      &lt;ColumnReference Column="Expr1055" /&gt;
    &lt;/OutputList&gt;
    &lt;Spool PrimaryNodeId="3" /&gt;
  &lt;/RelOp&gt;
  </code></pre>
  
  <p>So my question is what is there that I can do to improve the speed of this thing? I already run 
  ALTER TABLE TABLENAME NOCHECK CONSTRAINTS ALL
  Before the queries and then 
  ALTER TABLE TABLENAME NOCHECK CONSTRAINTS ALL
  after the queries. </p>
  
  <p>And that didn't shave off hardly anything off of the time. </p>
  
  <p>Know I am running these queries in a .NET application that uses a SqlCommand object to send the query.</p>
  
  <p>I then tried to output the sql commands to a file and then execute it using sqlcmd, but I wasn't getting any updates on how it was doing, so I gave up on that. </p>
  
  <p>Any ideas or hints or help?</p>
  
  <p>UPDATE: </p>
  
  <p>Ok so all of you were very helpful. In this situation I wish I could give credit to more than one answer.</p>
  
  <p>The solution to fix this was twofold. </p>
  
  <p>The first:</p>
  
  <p>1) I disabled/reenabled all the foreign keys(much easier than dropping them)</p>
  
  <pre><code>ALTER TABLE TableName NOCHECK CONSTRAINT ALL
  ALTER TABLE TableName CHECK CONSTRAINT ALL
  </code></pre>
  
  <p>2) I disabled/Reenabled the indexes (again much easier than dropping)</p>
  
  <pre><code>ALTER INDEX [IX_InvoiceDetail_1] ON [dbo].[InvoiceDetail] DISABLE
  ALTER INDEX [IX_InvoiceDetail_1] ON [dbo].[InvoiceDetail] REBUILD PARTITION = ALL WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
  </code></pre>
  
  <p>The second:</p>
  
  <p>I wrapped all of the insert statements into one transaction. I initially didn't know how to do that in .NET. </p>
  
  <p>I really appreciate all of the input I got. </p>
  
  <p>If I ever do this kind of translation from DB to DB I will definitely start with BULK INSERT. It seems much more flexible and faster.</p>