ORM vs Plain SQL

nick sergeev nick sergeev
gotdotnet не работает, попробую написать здесь.

Недавно в силу профессиональной занятости пришлось проводить перфоманс ревью одного проекта, который перестал адекватно работать при планируемой нагрузке. В сервис-деске запросили Visual Studio Ultimate для лоад тестов, подняли кластер из 3 машин — тест-контроллер, БД и сервер приложения и приступили к тестированию.

Ситуация следующая, при наращении нагрузки до 5 пользователей, SQL сервер выходит в плато на 100% CPU загрузки. При этом должен заметить, что сервер нам удалось получить очень мощный: 16 GB RAM, 8 Cores, RAID 0 x 4 HDD, ну в общем, кто знает тот поймет. Такая загрузка при таком количестве пользователей выглядит, мягко говоря, ненормально. Переписали на Plain SQL, запрос конечно получился не элементарный, заджойнили больше 8 таблиц. Поддержка всего этого дела сулит неприятностями. Запускаем тесты — нагрузка сервера БД держится на уровне 25%, а количество операций возросло в несколько раз. ОК, пишем отчет, получаем деньги.

Хотел расписать долго, но суть то в итоге коротка, пока архитекторы не понимают, что БД это самостоятельный организм, который требует инвестиций собственных мозгов — такие как мы будем получать овертаймы, сэйвшиты и прочие $$.
Философ
Философ
14.09.2012 06:23
Здравствуйте, huligun, Вы писали:

H>БД это самостоятельный организм, который требует...


самостоятельного онанизма.
Всё просто: если ORM позволяет что-то сделать быстрее и проще прямого SQL'я — его имеет смысл использовать, ибо по итогу всё равно придётся проводить нагрузочное тестирование/анализировать репорты сервера БД, и даже более того — оптимизировать систему целиком.
IT
IT
14.09.2012 06:36
Здравствуйте, huligun, Вы писали:

H>Запускаем тесты — нагрузка сервера БД держится на уровне 25%, а количество операций возросло в несколько раз. ОК, пишем отчет, получаем деньги.


Во-первых, ORM нужно уметь готовить, как это ни банально звучит. Во-вторых, нужно использовать правильную ORM, желательно такую, которая не даст себя приготовить неправильно.

Совсем недавно в результате переписывания прямого SQL на ORM на этом сайте, вот эта страница стала работать на пару порядков быстрее и у неё добавилось фукнциональности. Раньше мы тут уходили в даун и получали time-out exception.
Steamus
Steamus
14.09.2012 06:55
Здравствуйте, IT, Вы писали:

IT>Здравствуйте, huligun, Вы писали:


IT>Совсем недавно в результате переписывания прямого SQL на ORM на этом сайте, вот эта страница стала работать на пару порядков быстрее и у неё добавилось фукнциональности. Раньше мы тут уходили в даун и получали time-out exception.


Это говорит лишь о том, что вы плохо знаете SQL. ORM соотносится с прямым SQL примерно так, как автоматический языковый переводчик с реальным человеком переводчиком. То есть — быстро, некачественно, но, порой, приемлимо. Ибо быстро, и, главное, не требует знания языка.
IT
IT
14.09.2012 07:12
Здравствуйте, Steamus, Вы писали:

S>Это говорит лишь о том, что вы плохо знаете SQL. ORM соотносится с прямым SQL примерно так, как автоматический языковый переводчик с реальным человеком переводчиком. То есть — быстро, некачественно, но, порой, приемлимо. Ибо быстро, и, главное, не требует знания языка.


Я же говорю, нужно использовать правильные ORM, которые соотносятся с прямым SQL на 100%, не пытаются втихаря шаманить и додумывать за разработчика, а выполняют в точности то, что им сказано и делают это по возможности эффективно.

