Improving massive INSERT-UPDATE performance


  • Hi.

    I have some stored procedures with massive INSERT and UPDATE statements. I have some non-clustered indexed on the tables that I read or write. I can register a good performance. How can I improve it, yet? Many thanks

    • Moved by Tom PhillipsModerator Thursday, November 04, 2010 5:58 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Wednesday, November 03, 2010 5:25 PM

All replies

  • You might try deleting any index that does not provide a necessary constaint such as 'UNIQUE' prior to the update and recreate the indexes at the conclusion. Index maintenance is expensive. You can almost always create and index faster than you can maintain one when you update more than 30% of the rows in a table.
    Tom G.
    Wednesday, November 03, 2010 7:31 PM
  • We need more info. How do you perform these INSERT/UPDATE? Do you mix them up? Is it performed on sinlge table? What version you are using?
    Best Regards, Uri Dimant SQL Server MVP
    Thursday, November 04, 2010 7:51 AM
  • Any progress?
    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    Tuesday, November 09, 2010 4:32 PM