SQL Server insert performance

Question

I have an insert query that gets generated like this

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);

This query is generated for only 110K rows.

It takes 30 minutes for all of these query's to execute

I checked the query plan and the largest % nodes are

A Clustered Index Insert at 57% query cost which has a long xml that I don't want to post.

A Table Spool which is 38% query cost

<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">
  <OutputList>
    <ColumnReference Database="[SkipPro]" Schema="[dbo]" Table="[InvoiceDetail]" Column="InvoiceId" />
    <ColumnReference Database="[SkipPro]" Schema="[dbo]" Table="[InvoiceDetail]" Column="InvestigatorId" />
    <ColumnReference Column="Expr1054" />
    <ColumnReference Column="Expr1055" />
  </OutputList>
  <Spool PrimaryNodeId="3" />
</RelOp>

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.

And that didn't shave off hardly anything off of the time.

Know I am running these queries in a .NET application that uses a SqlCommand object to send the query.

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.

Any ideas or hints or help?

UPDATE:

Ok so all of you were very helpful. In this situation I wish I could give credit to more than one answer.

The solution to fix this was twofold.

The first:

1) I disabled/reenabled all the foreign keys(much easier than dropping them)

ALTER TABLE TableName NOCHECK CONSTRAINT ALL
ALTER TABLE TableName CHECK CONSTRAINT ALL

2) I disabled/Reenabled the indexes (again much easier than dropping)

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 )

The second:

I wrapped all of the insert statements into one transaction. I initially didn't know how to do that in .NET.

I really appreciate all of the input I got.

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.

Answer 1

Sounds like the inserts are causing SQL Server to recalculate the indexes. One possible solution would be to drop the index, perform the insert, and re-add the index. With your attempted solution, even if you tell it to ignore constraints, it will still need to keep the index updated.