Что касается нашего случая, то, конечно же, победил не ORM, а полное переписывание старого кода. Старый SQL пытались оптимизировать, но не очень успешно. ORM всего лишь позволил переписать код быстро, поиграться с разными вариантами запросов, т.е. сделать то, что на прямом SQL заняло бы на порядок больше времени.
Steamus
Steamus
14.09.2012 07:47
Здравствуйте, IT, Вы писали:

IT>Здравствуйте, Steamus, Вы писали:


IT>Я же говорю, нужно использовать правильные ORM, которые соотносятся с прямым SQL на 100%, не пытаются втихаря шаманить и додумывать за разработчика, а выполняют в точности то, что им сказано и делают это по возможности эффективно.


Это и будет прямой SQL плюс небольшой кусок кода, создающий объект и инициализирующий его поля из результата SQL запроса. Возможно делающий это несколько раз для создания массива объектов.
Steamus
Steamus
14.09.2012 08:35
Здравствуйте, Steamus, Вы писали:

S>Здравствуйте, IT, Вы писали:


IT>>Здравствуйте, huligun, Вы писали:


IT>>Совсем недавно в результате переписывания прямого SQL на ORM на этом сайте, вот эта страница стала работать на пару порядков быстрее и у неё добавилось фукнциональности. Раньше мы тут уходили в даун и получали time-out exception.


S>Это говорит лишь о том, что вы плохо знаете SQL. ORM соотносится с прямым SQL примерно так, как автоматический языковый переводчик с реальным человеком переводчиком. То есть — быстро, некачественно, но, порой, приемлимо. Ибо быстро, и, главное, не требует знания языка.


Владимир Кочетков поставил кокетливый смайлик на моё сообщение, что видимо должно означать снисходительный намёк на то, что человек отлично владеет SQL и проблема как бы глубже моего уровня её понимания. Я верю. Ну, тогда, парни, и пишите конкретнее. Ибо сообщение вида — программа генерирует более умный SQL, чем квалифицированный разработчик... оно ведь других вариантов не оставляет.
IT
IT
16.09.2012 09:43
Здравствуйте, Steamus, Вы писали:

S>Владимир Кочетков поставил кокетливый смайлик на моё сообщение, что видимо должно означать снисходительный намёк на то, что человек отлично владеет SQL и проблема как бы глубже моего уровня её понимания. Я верю. Ну, тогда, парни, и пишите конкретнее. Ибо сообщение вида — программа генерирует более умный SQL, чем квалифицированный разработчик... оно ведь других вариантов не оставляет.


А кто-то где-то говорил, что "программа генерирует более умный SQL, чем квалифицированный разработчик"?
Steamus
Steamus
16.09.2012 11:25
Здравствуйте, IT, Вы писали:

IT>А кто-то где-то говорил, что "программа генерирует более умный SQL, чем квалифицированный разработчик"?


Нда, верно. По сути вы сказали, что редизайн старого кода увеличил производительность и что А ORM, по любому, нужно "уметь готовить".
kochetkov.vladimir
kochetkov.vladimir
17.09.2012 12:40
Здравствуйте, Steamus, Вы писали:

S>Владимир Кочетков поставил кокетливый смайлик на моё сообщение


Мда, от дальнейшего общения в данной теме я, пожалуй, воздержусь. А то вдруг еще решишь, что я заигрывать начал...
huligun
huligun
14.09.2012 07:46
Отвечу просто, мой кумир stackoverflow, а они давно перешли на Dapper
Философ
Философ
14.09.2012 07:54
не сотвори себе кумира (с)
IT
IT
14.09.2012 08:31
Здравствуйте, huligun, Вы писали:

H>Отвечу просто, мой кумир stackoverflow, а они давно перешли на Dapper


Dapper — это другая крайность. Это простенький маппер, в которой абсолютно всё без разбора принесено в жертву производительности. От него помощи в работе с SQL — чуть. А после появления в C# анонимных типов и инициализаторов чуть больше, чем ничего. Его эффект проявляется только при очень большом количестве очень маленьких запросов к БД, где маппинг действительно играет существенную роль. Большинство ORM, особенно тяжёлых, ему в этом вопросе сливают как стиральная машина после стирки.

