SQL speed up performance of insert?

by

To get the best possible performance you should:

  • Remove all triggers and constraints on the table
  • Remove all indexes, except for those needed by the insert
  • Ensure your clustered index is such that new records will always be inserted at the end of the table (an identity column will do just fine). This prevents page splits (where SQL Server must move data around because an existing page is full)
  • Set the fill factor to 0 or 100 (they are equivalent) so that no space in the table is left empty, reducing the number of pages that the data is spread across.
  • Change the recovery model of the database to Simple, reducing the overhead for the transaction log.

Are multiple clients inserting records in parallel? If so then you should also consdier the locking implications.

Note that SQL Server can suggest indexes for a given query either by executing the query in SQL Server Management Studio or via the Database Engine Tuning Advisor. You should do this to make sure you haven’t removed an index which SQL Server was using to speed up the INSERT.

If this still isn’t fast enough then you should consider grouping up inserts an using BULK INSERT instead (or something like the bcp utility or SqlBulkCopy, both of which use BULK INSERT under the covers). This will give the highest throughput when inserting rows.

Also see Optimizing Bulk Import Performance – much of the advice in that article also applies to “normal” inserts.

More Related Contents:

  1. Inserting multiple rows in a single SQL query? [duplicate]
  2. How can I INSERT data into two tables simultaneously in SQL Server?
  3. How to insert Arabic characters into SQL database?
  4. Calculate a Running Total in SQL Server
  5. How can I do an UPDATE statement with JOIN in SQL Server?
  6. Not equal != operator on NULL
  7. SET NOCOUNT ON usage
  8. How can I get column names from a table in SQL Server?
  9. How can I select the first day of a month in SQL?
  10. Select statement to find duplicates on certain fields
  11. Is it possible to specify condition in Count()?
  12. Getting only Month and Year from SQL DATE
  13. WHERE Clause vs ON when using JOIN
  14. how can I Update top 100 records in sql server
  15. @@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity
  16. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  17. How to create a PivotTable in Transact/SQL?
  18. Pivot data in T-SQL
  19. Extracting hours from a DateTime (SQL Server 2005)
  20. How to split string using delimiter char using T-SQL?
  21. Oracle insert if row does not exist
  22. Get the records of last month in SQL server
  23. I want to use CASE statement to update some records in sql server 2005
  24. Generate all combinations in SQL
  25. alternatives to REPLACE on a text or ntext datatype
  26. How to return a incremental group number per group in SQL
  27. SQL – Inserting a row and returning primary key
  28. How to search SQL column containing JSON array
  29. Eliminate and reduce overlapping date ranges
  30. How to bulk insert only new rows in PostreSQL

Leave a Comment