Lee Everest created a post named MongoDB vs. SQL Server – INSERT comparison where he compared inserting 50001 rows with MongoDB vs. SQL Server. So he claims that MongoDB inserts 50001 rows in 30 seconds while the SQL Server one takes 1.5 minutes. Okay so I looked at this SQL Script and can make 2 improvements

First create this table

T-SQL
1
2
3
4
5
CREATE TABLE MongoCompare
    (guid uniqueidentifier
    ,value int
    )
GO
CREATE TABLE MongoCompare
    (guid uniqueidentifier
    ,value int
    )
GO

Here is the script he used.

T-SQL
1
2
3
4
5
6
7
DECLARE @id int = 1
WHILE (@id < 500001)
BEGIN
    INSERT INTO MongoCompare VALUES (newid(), @id)
    SET @id+=1
END
GO
DECLARE @id int = 1
WHILE (@id < 500001)
BEGIN
    INSERT INTO MongoCompare VALUES (newid(), @id)
    SET @id+=1
END
GO

Now if I was to write that code I would write it with an explicit transaction and I would also use nocount on. So If we do this

T-SQL
1
2
3
4
5
6
7
8
9
SET NOCOUNT ON
BEGIN TRAN
DECLARE @id int = 1
WHILE (@id < 500001)
BEGIN
    INSERT INTO MongoCompare VALUES (newid(), @id)
    SET @id+=1
END
COMMIT
SET NOCOUNT ON
BEGIN TRAN
DECLARE @id int = 1
WHILE (@id < 500001)
BEGIN
    INSERT INTO MongoCompare VALUES (newid(), @id)
    SET @id+=1
END
COMMIT

SQL Server now only takes 6 seconds and you have one atomic block of code, either all succeeds or nothing succeeds.

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum