Сравнение производительности .NET ORM: Часть 2. Генерация SQL-запросов

И снова здравствуйте. В первой части серии я постарался рассмотреть производительность L2S, EF и AR/NH по сравнению с классическим ADO (тестовое приложение можно найти здесь). NET и друг с другом. Для этого были написаны специальные тесты, но замеры мы производили на достаточно высоком уровне, не выделяя в результатах базу данных. Как я и обещал, во второй части я бы хотел опуститься ниже, в базу, и сравнить запросы, генерируемые нашими подопытными. Должен сказать, что я был несколько удивлен полученными результатами, но обо всем по порядку.

Способ сравнения

Способ сравнения я выбрал до предела простой. У нас уже есть написанный код для получения продуктов по заказчику. Это не ахти какой сложный запрос, конечно, но тем не менее что-то мы из него получим. При этом у нас есть два варианта получения данных: одним запросом с джойнами (тест CustomerProducts) или при помощи простых операций вроде получения сначала заказчика, потом его заказов, потом деталей заказа и уж в самом конце – продуктов, то есть при помощи пачки запросов (тест CustomerProductsComplex).

Значит, заряжаем SQL Server Profiler, запускаем наши тесты, вытаскиваем пойманные SQL-запросы, садим их в Management Studio, включаем “Include Actual Execution Plan”. Я сделал 4 теста. В первом сравниваются запросы из теста CustomerProducts для всех четырех способов доступа к данным. В последующих трех я сравнивал попарно запросы для теста CustomerProducts и для теста CustomerProductsComplex для каждого из ORM отдельно (для классического ADO.NET я не делал этот тест).

Какова цель этих тестов? Цель первого теста – сравнить качество генерируемого SQL-кода по сравнению с конкурентами и самым простым SQL-запросом, написанным вручную и получающим эти же данные. Цель остальных тестов – выяснить, какой способ получения данных, через один или несколько запросов, выгоднее и насколько.

Итак, поехали.

Сюрпризы

Уже на проведении первого же теста (того, где должен быть один результирующий запрос) меня ждало несколько интересных открытий:

1. L2S зачем-то сгенерировал два запроса вместо одного. Первый запрос у них вытаскивает заказчика, а второй уже выполняет реальную работу. Я проверил свой код – вроде бы там простой LINQ-запрос, который должен превратиться в один SQL-запрос. Более того, такой же LINQ-запрос в EF действительно превращается в один SQL-запрос. Что ж, сами напросились, L2S будет тестироваться с двумя запросами, тем более что первый запрос по сравнению со вторым выполняется почти моментально, поэтому какая разница.
2. AR/NH пошел еще дальше, чем L2S. Я ожидал, что написанный мною HQL-запрос выльется в один SQL-запрос. Не срослось. NH сгенерировал и выполнил 23 запроса. Но, присмотревшись к природе этих запросов, я увидел, что это запросы к таблице Supplier, которая в тесте никак не участвует, но зато есть в моем маппинге. Я понял, что, по всей видимости, не отключил lazy loading, поэтому просто в очередной раз отметил свое незнание AR/NH, исправил свойство Supplier класса Product на SupplierID, а атрибут [BelongsTo] поменял на [Property], после чего просто перезапустил тесты и получил корректные SQL-запросы. Замечание: Да, вы правильно подумали, результаты общих тестов для AR из предыдущей части тоже учитывают эти дополнительные запросы, но исходя из того, что я увидел в результатах выполнения тестов, мы не получили здесь особого прироста производительности.
3. Я наконец-то убедился, что L2S и EF писали разные люди :) Даже несмотря на то, что я специально сделал LINQ-запросы в L2S и EF идентичными, генерируемые SQL-запросы отличаются. Более того, они отличаются не только текстом, но и производительностью, что вы увидите в результатах.

Анализ результатов

Результаты тестов несколько необычны. Для простоты я запустил все запросы каждого теста в одном наборе и посмотрел на Query cost каждого запроса, который считается относительно всего набора.

В первом тесте я получил следующие результаты:

Классический ADO.NET – 14%
LINQ 2 SQL – 2 + 48 = 50%
Entity Framework – 22 %
Active Record (NHibernate) – 14%