Но то о чём говорит ТС совсем другая история. 8 джоинов — это не тривиальныый запрос. На его фоне затраты на маппинг не видны даже в микроскоп, если, конечно, не используется какой-нибудь абсолютный тормоз, типа сабсоника или NH. Для таких задач у нас на первый план выходят системы, которые облегчают прежде всего работу с SQL, его простую генерацию и по возможности валидацию во время компиляции. И как раз вот тут Dapper сосёт вместе с плоским SQL как трофейный пылесос. Я уже приводил тут свой пример. С плоским SQL просто никто не хотел связываться, а на Linq эту проблему решили в лёт. Могу показать код, если надо, чтобы оценить простоту решения и сложность генерируемого SQL.
Философ
Философ
14.09.2012 08:38
Здравствуйте, IT, Вы писали:

IT>Могу показать код, если надо, чтобы оценить простоту решения и сложность генерируемого SQL.


да, любопытно
Enomay
Enomay
17.09.2012 05:40
IT> 8 джоинов — это не тривиальныый запрос.

тут как бы еще проблема в том, что мы не видим запрос, который генерил ORM до переписывания на plain-SQL.
если там был select 1+n, то это не проблема ORM как таковая.
gravatar
Аноним
17.09.2012 04:12
Нет, там действительно просто один тяжелый запрос с джоинами и подзапросами.
Но объем кода сгенерированный ОРМ получился в несколько раз больше ручного кода, что также является сложностью для сервера БД,
так как ему все это нужно как минимум парсить.
Enomay
Enomay
18.09.2012 05:45
Здравствуйте, Аноним, Вы писали:

А>Нет, там действительно просто один тяжелый запрос с джоинами и подзапросами.

А>Но объем кода сгенерированный ОРМ получился в несколько раз больше ручного кода, что также является сложностью для сервера БД,
А>так как ему все это нужно как минимум парсить.

plain-sql ему тоже нужно парсить.
неужто ORM сгенерировал на столько кривой код?
было бы интересно сравнить. и знать что за ORM такой )
IT
IT
18.09.2012 01:24
Здравствуйте, Enomay, Вы писали:

E>неужто ORM сгенерировал на столько кривой код?


Скорее всего там был сгенерирован не один запрос.
IT
IT
14.09.2012 09:15
По просьбам турдящихся.

Это C# код с моими комментариями:

  Скрытый текст
