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?

Improve this question
1
  • Comments are not for extended discussion; this conversation has been moved to chat. Commented Aug 29, 2017 at 10:44

2 Answers 2

Reset to default
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.

Improve this answer
1
  • 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" Commented Apr 10, 2012 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.

Improve this answer
5
  • 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. Commented Feb 27, 2012 at 8:35
  • The problem with your approach is that laptops and servers are fundamentally different from an internal architecture point of view. Commented Feb 27, 2012 at 14:24
  • ... and I cannot determine ways to speed up something that would run on a server if I test on a laptop? Commented Feb 27, 2012 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. Commented Feb 27, 2012 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 Commented Feb 27, 2012 at 19:55

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.