Mongo vs MsSql часть четвертая - select

Здравствуй дорогой читатель! Продолжаю серию экспериментов по сравнению производительности Mongo DB и MSSQL Server. На этот раз я решил сравнить производительность операций выборки, так как это, пожалуй, основная операция при взаимодействии с БД в большинстве приложений. На этот раз постараюсь провести несколько экспериментов в одной статье, по-этому будет минимум текста.

Выборка по ключу

В первом тесте я буду выбирать информацию из БД по первичному ключу. Для подготовки тестовых данных я воспользовался наработками предыдущего эксперимента по добавлению разнородных объектов. Выборки осуществляются по случайно сгенерированному ключу в цикле по десять тысяч итераций из наборов записей в количествах 1000, 10000, 50000, 100000 и 500000. Исходники этого теста на Гитхабе.

Результаты

----------------------------------------------------------
Generating test data for FindByKey by FakeBenchmark (1000)
Starting search by FakeBenchmark. 30000 iterations
Time: 00:00:00.0020001
Generating test data for FindByKey by SqlServerBenchmark (1000)
Starting search by SqlServerBenchmark. 30000 iterations
Time: 00:00:03.8220500
Generating test data for FindByKey by MongoBenchmark (1000)
Starting search by MongoBenchmark. 30000 iterations
Time: 00:00:03.7440066
----------------------------------------------------------
Generating test data for FindByKey by FakeBenchmark (10000)
Starting search by FakeBenchmark. 30000 iterations
Time: 00:00:00
Generating test data for FindByKey by SqlServerBenchmark (10000)
Starting search by SqlServerBenchmark. 30000 iterations
Time: 00:00:03.7752066
Generating test data for FindByKey by MongoBenchmark (10000)
Starting search by MongoBenchmark. 30000 iterations
Time: 00:00:03.7440066
----------------------------------------------------------
Generating test data for FindByKey by FakeBenchmark (50000)
Starting search by FakeBenchmark. 30000 iterations
Time: 00:00:00
Generating test data for FindByKey by SqlServerBenchmark (50000)
Starting search by SqlServerBenchmark. 30000 iterations
Time: 00:00:03.9285434
Generating test data for FindByKey by MongoBenchmark (50000)
Starting search by MongoBenchmark. 30000 iterations
Time: 00:00:03.7596066
----------------------------------------------------------
Generating test data for FindByKey by FakeBenchmark (100000)
Starting search by FakeBenchmark. 30000 iterations
Time: 00:00:00
Generating test data for FindByKey by SqlServerBenchmark (100000)
Starting search by SqlServerBenchmark. 30000 iterations
Time: 00:00:03.9624070
Generating test data for FindByKey by MongoBenchmark (100000)
Starting search by MongoBenchmark. 30000 iterations
Time: 00:00:03.7752066
----------------------------------------------------------
Generating test data for FindByKey by FakeBenchmark (500000)
Starting search by FakeBenchmark. 30000 iterations
Time: 00:00:00.0156001
Generating test data for FindByKey by SqlServerBenchmark (500000)
Starting search by SqlServerBenchmark. 30000 iterations
Time: 00:00:04.0227168
Generating test data for FindByKey by MongoBenchmark (500000)
Starting search by MongoBenchmark. 30000 iterations
Time: 00:00:03.9624069

Как видно, на операциях выборки данных по первичному ключу обе СУБД показали примерно одинаковые результаты, причем скорость выборки практически не зависит от количества записей в базе.

Выборка набора записей

Во второй части я буду выбирать данные не по одной штуке, а по 10, 100 и 500, но тоже по первичному ключу. Прямая ссылка на исходники эксперимента.

Результаты выборки по 10 записей