public ActionResult ActTop(string forum, int? days, int? start)
{
    var model = new ActTopModel
    {
        Days         = days,
        CurrentForum = forum,
        Start        = start.GetValueOrDefault(0),
        PageSize     = _pageSize
    };

    using (var db = new RsdnDB())
    {
        int? forumID = null;

        if (!string.IsNullOrEmpty(forum))
        {
            forumID = db.Forums
                .Where(f => f.ShortName == forum.ToLower())
                .Select(f => (int?) f.ID)
                .FirstOrDefault();

            if (forumID == null)
                return ErrorMessageBoxFormat("Форум '{0}' не найден", (object)forum);
        }

        // Версии запроса для двух параметров: days и forumID.
        // Всего 4 варианта.
        //
        var q1 = days == null || days == 0
            ? forumID == null
                ? db.Accounts.Select(a => new
                {
                    a,
                    Count = a.Messages.Count(m => m.Forum.ReadLevel == AccessLevel.Anonymous)
                })
                : db.Accounts.Select(a => new
                {
                    a,
                    Count = a.Messages.Count(m => m.ForumID == forumID && m.Forum.ReadLevel == AccessLevel.Anonymous)
                })
            : forumID == null
                ? db.Accounts.Select(a => new
                {
                    a,
                    Count = a.Messages.Count(m =>
                        m.CreatedOn >= Sql.CurrentTimestamp.AddDays(-days.Value) &&
                        m.Forum.ReadLevel == AccessLevel.Anonymous)
                })
                : db.Accounts.Select(a => new
                {
                    a,
                    Count = a.Messages.Count(m =>
                        m.ForumID == forumID &&
                        m.CreatedOn >= Sql.CurrentTimestamp.AddDays(-days.Value) &&
                        m.Forum.ReadLevel == AccessLevel.Anonymous)});

        // Тело запроса.
        //
        var q2 =
            from t in q1
            where t.a.ID > 0 && t.Count > 0
            orderby t.Count descending , t.a.ID
            select
                new ActTopModel.AccountData
                {
                    ID = t.a.ID,
                    DisplayName = t.a.GetDisplayName(),
                    Title = t.a.Title,
                    TitleColor = t.a.TitleColor ?? 0,
                    UserRole =
                        t.a.ForumModerators.Any() && t.a.AccessLevel == AccessLevel.TeamMember && forumID != null
                            ? UserRole.Moderator
                            : t.a.ForumExperts.Any() && t.a.AccessLevel == AccessLevel.User
                                ? UserRole.Expert
                                : (UserRole) t.a.AccessLevel,
                    Count = t.Count
                };

        // Выполняем первый запрос для получения подверженных запросу количества записей.
        //
        model.Total = q2.Count();

        // Организация пейджинга.
        //
        if (start != null)
            q2 = q2.Skip(start.Value);

        // Запрашиваем данные.
        //
        model.AccountDatas = q2.Take(_pageSize).ToArray();

        model.CurrentForum = forum;
    }

    return View(model);
}

Здесь у нас 4 версии запрос в зависимости от параметров. Сначала берётся Count, т.е. 4 возможных варианта запроса, затем по необходимости строится пейджинг и выполняется запрос. Итого 12 вариантов запроса

Далее 3 запроса (из 12 возможных) на SQL:

Получение Count:
  Скрытый текст
-- DECLARE @forumID Int32
-- DECLARE @p1 Double

-- SET @forumID = 8
-- SET @p1 = -31

SELECT
    Count(*) as [cnt]
FROM
    (
        SELECT
            [a].[userid] as [ID],
            (
                SELECT
                    Count(*)
                FROM
                    [forum_tree] [c]
                        INNER JOIN [forum_groups] [t1] ON [c].[gid] = [t1].[gid]
                WHERE
                    [a].[userid] = [c].[uid] AND
                    [c].[gid] = @forumID AND
                    [c].[dte] >= DateAdd(Day, @p1, CURRENT_TIMESTAMP) AND
                    [t1].[ucread] = 4
            ) as [c1]
        FROM
            [users] [a]
    ) [t]
WHERE
    [t].[ID] > 0 AND [t].[c1] > 0

Выборка первых записей:
  Скрытый текст
-- DECLARE @forumID Int32
-- DECLARE @p1 Double

-- SET @forumID = 8
-- SET @p1 = -31

SELECT TOP (100)
    [t].[ID] as [ID1],
    [t].[LoginName] as [LoginName1],
    [t].[UserNick] as [UserNick1],
    [t].[RealName] as [RealName1],
    [t].[Title] as [Title1],
    Coalesce([t].[TitleColor], 0) as [c1],
    CASE WHEN EXISTS(
        SELECT
            *
        FROM
            [forum_moderators] [c]
        WHERE
            [t].[ID] = [c].[uid]
    ) THEN 1 ELSE 0 END as [c2],
    [t].[AccessLevel] as [AccessLevel1],
    CASE WHEN EXISTS(
        SELECT
            *
        FROM
            [forum_experts] [c3]
        WHERE
            [t].[ID] = [c3].[uid]
    ) THEN 1 ELSE 0 END as [c4],
    [t].[c6] as [c61]