Вот и еще один сюрприз: запрос L2S оказался существенно медленнее запроса EF, не говоря уже про победителей – NH и наш контрольный запрос. Зато NH превзошел все ожидания. HQL очень чисто трансформировался в SQL, его план выполнения в точности совпал с планом выполнения контрольного запроса. План выполнения EF тоже неплох, а вот L2S намудрил. Да, здесь есть важный момент: это всего лишь ОДИН тест, на основании которого не стоит судить об ORM в целом. Вполне возможно, есть запросы, где намудрит EF или даже NH. В любом случае, среднее время выполнения этих запросов намного меньше результирующего времени, учитывающего дополнительные расходы на построение запроса и материализацию результата (в среднем где-то 50-150 мс согласно профайлеру). И тут уже L2S несомненно опережает EF и AR/NH с большим отрывом.

Во втором тесте я сравнил сложный запрос с джойнами и набор простых запросов для L2S:

Сложный запрос с джойнами – 25%
Набор простых запросов – 75%

Как видите, один запрос выиграл, что неудивительно. Однако, в целом, можно сказать, что с точки зрения базы данных потери на lazy loading не такие уж и большие.

Третий тест был посвящен EF:

Сложный запрос с джойнами – 18%
Набор простых запросов – 72%

Как видите, результаты похожи до безобразия.

И, наконец, в четвертом тесте я сравнил запросы для NH:

Сложный запрос с джойнами – 6%
Набор простых запросов – 94%

Вот уж где для увеличения производительности стоит отказаться от удобных переборов коллекций в пользу HQL, так это здесь :)

Выводы

Какие выводы мы можем сделать по результатам этих тестов?

1. Прежде всего, если у вас есть возможность выполнить один сложный запрос – выполняйте, не бегайте по коллекциям и не инициализируйте их отдельно, это немного сэкономит вам драгоценное время. Особенно это касается ситуаций, где bottleneck находится в базе данных, а не внутри ORM.
2. Похоже, оптимизация запросов в L2S сделана немного хуже, чем в EF. Я слышал, что в EF team привлекли очень толковых специалистов по оптимизации запросов, так что, возможно, это результат их работы.
3. Как показывает практика, генерируемые запросы достаточно неплохи по сравнению с контрольным, так что основная проблема в производительности ORM находится все-таки за пределами базы данных, а именно внутри ORM, в алгоритмах генерации SQL-запроса, работы с контекстом и материализации данных.
4. Я протестировал LINQ-запросы для L2S и EF. Было бы еще интересно посмотреть, как с заданием справится eSQL (Entity SQL) для EF. Если у кого-то есть опыт – поделитесь.
5. Аналогично я хотел бы узнать у специалистов насчет HQL в NH. Насколько я понял, в NH есть еще и альтернативный способ создавать сложные запросы через джойны – при помощи критериев. Какие у них плюсы и минусы?

Бонус

Ну, и напоследок бонус тем, кто дочитал до этого места :) Собственно, сгенерированные сложные запросы с джойнами для NH, EF и L2S. Не пугайтесь их, они не такие страшные, как кажутся на первый взгляд :)

NHibernate:

exec sp_executesql N'select product2_.ProductID as ProductID4_, product2_.ProductName as ProductN2_4_, product2_.UnitPrice as UnitPrice4_, product2_.SupplierId as SupplierId4_ from [Order Details] orderdetai0_ inner join Orders order1_ 
on orderdetai0_.OrderID=order1_.OrderID inner join Products product2_ on orderdetai0_.ProductID=product2_.ProductID where (product2_.ProductID=product2_.ProductID )AND(order1_.OrderID=order1_.OrderID )AND(order1_.CustomerID=@p0 
)',N'@p0 nvarchar(5)',@p0=N'BERGS'

Entity Framework:

