patternsqlMinor
Speed up INSERTs
Viewed 0 times
insertsspeedstackoverflow
Problem
I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is :
The test scenario that I use consists of 100 connections that each run an INSERT and then wait 0.1 seconds and then again, indefinitely. Each "inserter" logs execution time.
Looking at execution times I sometimes see 1.5 and even 10 seconds on an insert (as exceptional cases), otherwise a typical 0.2 seconds is what I see.
Further context :
Where should I look for further improvements to the performance of such a process?
- Have no index on the table whatsoever
- Switch to simple logging (from full logging)
The test scenario that I use consists of 100 connections that each run an INSERT and then wait 0.1 seconds and then again, indefinitely. Each "inserter" logs execution time.
Looking at execution times I sometimes see 1.5 and even 10 seconds on an insert (as exceptional cases), otherwise a typical 0.2 seconds is what I see.
Further context :
- SQL Server 2008 R2 Express (afaik the express version limits only DB size (4GB), RAM (1GB) and logical CPUs (1))
- Machine : Laptop with 7200 RPM HDD, 8 GB RAM and 8 logical CPUs, hosting the server and the clients
- The table consists of 1 BigInt ID (identity), an NVARCHAR(100) field, and NVARCHAR(MAX) (INSERTs on this have a 10k payload), and an NVARCHAR(MAX) (30k payload at INSERT) column(s)
Where should I look for further improvements to the performance of such a process?
Solution
A lot of folks added a lot of good points in the comments.
1) Separate your transaction logs onto a different drive. That's going to be tough with a laptop. If you can't do that, get yourself an SSD for the laptop, and that should make your life considerably better.
2) Pre-grow your data and log files to a target amount. If you expect to add 1GB of data to your database, make your data and log files at least 1.5GB to start. Data and log file autogrowth are killers for performance and may very well be the reason you see these 10 second "chokes" in performance. SQL Management Studio has some built-in reports (I believe you right click on the DB, then select Reports -> Disk Usage) which should have a table of all of your autogrow events there.)
3) If you can batch your inserts on a per-client basis, then do so using the SqlBulkCopy or BULK INSERT statement.
4) There should be no reason why your tables shouldn't have a clustered primary key. SQL Server should be able to deal with a clustered index on your IDENTITY column in a fairly performant way.
1) Separate your transaction logs onto a different drive. That's going to be tough with a laptop. If you can't do that, get yourself an SSD for the laptop, and that should make your life considerably better.
2) Pre-grow your data and log files to a target amount. If you expect to add 1GB of data to your database, make your data and log files at least 1.5GB to start. Data and log file autogrowth are killers for performance and may very well be the reason you see these 10 second "chokes" in performance. SQL Management Studio has some built-in reports (I believe you right click on the DB, then select Reports -> Disk Usage) which should have a table of all of your autogrow events there.)
3) If you can batch your inserts on a per-client basis, then do so using the SqlBulkCopy or BULK INSERT statement.
4) There should be no reason why your tables shouldn't have a clustered primary key. SQL Server should be able to deal with a clustered index on your IDENTITY column in a fairly performant way.
Context
StackExchange Database Administrators Q#13866, answer score: 6
Revisions (0)
No revisions yet.