FROM
    (
        SELECT
            [a].[userid] as [ID],
            (
                SELECT
                    Count(*)
                FROM
                    [forum_tree] [c5]
                        INNER JOIN [forum_groups] [t1] ON [c5].[gid] = [t1].[gid]
                WHERE
                    [a].[userid] = [c5].[uid] AND
                    [c5].[gid] = @forumID AND
                    [c5].[dte] >= DateAdd(Day, @p1, CURRENT_TIMESTAMP) AND
                    [t1].[ucread] = 4
            ) as [c6],
            [a].[username] as [LoginName],
            [a].[usernick] as [UserNick],
            [a].[realname] as [RealName],
            [a].[Title],
            [a].[TitleColor],
            [a].[userclass] as [AccessLevel]
        FROM
            [users] [a]
    ) [t]
WHERE
    [t].[ID] > 0 AND [t].[c6] > 0
ORDER BY
    [t].[c6] DESC,
    [t].[ID]

Пейджинг:
  Скрытый текст
-- DECLARE @forumID Int32
-- DECLARE @p1 Double

-- SET @forumID = 8
-- SET @p1 = -31

SELECT *
FROM
(
    SELECT
        t2.*,
        ROW_NUMBER() OVER
        (
            ORDER BY
                oby DESC,
                oby1
        ) as rn
    FROM
    (
        SELECT
            [t].[ID] as [ID1],
            [t].[LoginName] as [LoginName1],
            [t].[UserNick] as [UserNick1],
            [t].[RealName] as [RealName1],
            [t].[Title] as [Title1],
            Coalesce([t].[TitleColor], 0) as [c1],
            CASE WHEN EXISTS(
                SELECT
                    *
                FROM
                    [forum_moderators] [c]
                WHERE
                    [t].[ID] = [c].[uid]
            ) THEN 1 ELSE 0 END as [c2],
            [t].[AccessLevel] as [AccessLevel1],
            CASE WHEN EXISTS(
                SELECT
                    *
                FROM
                    [forum_experts] [c3]
                WHERE
                    [t].[ID] = [c3].[uid]
            ) THEN 1 ELSE 0 END as [c4],
            [t].[c6] as [c61],
            [t].[c6] as [oby],
            [t].[ID] as [oby1]
        FROM
            (
                SELECT
                    [a].[userid] as [ID],
                    (
                        SELECT
                            Count(*)
                        FROM
                            [forum_tree] [c5]
                                INNER JOIN [forum_groups] [t1] ON [c5].[gid] = [t1].[gid]
                        WHERE
                            [a].[userid] = [c5].[uid] AND
                            [c5].[gid] = @forumID AND
                            [c5].[dte] >= DateAdd(Day, @p1, CURRENT_TIMESTAMP) AND
                            [t1].[ucread] = 4
                    ) as [c6],
                    [a].[username] as [LoginName],
                    [a].[usernick] as [UserNick],
                    [a].[realname] as [RealName],
                    [a].[Title],
                    [a].[TitleColor],
                    [a].[userclass] as [AccessLevel]
                FROM
                    [users] [a]
            ) [t]
        WHERE
            [t].[ID] > 0 AND [t].[c6] > 0
    ) t2
) t3
WHERE
    t3.rn BETWEEN 77 AND 176

Эти три запроса принципиально разные, остальные вариации на тему.

Теперь внимательно смотрим, сравниваем количество кода, представляем какие усилия потребовались бы для ручного выпиливания 12-ти вариантов прямого SQL, ищем что сгенерировано не так.
gravatar
Аноним
05.10.2012 06:46
Здравствуйте, IT, Вы писали:

IT>Это C# код с моими комментариями:


Ты всегда переменные называешь t, a, f, m, q1?
IT
IT
06.10.2012 05:43
Здравствуйте, Аноним, Вы писали:

IT>>Это C# код с моими комментариями:

А>Ты всегда переменные называешь t, a, f, m, q1?

Длинные имена локальных переменных — это мусор в коде. Впрочем, это всё вкусовщина.