I need to have a way to insert to a table, fast, synchronous, with minimal duration. What I've tried ("blind") is :
- 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?