SQL Server Performance

Insert Query performance issue

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by DStevensTN, Jul 5, 2006.

  1. DStevensTN New Member

    The following insert query is taking around 7 minutes to handle 4000 rows. The select runs in less than a second. At the end of the run it gets a foreign key constraint error.
    I am assuming that the constraint error is what is taking so long. It created thousands of locks on the table. We will fix the constraint error, but is there anything we can do that would improve the time this takes? or a best practices to help with the locks?
    Thanks.



    INSERT INTO ServiceHistory(
    ROID,
    VehicleID,
    ServiceAdvisorID,
    CustomerID,
    BatchHistoryID,
    ServiceDate,
    Mileage,
    RONumber)
    SELECT DISTINCT RO.ROID,RO.VehicleID,SA.ServiceAdvisorID,
    RO.CustomerID,RO.BatchHistoryID,RO.CloseDate,RO.Vehicle_Mileage,
    RO.RONumber
    FROM ROs RO
    JOIN ROLines RL ON RO.ROID = RL.ROID
    JOIN ServiceAdvisors SA ON RO.DealerID = SA.DealerID
    AND RL.AdvisorNbr = SA.ServiceAdvisorNumber
    WHERE RO.Processed = 0

  2. Adriaan New Member

    What kind of indexes dop you have on the target tables? Check the fill factor for the indexes.

    Also, does the target table have any trigger firing for inserts? If so, check if there is any less-than-optimal processing in there. If the insert trigger updates certain fields, note that this in turn would fire up any update trigger on the same table.

    See if it makes any difference if you do it in 4 batches of 1000 rows each.
  3. DStevensTN New Member

    3 indexes on target table, no triggers, breaking up the query did not help. I did notice the database(this is a development database) was set to grow at 1MB, I changed it to 10%. This did not help.
    Also, got rid of the foreign key issue, and we still are very slow. 2 minutes to insert 999 records.
  4. Adriaan New Member

    Probably the indexes - fill factor too small, or clustered on multiple columns?

    Try clustered on identity column, then nonclustered for PK etc.
  5. joechang New Member

    insert the select results into a temp table, how long does this take
    how big is the table

    then insert the temp contents into the final table, how long does this take

    check your disk counters during this

    make sure there is no database growth during the test, size your files correctly
  6. DStevensTN New Member

    Recreated the table, everything is running fast now

Share This Page