MongoDB vs SQL Insert Comparison

2 years ago I read a blog post that stated MongoDB was significantly faster for writes than SQL.

The original post seems to have disappeared although there was a response here 

http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/mongodb-vs-sql-server-insert-comparison

I decided at the time not only to take up the challenge, but to get the guys on my DB team to have a go as well.

The basic idea was to create 500,000 Unique Identifiers and insert them into a document or into a table.

The MongoDB option in the original post said SQL would take 1.5 minutes and MongoDB 30 seconds. You have to create these too, not grab them from somewhere else.

As it turned out the above post suggested it could be done in 6 seconds. I thought even that could be improved on.

The entire DB team (about 8 of us) all comfortably beat the 30 second watermark and most got down really low. I'm not bragging, but I won and my solution takes approx 3 seconds on my 3 year old work PC (after the query plan has been cached).

CREATE TABLE dbo.MongoCompare (
  GUID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
  VALUE INT)
GO


SET NOCOUNT ON
BEGIN TRAN
DECLARE @Id INT = 1
WHILE (@Id < 500001) 
  BEGIN
    INSERT  INTO dbo.MongoCompare WITH (TABLOCK) (VALUE)
    VALUES (@Id)
    SET @Id = @Id + 1
  END
COMMIT

IF (SELECT @@TRANCOUNT) > 0 
  ROLLBACK TRANSACTION



Whether or not you prefer MongoDB or SQL is sort of irrelevant for this. It's just something to mull over. You need to do any comparisons with equal skill sets to ensure you have the best answer for what you are doing.

The trick to my answer was to use a transaction and commit in one fell swoop at the end. Something to bear in mind is that the original post stated that for the duration MongoDB sent the processor crazy. For my option it barely tickles it (a couple of percent on one core)