exec sp_executesql N'SELECT 
[Project3].[CustomerID] AS [CustomerID], 
[Project3].[C1] AS [C1], 
[Project3].[C3] AS [C2], 
[Project3].[OrderID] AS [OrderID], 
[Project3].[C2] AS [C3], 
[Project3].[ProductID] AS [ProductID], 
[Project3].[ProductName] AS [ProductName], 
[Project3].[QuantityPerUnit] AS [QuantityPerUnit], 
[Project3].[UnitPrice] AS [UnitPrice], 
[Project3].[UnitsInStock] AS [UnitsInStock], 
[Project3].[UnitsOnOrder] AS [UnitsOnOrder], 
[Project3].[ReorderLevel] AS [ReorderLevel], 
[Project3].[Discontinued] AS [Discontinued], 
[Project3].[CategoryID] AS [CategoryID], 
[Project3].[SupplierID] AS [SupplierID]
FROM ( SELECT 
    [Extent1].[CustomerID] AS [CustomerID], 
    1 AS [C1], 
    [Project2].[OrderID] AS [OrderID], 
    [Project2].[ProductID] AS [ProductID], 
    [Project2].[ProductName] AS [ProductName], 
    [Project2].[SupplierID] AS [SupplierID], 
    [Project2].[CategoryID] AS [CategoryID], 
    [Project2].[QuantityPerUnit] AS [QuantityPerUnit], 
    [Project2].[UnitPrice] AS [UnitPrice], 
    [Project2].[UnitsInStock] AS [UnitsInStock], 
    [Project2].[UnitsOnOrder] AS [UnitsOnOrder], 
    [Project2].[ReorderLevel] AS [ReorderLevel], 
    [Project2].[Discontinued] AS [Discontinued], 
    [Project2].[C1] AS [C2], 
    [Project2].[C2] AS [C3]
    FROM  [dbo].[Customers] AS [Extent1]
    LEFT OUTER JOIN  (SELECT 
        [Extent2].[OrderID] AS [OrderID], 
        [Extent2].[CustomerID] AS [CustomerID], 
        [Project1].[ProductID] AS [ProductID], 
        [Project1].[ProductName] AS [ProductName], 
        [Project1].[SupplierID] AS [SupplierID], 
        [Project1].[CategoryID] AS [CategoryID], 
        [Project1].[QuantityPerUnit] AS [QuantityPerUnit], 
        [Project1].[UnitPrice] AS [UnitPrice], 
        [Project1].[UnitsInStock] AS [UnitsInStock], 
        [Project1].[UnitsOnOrder] AS [UnitsOnOrder], 
        [Project1].[ReorderLevel] AS [ReorderLevel], 
        [Project1].[Discontinued] AS [Discontinued], 
        [Project1].[C1] AS [C1], 
        1 AS [C2]
        FROM  [dbo].[Orders] AS [Extent2]
        LEFT OUTER JOIN  (SELECT 
            [Extent3].[OrderID] AS [OrderID], 
            [Extent4].[ProductID] AS [ProductID], 
            [Extent4].[ProductName] AS [ProductName], 
            [Extent4].[SupplierID] AS [SupplierID], 
            [Extent4].[CategoryID] AS [CategoryID], 
            [Extent4].[QuantityPerUnit] AS [QuantityPerUnit], 
            [Extent4].[UnitPrice] AS [UnitPrice], 
            [Extent4].[UnitsInStock] AS [UnitsInStock], 
            [Extent4].[UnitsOnOrder] AS [UnitsOnOrder], 
            [Extent4].[ReorderLevel] AS [ReorderLevel], 
            [Extent4].[Discontinued] AS [Discontinued], 
            1 AS [C1]
            FROM  [dbo].[Order Details] AS [Extent3]
            LEFT OUTER JOIN [dbo].[Products] AS [Extent4] ON [Extent3].[ProductID] = [Extent4].[ProductID] ) AS [Project1] ON [Extent2].[OrderID] = [Project1].[OrderID] ) AS [Project2] ON [Extent1].[CustomerID] = 
[Project2].[CustomerID]
    WHERE [Extent1].[CustomerID] = @p__linq__1
)  AS [Project3]
ORDER BY [Project3].[CustomerID] ASC, [Project3].[C3] ASC, [Project3].[OrderID] ASC, [Project3].[C2] ASC',N'@p__linq__1 nvarchar(5)',@p__linq__1=N'BERGS'

LINQ 2 SQL:

exec sp_executesql N'SELECT [t0].[CustomerID]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] = @p0',N'@p0 nvarchar(5)',@p0=N'BERGS'

exec sp_executesql N'SELECT [t2].[ProductID], [t2].[ProductName], [t2].[SupplierID], [t2].[CategoryID], [t2].[QuantityPerUnit], [t2].[UnitPrice], [t2].[UnitsInStock], [t2].[UnitsOnOrder], [t2].[ReorderLevel], [t2].[Discontinued], (
    SELECT COUNT(*)
    FROM [dbo].[Order Details] AS [t3]
    INNER JOIN [dbo].[Products] AS [t4] ON [t4].[ProductID] = [t3].[ProductID]
    WHERE [t3].[OrderID] = [t0].[OrderID]
    ) AS [value]
FROM [dbo].[Orders] AS [t0]
LEFT OUTER JOIN ([dbo].[Order Details] AS [t1]
    INNER JOIN [dbo].[Products] AS [t2] ON [t2].[ProductID] = [t1].[ProductID]) ON [t1].[OrderID] = [t0].[OrderID]
WHERE [t0].[CustomerID] = @x1
ORDER BY [t0].[OrderID], [t1].[ProductID]',N'@x1 nchar(5)',@x1=N'BERGS'