Thursday, November 13, 2008

Сравнение производительности .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'

17 comments:

  1. Большое спасибо за статью.

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

    Я правильно понял, что это как раз запросы, которые герерирует тест CustomerProducts?

    Просто ты в статье немного ввел путаницу категориями "простой набор", "сложный набор", "сложный запрос".

    Мне кажется что гораздо логичнее было всегда следовать только двум категориям: "один запрос с соединением" и "набор простых запросов".

    Либо как вариант привязать все к названию соответствующего Unit-теста.

    Ну это так, чисто косметические предложения. :)

    В целом меня очень удивил результат NH vs EF. Не ожидал что у NH запрос будет гораздо чище.
    С другой стороны непонятно почему EF использовал LEFT OUTER JOIN, вероятно это было сделано для упрощения кодогенерации.

    ReplyDelete
  2. Как по мне, так Active Record/NHibernate просто всех порвал :)
    Во всяком случае его запрос с соединениями выглядит так, как это пишет разработчик вручную.
    Хотя конечно все упирается в дальнейшую полировку EntityFramework силами Microsoft :). С другой стороны у Active Record нет никакой инструментальной поддержки в Visual Studio, а жаль :(

    Так что будущее таки за EntityFramework IMHO.

    ReplyDelete
  3. Леш, спасибо за дополнение о путанице в названиях тестов :) Поменял текст немного, надеюсь, теперь более понятно.

    NH всех "порвал", потому что там я просто фактически написал этот запрос на HQL (SQL для объектов NH). В EF есть похожая вещь - Entity SQL, наверно, более честно было бы сравнивать именно их. Плюс не забывай, что когда ты пишишь запрос на этом самом HQL или Entity SQL, ты пишешь строку со всеми вытекающими проблемами при переименовании классов/полей. При использовании LINQ (L2S, EF) эта проблема будет найдена на этапе компиляции.

    Мне намного более интересны результаты L2S vs EF в данном контексте :) L2S вот зачем-то включил в запрос дополнительный столбик с количеством строк в таблице Orders. Наверно, он их использует в каких-то целях внутри, может, даже для оптимизации будущих запросов.

    ReplyDelete
  4. Добрый времени суток.
    Александр, мне кажется не стоило включать nhibernate в тесты, для получения более достоверных результатов, необходимо иметь экспертизу во всех сравниваемых продуктах.

    ReplyDelete
  5. Никита, мне было интересно посмотреть и на NHibernate тоже. Вполне возможно, что я допустил некоторые ошибки, однако не вижу причины, почему я не могу выкладывать полученные результаты. Если вы являетесь экспертом в NH и нашли ошибки в предлагаемом мною примере, опишите их для всех - думаю, и мне, и остальным это будет полезно. Одну свою ошибку я честно признал и постарался ее исправить.

    Если же вы говорите о каких-то оптимизациях, которые я не провел в NH, то могу вам сказать, что я не использовал никакие оптимизации также для EF и L2S. Так что похоже, все честно.

    ReplyDelete
  6. Во-первых, спасибо за проделанную работу! Скажу честно, читал все предвзято, и радовался за NH, там где он рулит :о) с L2S и EF работать мне приходилось немного 1 неделю и 1.5 месяца соответсвенно, поэтому знаком с ними весьма поверхностно. Очень не понравились следующие вещи:
    1. Дизайнер хорошо иметь как утилиту, но когда без него ничего нельзя сделать это плохо. Во-первых, я противник кодогенерации, а во-вторых, неоднократно сталкивались с конфликтами designer.cs при апдейтах - и это на маленьком проекте. О том что происходит на средних проектах я даже подумать боюсь.
    2. Вдогонку к 1 - отсутсвие нативной поддержки POCO. Нагулил стороннюю штуку, но попробовать руки не дошли. http://code.msdn.microsoft.com/EFPocoAdapter
    3. Очень расстроил подход Update Schema from DB. Иногда приходилось маппинг в блокноте править. Ну и вообще, рекомендации начинать изменения с модели применять сложно.
    4. мы использовали Бету - нативного сапорта для энумов там не было. Выкрутились тем, что в партиал классах писали свойства-обвертки. Работает, но тошно.
    5. для оператора IN в EF приходилось писать хак - кастомный expression builder. Хотя в L2S все замечательно работало ;)
    6. Воистину ленивый Lazy Load. Пока не дернешь Load, ничего не произойдет. А смысл?
    Это то, что навскидку вспомнил.

    В противовес всем недостаткам очень понравилась возможность писать критерии на лямбда выражениях. Хотя и не обошлось без танцев с бубном ;)

    По поводу сложных запросов в NH. Есть 2 подхода CriteriaAPI и HQL. В предыдущих версиях HQL позволял строить более сложные запросы, но начиная с NH 2.0 (а может и 1.2 :)) с помощью Criteria API можно элегантно строить очень сложные запросы с подзапросами, группировками, пейждингом и т.п. Остаются строки в названих пропертей, но тут уж извините - приходится в тесты заворачивать. Простой пример можно посмотеть тут http://nhforge.org/doc/nh/en/index.html#querycriteria.

    ReplyDelete
  7. и еще небольшой коментарий по поводу инциндета с Supplier. NH по умолчанию пытается вытащить все данные, о которых ему рассказали в маппинге. Для того чтобы при попытке вытащить одного юзера не вытащить пол базы следует использовать Lazy Load. Тут возможно 2 варианта
    1. данные загрузятся при обращении к свойству или коллекции.
    2. Фетчить нужные данные в основном запросе. http://nhforge.org/doc/nh/en/index.html#querycriteria-dynamicfetching

    2й вариант позволяет значительно уменьшить количество раундтрипов к базе (проблема N+1)

    ReplyDelete
  8. Vitalya: Мы тоже сталкивались с этими проблемами в бете, но в релизе они хотя бы основные баги пофиксили. Я, конечно, не говорю про идеологические проблемы вроде POCO. Lazy load мы прохачили довольно хитроумным способом, наверно, надо об этом отдельный пост сделать :)

    Спасибо за комментарии по теме и советы. Я вижу, что несмотря на то, что у EF есть linq, query-подход NH, который использует CryteriaAPI, все же будет погибче.

    ReplyDelete
  9. А я еще и крестиком вышивать умею (с)
    В NH 2.1 будет родная поддержка LINQ, так же для 2.0.1 есть сторонние реализации. По отзывам вроде набор функциональности ограничен, но то что есть вроде работает. Мы еще сами эту фичу не использовали.

    ReplyDelete
  10. Hello Alex :)

    I was very glad to read your blog posts and evaluate the test solution... Awesome! Good work!

    What do you think about the idea to port the testing solution to MS unit tests and use Team System load testing? If it is interesting to you I can help. Also I think my team has the know how (though not the time :) ) to optimize the Entities test to use the eSQL.

    That said I would like to see some commercial products added to the comparison matrix - if the idea is worth I can contact you by e-mail or ICQ or both and do some really nice work together :)
    Also I must say that I work for one of the commercial vendors, so I am very interested to come up with some more real test results that are vendor independent. Cheers
    and 'S novim godom' :)

    ReplyDelete
  11. Hi Dimitar :) Thank you for the comment. At the moment I am creating a new test application as the part of EF performance speech preparation. It will include comparisons of LINQ and eSQL and some other performance features analysis. I will be sharing it as well and making a post with some results after speech session. So, if it would be interesting for you - feel free to modify it :) I will be pleased to work with you in this area in the future.

    Regarding the testing solution I don't think it will be very useful to make load testing here. However, why not? :) Let's try it. You can use my first test solution and let me know if you achieved any results. Then we will correct the course of testing.

    Thanks again for the interest in my work and looking forward to having any news from you :)

    ReplyDelete
  12. Hi Alex,
    To be more exact what I reffed to as 'load testing' is the exact 'load test' definition inside TFS. Actually this is just a specialized test container that can host unit tests (I will port the ORM requests to unit tests), and then you can apply different test strategies to this specialized container like: test duration, iterations, 'think timing', also it has integrated ADO.NET and SQL server counters and automatically handles SQL/execution plan profiling without an external tool. My point is that it will be a complete use case with the help of VSTS. What do you think? Should I put some work in it?
    Also I am awaiting to see/read your presentation on the EF topic and the sample app. If possible (and you do recording during the group meeting) you can share some videos of the session as well :)

    Thanks in advance.

    ReplyDelete
  13. Dimitar, I've got your idea. I didn't think about 'load testing' from such point of view :) That seems to be really good idea if TFS provides all these kinds of metrics and measures. Thus, if you have some spare time at least to change the solution for few tests we would be able to see the results of this approach. Unfortunately, I don't have TFS installed somewhere at the moment but I will be happy to take a look at your findings.

    I will definitely provide the code and presentation in English. Don't know about the video at the moment - it depends on organizers :)

    ReplyDelete
  14. Hi Alex,
    just wanted to know how to contact you by IM/Mail, as I prepared the MS Test solution, fired a couple of test and have some results, but I changed some queries, got different results... Anyway there is a lot of information I want to share and I don't think the appropriate way is through comments in your blog :)
    We can make contact on Skype or ICQ possibly? My Skype name is 'dedalcom' (full name: Dimitar Kapitanov). Please send details so that we could establish some kind of communication.

    ReplyDelete
  15. Dimitar, I have added you to my skype contacts. So, please contact me there :)

    Vitalya, раз пошла такая пьянка - можно твои контакты тоже узнать? :)

    ReplyDelete
  16. skype vitalyal :) мылом тогда уже по скайпу обменяемся :)

    ReplyDelete
  17. Hi Alex, unfortunately didn't got any Skype invitation request, could you try again please? Also you can contact me at dedalcom_at_Gmail_dot_com to interchange contacts if there is no other way to do so. Thanks in advance.

    ReplyDelete