----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (1000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (1000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:00.5750329
Generating test data for FindByKeyPage by MongoBenchmark (1000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:01.5640895
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (10000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (10000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:00.5280302
Generating test data for FindByKeyPage by MongoBenchmark (10000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:01.5450884
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (50000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (50000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:00.5480313
Generating test data for FindByKeyPage by MongoBenchmark (50000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:01.5370880
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (100000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (100000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:00.5820333
Generating test data for FindByKeyPage by MongoBenchmark (100000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:01.6270931
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (500000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (500000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:00.8730499
Generating test data for FindByKeyPage by MongoBenchmark (500000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:01.5280874

Результаты выборки по 100 записей

----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (1000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (1000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:02.4960044
Generating test data for FindByKeyPage by MongoBenchmark (1000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:10.6236186
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (10000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (10000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:02.0904036
Generating test data for FindByKeyPage by MongoBenchmark (10000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:10.5924186
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (50000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (50000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:02.6591521
Generating test data for FindByKeyPage by MongoBenchmark (50000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:10.7436145
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (100000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00.0010000
Generating test data for FindByKeyPage by SqlServerBenchmark (100000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:02.0904037
Generating test data for FindByKeyPage by MongoBenchmark (100000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:10.8579860
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (500000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (500000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:02.3035283
Generating test data for FindByKeyPage by MongoBenchmark (500000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:11.0840761

Результаты выборки по 500 записей

----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (1000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (1000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:07.8892956
Generating test data for FindByKeyPage by MongoBenchmark (1000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:56.5102290
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (10000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (10000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:07.8762948
Generating test data for FindByKeyPage by MongoBenchmark (10000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:59.7670187
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (50000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (50000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:09.2549259
Generating test data for FindByKeyPage by MongoBenchmark (50000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:01:03.6063025
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (100000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (100000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:08.9895913
Generating test data for FindByKeyPage by MongoBenchmark (100000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:01:06.0436285
----------------------------------------------------------
Generating test data for FindByKeyPage by FakeBenchmark (500000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00
Generating test data for FindByKeyPage by SqlServerBenchmark (500000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:08.1980976
Generating test data for FindByKeyPage by MongoBenchmark (500000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:01:04.4944175

Увы, в выборке больше одной записи Mongo не особо силен. Если на достаточно маленьком количестве записей в выборке, Mongo практически не уступает MSSQL, то уже на 100 записях выборки разница в быстродействии отличается в пять раз. И чем больше выборка, тем этот разрыв в производительности увеличивается.

Выборка по like

Последний, на этот раз, эксперимент - поиск по like. Искать буду по начальным четырем символам Guid, которыми заполняется поле Field1. Индекс по этому полю не строился. Исходники этого эксперимента.

Результат

----------------------------------------------------------
Generating test data for FindLike by FakeBenchmark (1000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00.0030002
Generating test data for FindLike by SqlServerBenchmark (1000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:04.5132582
Generating test data for FindLike by MongoBenchmark (1000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:03.6492087
----------------------------------------------------------
Generating test data for FindLike by FakeBenchmark (10000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00.0020001
Generating test data for FindLike by SqlServerBenchmark (10000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:15.9119101
Generating test data for FindLike by MongoBenchmark (10000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:00:30.7047562
----------------------------------------------------------
Generating test data for FindLike by FakeBenchmark (50000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00.0030001
Generating test data for FindLike by SqlServerBenchmark (50000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:00:55.6081806
Generating test data for FindLike by MongoBenchmark (50000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:02:04.7851373
----------------------------------------------------------
Generating test data for FindLike by FakeBenchmark (100000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00.0030002
Generating test data for FindLike by SqlServerBenchmark (100000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:01:36.6695292
Generating test data for FindLike by MongoBenchmark (100000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:03:23.4496367
----------------------------------------------------------
Generating test data for FindLike by FakeBenchmark (500000)
Starting search by FakeBenchmark. 3000 iterations
Time: 00:00:00.0040002
Generating test data for FindLike by SqlServerBenchmark (500000)
Starting search by SqlServerBenchmark. 3000 iterations
Time: 00:02:34.5498398
Generating test data for FindLike by MongoBenchmark (500000)
Starting search by MongoBenchmark. 3000 iterations
Time: 00:04:22.5310159

И снова MSSQL выполнил операции поиска быстрее Mongo. Как и следовало ожидать, время поиска по не индексированному полю зависит от количества записей в базе.

Сегодняшние эксперименты показали преимущество MSSQL Server в выборке набора записей. Тем не менее, Mongo DB вполне может заменить MSSQL Server, если требуется выбирать небольшое количество записей.