Slow INSERT | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Slow INSERT

Hi! I have a query that executes in 80 s which, given that amounts of data, I guess is resonable. What is not resonable is that it takes about 20 min to insert the data into a table. The data consists of 103,000 rows of <uniqueidentifier (constant), bigint> so the total size of data should not be too large. At first I figured this had to be index-related. The PK to the table was/is the combination of the two columns, so therefore I automatically had a clustered index. Removing the index (just as a test) gave a slight negativ effect on the INSERT-performance!!!
I also tried changing the fill factor (which I admittedly does not know much about) to 50%, this gave a slight performance gain.
Next try was using sp_autostats to shut down updates to the index, this had no effect what so ever. Having tried all this I figured that it must be hardware related and tried running the query on a much better server. The query has not completed yet after 2h 5 min!!! BTW, there are no other users on the two servers I’ve been running the query on so any kind of locking is not an issue. What the beeeeeeeeeeeeeeeeeeeeeeeeep is going on? The query (if it matters): The select-statement is split into two basically identical parts by a union all. DECLARE @iOperation int
DECLARE @iCalculationID int
DECLARE @iPluginID int
DECLARE @sPartitionName nvarchar(200)
DECLARE @uiKey uniqueidentifier
SET @iOperation = 1
SET @iCalculationID = 1
SET @iPluginID = 32
SET @sPartitionName = ”
SET @uiKey = NEWID() INSERT tSigmundTransactionID2(AccessKey, TransactionID)
SELECT @uiKey, tFinancialTransaction.FinancialTransactionID FROM
tFinancialTransaction
INNER JOIN tArticle ON tFinancialTransaction.ArticleNumber=tArticle.ArticleNumber
INNER JOIN (SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesSales(@iCalculationID)) SAS ON SAS.ArticleNumber=tFinancialTransaction.ArticleNumber
INNER JOIN (SELECT CustomerID FROM folkspel.dbo.GetCustomerSelection(@iPluginID, @sPartitionName)) CS ON CS.CustomerID=tFinancialTransaction.CustomerID
INNER JOIN vSigmundTransactionGroupsByName ON vSigmundTransactionGroupsByName.TransactionTypeID=tFinancialTransaction.TransactionCode
LEFT OUTER JOIN tSigmundFTExtension ON tFinancialTransaction.FinancialTransactionID=tSigmundFTExtension.TransactionID
WHERE
(tArticle.Product=’0001′ AND vSigmundTransactionGroupsByName.GroupName = ‘AfterDrawnSales’)
AND
NOT tFinancialTransaction.CustomerID IS NULL
AND
NOT tFinancialTransaction.ArticleNumber IS NULL
AND
NOT tFinancialTransaction.TransactioNcode IS NULL
AND
tSigmundFTExtension.TransactionID IS NULL
UNION ALL
SELECT @uiKey, tFinancialTransaction.FinancialTransactionID FROM
tFinancialTransaction
INNER JOIN tArticle ON tFinancialTransaction.ArticleNumber=tArticle.ArticleNumber
INNER JOIN (SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesSales(@iCalculationID)) SAS ON SAS.ArticleNumber=tFinancialTransaction.ArticleNumber
INNER JOIN (SELECT CustomerID FROM folkspel.dbo.GetCustomerSelection(@iPluginID, @sPartitionName)) CS ON CS.CustomerID=tFinancialTransaction.CustomerID
INNER JOIN vSigmundTransactionGroupsByName ON vSigmundTransactionGroupsByName.TransactionTypeID=tFinancialTransaction.TransactionCode
LEFT OUTER JOIN tSigmundFTExtension ON tFinancialTransaction.FinancialTransactionID=tSigmundFTExtension.TransactionID
WHERE
(tArticle.Product<>’0001′ AND vSigmundTransactionGroupsByName.GroupName = ‘PreDrawnSales’)
AND
NOT tFinancialTransaction.CustomerID IS NULL
AND
NOT tFinancialTransaction.ArticleNumber IS NULL
AND
NOT tFinancialTransaction.TransactioNcode IS NULL
AND
tSigmundFTExtension.TransactionID IS NULL /HL
I remembered something else: Execution plan shows that all steps takes 0% of the time… go figure… /HL
something has to show as contributing cost, totaling 100%
try SET SHOWPLAN_ALL ON, and read the top value of TotalSubtreeCost i am interpreting this as:
it takes 80 sec to execute the above without the INSERT, ie, as a SELECT query,
but including the INSERT causes it to take 20min? compare the execution plan with and without the INSERT, is the SELECT portion of the plans the same or different?
various versions/hotfixes of SQL Server sometimes finds a good plan for a SELECT only query, but a bad plan when the select query feeds into an insert. does tSigmundTransactionID2 have foreign keys? also, try inserting this data into a temp table, then try inserting the contents of the temp table into the permanent table. note the time for each step
I tried it with "SET SHOWPLAN_ALL ON" and got the following figures (total subtree cost): SELECT 0.56892
INSERT 0.57897
INSERT to tmp table 0.57897
INSERT from tmp to real: 0.61189 These figures, however, have no relation to reality.
Inserting to a tmp table was marginally faster than inserting it into the real table.
Inserting rows from the tmp table to the real table took no time at all (less than a second). It is correct that 80 s was for the select separately and 20 min for insert+select. Execution plan is the same for select and select+insert (except the insert). I tried SELECT…INTO same problem there. /HL
I forgot: tSigmundTransactionID2 has no foreign keys, triggers etc.
I created it for the purpose of avoiding anything like that impacting performance. /HL
Interesting results: I removed the function calls from the joins and instead inserted them into tables and then joined with those tables. This improved performance considerably. Now both SELECT and INSERT+SELECT takes 1 min. I probably won’t dig further into this since I now have got an acceptable performance, but it sure would be interesting to know why SQL server behaves the way it does. The new query (using the old table with the clustered index): DECLARE @iOperation int
DECLARE @iCalculationID int
DECLARE @iPluginID int
DECLARE @sPartitionName nvarchar(200)
DECLARE @uiKey uniqueidentifier
DECLARE @uiCSKey uniqueidentifier
DECLARE @uiA1Key uniqueidentifier
DECLARE @uiA2Key uniqueidentifier
SET @iOperation = 1
SET @iCalculationID = 1
SET @iPluginID = 32
SET @sPartitionName = ”
SET @uiKey = NEWID()
SET @uiCSKey = NEWID()
SET @uiA1Key = NEWID()
SET @uiA2Key = NEWID() DELETE tSigmundTransactionID
DELETE tSigmundCustomerSet
DELETE tSigmundArticleSet INSERT tSigmundCustomerSet(AccessKey, CustomerID)
SELECT @uiCSKey, CustomerID FROM folkspel.dbo.GetCustomerSelection(@iPluginID, @sPartitionName) INSERT tSigmundArticleSet(AccessKey, ArticleNumber)
SELECT @uiA1Key, t1.ArticleNumber FROM
(SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesSales(@iCalculationID)) as t1,
(SELECT ArticleNumber FROM tArticle WHERE Product=’0001′) as t2
WHERE t1.ArticleNumber=t2.ArticleNumber INSERT tSigmundArticleSet(AccessKey, ArticleNumber)
SELECT @uiA2Key, t1.ArticleNumber FROM
(SELECT ArticleNumber FROM folkspel.dbo.GetSelectedArticlesSales(@iCalculationID)) as t1,
(SELECT ArticleNumber FROM tArticle WHERE Product<>’0001′) as t2
WHERE t1.ArticleNumber=t2.ArticleNumber –EXECUTE sp_autostats ‘tSigmundTransactionID’, ‘OFF’ INSERT tSigmundTransactionID(AccessKey, TransactionID)
SELECT @uiKey, tFinancialTransaction.FinancialTransactionID FROM
tFinancialTransaction
INNER JOIN tSigmundArticleSet ON tFinancialTransaction.ArticleNumber=tSigmundArticleSet.ArticleNumber AND [email protected]
INNER JOIN tSigmundCustomerSet ON tSigmundCustomerSet.CustomerID=tFinancialTransaction.CustomerID AND [email protected]
INNER JOIN vSigmundTransactionGroupsByName ON vSigmundTransactionGroupsByName.TransactionTypeID=tFinancialTransaction.TransactionCode
LEFT OUTER JOIN tSigmundFTExtension ON tFinancialTransaction.FinancialTransactionID=tSigmundFTExtension.TransactionID
WHERE
vSigmundTransactionGroupsByName.GroupName = ‘AfterDrawnSales’
AND
NOT tFinancialTransaction.CustomerID IS NULL
AND
NOT tFinancialTransaction.ArticleNumber IS NULL
AND
NOT tFinancialTransaction.TransactioNcode IS NULL
AND
tSigmundFTExtension.TransactionID IS NULL
UNION ALL
SELECT @uiKey, tFinancialTransaction.FinancialTransactionID FROM
tFinancialTransaction
INNER JOIN tSigmundArticleSet ON tFinancialTransaction.ArticleNumber=tSigmundArticleSet.ArticleNumber AND [email protected]
INNER JOIN tSigmundCustomerSet ON tSigmundCustomerSet.CustomerID=tFinancialTransaction.CustomerID AND [email protected]
INNER JOIN vSigmundTransactionGroupsByName ON vSigmundTransactionGroupsByName.TransactionTypeID=tFinancialTransaction.TransactionCode
LEFT OUTER JOIN tSigmundFTExtension ON tFinancialTransaction.FinancialTransactionID=tSigmundFTExtension.TransactionID
WHERE
vSigmundTransactionGroupsByName.GroupName = ‘PreDrawnSales’
AND
NOT tFinancialTransaction.CustomerID IS NULL
AND
NOT tFinancialTransaction.ArticleNumber IS NULL
AND
NOT tFinancialTransaction.TransactioNcode IS NULL
AND
tSigmundFTExtension.TransactionID IS NULL
–SELECT COUNT(1) FROM tSigmundTransactionID2
]]>