SQL Server insert performance
- by Jose
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?
