SQL Server Vs MongoDB - Performance Perspective

Relational database such as SQL Server would be slower than NoSQL solutions. That's understandable. Relational database has its focus and ACID has its cost. But I didn't expect their performance gap is so big until I did some real tests.

The test machine is the same I tested on MongoDB last time:

• OS: Windows Server 2008 64-bit SP2
• CPU: Intel i5 750
• Memory: 6G
• DB: SQL Server 2008 Enterprise edition SP1.

Similar I did the test on MongoDB last time, I create a simple test database with only three columns:

  id    -- uniqueidentifier (Clustered index)
 key   -- varchar(40) (Unique non-clustered index)
 value -- varchar(40) (no indexing)

MongoDB test code was modified to test SQL Server. I ran the console and was eager to see the results. But the first test case of inserting 1-million records in local database seemed to be never completed.

The insert operations were just too slow. Is there anything going wrong with my machine? I installed all Windows/SQL Server important updates, and disable almost all unnecessary processes, deflagment disk, and redid the test. But the result is still very disappointing, less than 400 insert per second.

Then I tried a few options:

1. Change id to be non-clustered index: no big difference
2. Change id type from uniqueidentifier to varchar(40): no big difference
3. Add an extra identity integer column (primary key): no big difference
4. Change key/value from varchar(40) to char(40): no big difference
5. Change key's index fillfactor to 10, 30, 60, 80, 100: slightly difference among them
6. Do insertion using SQL Script:

    DECLARE @id uniqueidentifier
   DECLARE @Count int, @Max int
   SET @Count = 0
   SET @Max = 1000000
   WHILE @Count < @Max       
   BEGIN             
       SET @id = NEWID()            
       INSERT INTO testTable (id, key, value) VALUES (@id, @id, @id)            
       SET @Count = @Count + 1       
   END

no big difference
8. Use stored procedure: improve a little
9. Change Key's unique non-clustered index to non-clustered index: improve a little.
10. Change database recovery model from full to simple: improve a little

The maximum insertion speed could reach around 900 per second after all kind of tuning in my local machine. That's not even close to MongoDB, and I didn't do any extra work to achieve 200000+ insertions/sec.

Googled and found that actually many people are having the same problem. The article SQL Server Slow Performance on Insert compiles a few resources on the topic of slow insertion in SQL Server.

Anyway I was patient enough to wait the 1-million records being inserted. Then I did the rest of the test with following results (local test):

Insert: 900/Second
Update: 1500/Second
Search on clustered-indexed column: 9000/Second
Search on non-clusterd indexed column: 2000/Second
Search on Non-indexed column: 27/Second

Besides the non-indexed column search (table scan), only the search on clustered-indexed column in SQL Server is comparable to MongoDB's.