1. SQL / Говнокод #2305

    −125.1

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    create procedure [dbo].[pbsp_GetClientUsers]
    
    	(
            @ClientId int,
            @fname varchar(100),
            @lname varchar(100),
            @email varchar(150)
    	)
    
    AS
    	
    DECLARE @SQL varchar(1000)
    
    Set @SQL = 'Select TOP 500 *, tblRoles.title AS Role from tblUser INNER JOIN
            tbl_mtm_UserRoles ON tblUser.UserId = tbl_mtm_UserRoles.UserId INNER JOIN
            tblRoles ON tbl_mtm_UserRoles.RoleId = tblRoles.Id where tblUser.ClientId = ' + STR(@ClientId) + ' ' 
    
    if LEN(@fname) > 0
        Set @SQL = @SQL + ' AND tblUser.fName like ''' + @fname + '%'' '
        
    if LEN(@lname) > 0
        Set @SQL = @SQL + ' AND tblUser.lName like ''' + @lname + '%'' '
        
    if LEN(@email) > 0
        Set @SQL = @SQL + ' AND tblUser.Email like ''' + @email + '%'' '        	
        
    Set @SQL = @SQL + ' Order by tblUser.lName, tblUser.Fname'        	
    exec (@SQL)

    По долгу работы приходится местами переписывать унаследованный код. Я держалась неделю, но после этого шедевра все таки зарегилась на сайте и решила поделиться перлом. Интересно, что бы делали, если бы параметров еще штук пускай даже двадцать добавить?

    Запостил: Крендель, 21 Декабря 2009

    Комментарии (20) RSS

    • А как по вашему нужно было написать?
      Ответить
      • SELECT TOP(@Count) *
        FROM dbo.tblUser
        WHERE
        (@FirstName IS NULL OR fName LIKE @FirstName)
        AND (@LastName IS NULL OR lName LIKE @LastName)
        AND (...

        Я полагаю что как то так. Хотя конечно если вы считаете что это нормальная практика, то пора Вам покупать билет в Индию или оторвать себе руки, чтобы работы меньше было.
        Ответить
        • Показанный Вами пример удобен если нет необходимости оптимизации запроса по скорости. Да он читабелен и понятен человеку, но есть одно но: запрос должен четко спрашивать то что нужно получить от БД, чем точнее оформлен запрос - тем более еффективный план будет определен для него.
          С какими объемами данных работали? Как давно в мире БД?
          Ответить
          • да ладно, есть работа тем, кто разбирается в скуле и умеет оптимизировать. так что пусть пишут.
            Ответить
    • Дело в том что тут ни о какой оптимизации речи не может быть. Выбрать данные из таблицы с максимум 100 записями я не считаю нужным оптимизировать столь извращенными способами.
      Ответить
      • Интересно, зачем в запросе TOP 500, если максимум 100 записей в таблице?
        Ответить
    • Крендель, Вы не правы.
      Приведенный Вами пример чужого кода не очень хорош, но в плане производительности будет гораздо лучше того, что Вы написали самостоятельно.

      IMHO было бы лучше дать начальные значения параметров процедуры = null, считать не Len(@param) а выполнять проверку if @param is not null при формировании строки динамического запроса и выполнять получившийся запрос не через Exec(@param) а через sp_executesql.
      Кроме того при перечислении таблиц в динамическом запросе практически необходимо добавлять имя схемы, а также можно посоветовать использовать псевдонимы таблиц.

      А про билеты в Индию и отрывание рук Вы зря. Если бы мне пришлось выбирать, кого отправлять в ссылку в Индию - Вас или неизвестного ваятеля, то я скорее всего отправил бы Вас.
      Не судите и не судимы будете (с)
      Ответить
      • +1

        Самому недавно пришлось править подобный г-код (не тот, который динамический, а тот, что Крендель считает правильным). Только параметров было не 3, а 30-40 и запрос получался экрана на 3. Я бы на месте оптимизатора застрелился.

        Только вместо like ''' + @lname + '%'' ', лучше использовать

        set @lname = '%' + @lname
        в запросе 'like @lname'
        и exec sp_executesql ... с передачей @lname и прочих параметров.
        Ну, чтоб SQL-injection не случилось...
        Ответить
        • Начнем с того, что dynamic sql - зло в общем случае,
          ибо
          1. план нужно оптимизатору каждый раз строить заново
          2. Понять окончательный текст запроса сможете иногда
          только в профайлере.

          А 30-40 параметров - зло уже само по себе.
          Ответить
          • 1) a) Динамик sql также можно делать в виде параметризированных запросов (select * from table1 where fname like @param1), в случае MSSQL используем sp_executesql...
            б) Oracle к примеру умеет преобразовывать статик sql в параметризированные запросы (читаем дядю Кайта)
            2) никто не мешает иметь две процедуры: одна формирующая текст запроса, вторая выполняющая запрос.
            P.S. Зарубливаем себе на носу: чем точнее запрос, тем он "понятнее" оптимизатору.
            Ответить
            • 1. Да, от динамического sql можно добиться большего (чего не было сделано в исходной интерпретации)
              Но учтите всевозможные комбинации условий (сколько возможных комбинаций при 30 параметрах?)
              2. Можно и больше. Но чем больше процедур, тем менее удобно.

              Увы, оптимизатору бывает не очень понятны и простые запросы.
              Иногда приходится объяснять хинтами )
              Вообще проблема говнокода зачастую не столько в неоптимальности
              (потеря 50 тактов на нечасто вызываемой функции),
              сколько в невозможности такой код нормально поддерживать, править, находить в нем ошибки ...
              Ответить
          • 1a на больших объемах данных затраты на построение плана пренебрежимо малы по сравнению с трудоёмкостью выборки
            1б SQL-сервер планы динамических запросов умеет кэшировать. Особенно, если эти запросы параметризованы.
            1в Что лучше - построить новый план для простенького текста запроса или производить ту же выборку по заведомо неоптимальному плану для запроса длиной на несколько экранов?

            По поводу 30-40 параметров - каким еще образом позволить юзеру гибко указать критерии отбора при возможном (по ТЗ) сочетании из этих 30-40 условий? Причем в зависимости от выбранных критериев в запросе могут участвовать от 1 до 10 таблиц.
            Ответить
            • 1а. Про объемы ничего не сказано. Может там порядка 100 записей?
              1б. Что-то я не вижу параметризации в исходном коде.
              1в. Производить выборку по заведомо оптимальному плану для запроса длиной в пять строчек.
              Каков вопрос ....

              Про 30-40 условий и критерии отбора.
              Есть такое понятие, как архитектура данных.
              Можно вообще свести все в одну таблицу с 200ми полями и по ней выбирать.
              Ответить
        • >>Я бы на месте оптимизатора застрелился.
          Очень инетерсно, что тогда план показывал
          Потому как МС-овский оптимизатор обычно выкусывает заведомо ложные ветки без особых проблем
          Единственно где встречал проблемы с производительностью для таких конструкций - это ДБ2, а с МС-ом нормально всё было.
          Ответить
    • и при этом ни одного упоминания, что выполнения динамического кода идет от подключенного пользователя, а не от владельца процедуры. даешь всем полные права!
      Ответить
      • + сколько "полезного" кода можно вписать в 100-150 символов? =)
        Ответить
    • Ту как миниму будут sql инекции, и не забываем что динамик sql выполняетс в контексте запустивщего, т.е. нам небходимо будет дать полный доступ на все таблицы из запроса. И ИМХО странная выгода от ХП, тут обычным запросом все сделать можно.
      Ответить
    • В таблице, с которой идет работа, ожидается данных максимум сто записей. Раз. Такой код поддерживать просто невозможно. Обычно проще переписать чем переправить то что офигенно кто то оптимизировал, или просто писал по глупости. Кто же его знает. В третьих. Если писали такие умные люди, то почему этот проект в итоге оказался у нас? может все не так и клево было? И вообще всегда надо понимать, где критический код а где нет. Где оптимизация нужна как воздух, а где она вообще ничего не даст. А динамические запросы - это зло в квадрате.
      Ответить
      • Не залоганный крендель.
        PS Про объемы данных было сказано в самом начале. И сама процедура будет вызываться крайне редко.
        Ответить
    • Подход вобщем-то годный, реализация корява, это есть. Динамические запросы стоит юзать если в статике ну совсем тупняк выходит.
      Ответить

    Добавить комментарий