7

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?

6

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
    Partitioning seems like overkill here, and it's a lot more complicated to design and maintain that just "put a partition on it'll be faster" – JNK Apr 10 '12 at 18:19
1

My advice is to get off a laptop. Put this on a real machine, with fast drives and lots of ram.

  • I am sure that would help but I was testing on the laptop just to see where things will bottleneck and what would be the first and easiest steps I can take to improve things, before considering scaling up. – Andrei Rinea Feb 27 '12 at 8:35
  • The problem with your approach is that laptops and servers are fundamentally different from an internal architecture point of view. – datagod Feb 27 '12 at 14:24
  • ... and I cannot determine ways to speed up something that would run on a server if I test on a laptop? – Andrei Rinea Feb 27 '12 at 14:34
  • 1
    You can, but you are going to possibly waste time trying to overcome bottlenecks introduced by the laptop itself. It is like an olympic runner asking people to comment on his form, asking for ways to improve his time on the track, all the while he is wearing scuba gear. – datagod Feb 27 '12 at 19:04
  • 3
    Your bottlenecks on the laptop are probably very different (and variable!) compared to a proper server. If you're going to stress test at least run it on a device set up as a server with nothing else running. You're likely to hit RAM/CPU bottlenecks on a laptop that might not exist on a production machine – Ben Brocka Feb 27 '12 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.