SQL Server Performance

Slow INSERT

Discussion in 'T-SQL Performance Tuning for Developers' started by Litsne, Aug 1, 2005.

  1. Litsne New Member

    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
  2. Litsne New Member

    I remembered something else:

    Execution plan shows that all steps takes 0% of the time... go figure...

    /HL
  3. joechang New Member

    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
  4. Litsne New Member

    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
  5. Litsne New Member

    I forgot:

    tSigmundTransactionID2 has no foreign keys, triggers etc.
    I created it for the purpose of avoiding anything like that impacting performance.

    /HL
  6. Litsne New Member

    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 tSigmundArticleSet.AccessKey=@uiA1Key
    INNER JOIN tSigmundCustomerSet ON tSigmundCustomerSet.CustomerID=tFinancialTransaction.CustomerID AND tSigmundCustomerSet.AccessKey=@uiCSKey
    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 tSigmundArticleSet.AccessKey=@uiA2Key
    INNER JOIN tSigmundCustomerSet ON tSigmundCustomerSet.CustomerID=tFinancialTransaction.CustomerID AND tSigmundCustomerSet.AccessKey=@uiCSKey
    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

Share This Page