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

Hm, let it run, check performance counters. what do you see? What disc layout do you have? I can insert some million rows in 30 minutes - nearly a hundred million rows, to be exact (real time financial information, linkes to 3 other tables). I pretty much bet that your IO layout is bad (i.e. bad disc structure, bad file distribution)

Answer 2

Some suggestions for increasing insert performance:

• Increase ADO.NET BatchSize
• Choose the target table's clustered index wisely, so that inserts won't lead to clustered index node splits (e.g. autoinc column)
• Insert into a temporary heap table first, then issue one big "insert-by-select" statement to push all that staging table data into the actual target table
• Apply SqlBulkCopy
• Place a table lock before inserting (if your business scenario allows for it)

Taken from Tips For Lightning-Fast Insert Performance On SqlServer