none
Slow INSERT to a table

    Question

  • I have a stored proc that inserts 5 Million records into a table , the SELECT statement has around 10 Joins. There were no issues till last month, but now the table insert is too slow, say 500 records in 2 min.

    I scripted out the stored proc and run the SELECT part alone, and tried inserting to a temp table, all took around 10 min.

    The table has only one index (NC, composite on two columns, an INT and a CHAR(1)

    What could be the problem, what should I look into?


    • Edited by kool.rens Sunday, July 07, 2013 4:33 AM more info.
    Sunday, July 07, 2013 4:24 AM

Answers


  • The table has only one index (NC, composite on two columns, an INT and a CHAR(1)

    What could be the problem, what should I look into?


    If you mean this table does not have a clustered index, then it is a heap.  Heaps can cause performance problems.  One thing I would certainly try if I were you is creating a clustered index on the table.

    Since you are adding millions of rows, make sure the key clustered index you create is is always increasing (so that new rows go at the end of the table).  You may already have a columns like that (for example an identity column or a datetime column).  If you don't I would recommend adding a SEQUENCE column (if you are on SQL 2012) or an IDENTITY column (you can do that on any release of SQL Server).

    Tom

    Sunday, July 07, 2013 3:15 PM

All replies