Повышение скорости работы SQL-запросов

    Сразу оговорюсь, запросы в примерах – Transact SQL, он мне как-то роднее =)
    Но принципы, в общем-то, должны работать везде.
    Статья не претендует на новизну, и тем более, на полноту. Я лишь попытался вспомнить часто встречающиеся ошибки или недочеты в запросах, которые приводят к медленной работе с БД.

    Поиск показал, что статья частично пересекается с этим топиком, но не во всем =)



    Типы данных в полях


    Самое очевидное – не использовать типы данных «с запасом». То есть если у нас есть поле «ICQ» типа VarChar, делать его длиннее 10 символов бессмысленно. Аналогично, если есть внешний ключ к справочнику, в котором всего несколько записей, нет смысла задавать ему тип Int, хватит и SmallInt. Несмотря на очевидность ошибки, встречается повсеместно.

    Использование * в запросе


    Вообще говоря, было много споров на эту тему, но я стараюсь не использовать «*» в SQL-запросах.
    Во-первых, явное перечисление выбираемых полей повышает читабельность кода.
    Во-вторых, в выборке далеко не всегда нужны все поля таблицы. А если мы связываем в запросе несколько таблиц, то практически всегда конструкция «Select *» потянет из базы в выборку кучу ненужных полей, например, ключи, по которым связаны таблицы. Столкнулся один раз с ситуацией, когда в таблице в текстовом поле хранились наименования файлов, а в бинарном поле – их содержимое. И запрос, который должен был всего лишь выдавать список файлов, грузил в память сервера еще и их содержимое. Тормозило это безбожно.

    Использование курсоров


    Людям, привыкшим к императивным языкам программирования, курсоры в SQL – как бальзам на душу. Ведь цикл по записям – это так привычно и понятно. Иногда из-за этого рождается медленный код. Пример:
    Предположим, у нас есть таблица, в ней 4 поля: ID, Value1, Value2 и Summa. Задача: пересчитать таблицу, то есть посчитать значение Value1+Value2 и положить в поле Summa.

    Вариант 1, с курсором:


    1. DECLARE @ID int
    2. DECLARE @Val1 int
    3. DECLARE @Val2 int
    4. DECLARE #curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR 
    5. SELECT ID, Value1, Value2 from Table1
    6. OPEN #curr FETCH #curr INTO @ID, @Val1, @Val2
    7. WHILE @@FETCH_Status=0 
    8. BEGIN 
    9. UPDATE Table1 SET Summa=@Val1+@Val2 where ID=@ID
    10. FETCH #curr INTO @ID, @Val1, @Val2
    11. END CLOSE #curr DEALLOCATE #curr


    Вариант 2, без курсора:


    1. UPDATE Table1 SET Summa=Value1+Value2


    Понятно, что на таком простом примере вариант 2 очевиден. Но при более сложных вычислениях, для простоты реализации программист выбирает вариант с курсором – и ощутимо проигрывает в скорости.

    Использование индексов


    Без комментариев. Про индексы забывают сплошь и рядом, особенно новички.

    Использование хранимых процедур


    При выполнении сложных вычислений, использующих много значений из БД, их лучше оформить в виде хранимых процедур на сервере, нежели вычислять на клиентской стороне – зачем передавать на клиент исходные данные для вычислений, когда можно передать только результат.

    Использование временных таблиц


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

    Пожалуй это все, что пришло в голову навскидку, если статья кого-нибудь заинтересует, можно повспоминать еще.
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 15

      0
      Подписался бы под каждым словом. Особенно «Использование * в запросе». Поди разбери в коде, потом какие колонки вообще есть, а так видно.
        +1
        Ну звездочки это то, с чего обычно начинается изучение SQL, в большинстве учебников, примеров и т.п. самый базовый запрос — SELECT * FROM table, вот соответственно и получается такая ситуация, писали-бы там сразу SELECT col1, col2, col3 FROM table, или хотя-бы предупреждали-бы о минусах такого подхода — и было-бы таких ошибок меньше.
        0
        как типы данных (вернее их размерность) повлияет на скорость? можно прирост в цифрах?
          0
          Допустим есть у нас табличка только с числовыми полями, и мне надо вытащить оттуда второе поле из записи №3454324 — поскольку длина строки в таблице фиксирована, можно сразу сказать, из какой части файла читать, это практически мгновенное действие.

          Когда все строки переменной длины (много text, varchar и т.д.) — будет замедление при больших таблицах.
            0
            Косвенно =) меньший размер БД, меньший объем отдаваемых в выборке данных.
            0
            То есть если у нас есть поле «ICQ» типа VarChar, делать его длиннее 10 символов бессмысленно
            Т.е. когда у ICQ вместо десятизнаков появятся 11-ти(а это может произойти достаточно скоро) — вам надо будет об этом во-первых как-то узнать, во-вторых менять структуру БД, точнее менять размерность поля. На практике это означает, что вы узнаете об этом уже как о баге — вероятнее всего от пользователей вашего ресурса/приложения.
            Это нехороший подход. Конечно не надо для ICQ делать varchar(255), но хотя бы какой то разумный запас оставлять надо я бы поставил хотя бы на несколько лет вперед varchar(12). Тем более, что с точки зрения производительности — разница мизерна
              0
              Сейчас вроде как хватает 9 знаков. 10 знаков UIN'а покроют все население Земли с запасом, тем более, что 100% интернетизация населения планеты ой как не скоро
                0
                Упс… чего то на меня поздяя ночь плохо подействовала сегодня — неправильно разряды посчитал, в чиселке 487299860 насчитал 10 знаков, извините меня :)

                Насчет покрытия всего населения и интернетизации не совсем верно, не являясь злостным асечником, за свою жизнь зарегестрировал уинов 6-7. На работе практически весь состав офиса менял как минимум один раз аську за несколько лет, потому что меняли пароль, угодняли и пр.
                +1
                а что мешает сделать bigint вместо varchar и успокоиться?..
                +4
                В MSSQL длина заявленной varchar на производительность либо не влияет, либо влияет так слабо, что я не смог это отследить.
                Если в колонке varchar (10) и varchar (400) находятся строки по 10 символов — то скорость работы выборок и поиск по ним — не изменяется.

                И в целом, рассказать о производительсноти MSSQL можно гораздо больше! Автор, исследуй вопрос!
                  0
                  А как же порядок таблиц? Ведь даже если в выборе 10, они соединяются по 2 и надо стараться что бы результаты на каждом шаге были минимальны :)
                    +1
                    JOIN таблиц и его оптимизация — тема отдельного исследования, там можно очень много накопать. Может быть займусь в будущем.
                    0
                    На счёт типов данных очень спорно:
                    varchat на то и var что не хранит пустые хвосты строк, в отличие от char, где строка занимает всегда один и тот же объём. Хотя, допускаю, что размерность varchar может влиять на размер индекса.

                    int имеет размерность машинного слова — т.е. обрабатывается по логике вещей быстрее. А если при сравнении будет преобразование типов происходить.

                    Короче, это всё очень сильно зависит от самого запроса и сути данных. Следование рекомендациям автора в ряде случаев наоборот замедлит работу.
                      0
                      Я и не претендовал на абсолютную применимость рекомендаций. Кстати, по записям типа char поиск идет быстрее, чем по varchar.
                      Про int и длину машинного слова: вы не учли объем памяти, который займет общая выборка. При обработке выборки объемом в несколько миллиардов записей уже приходится задумываться о нехватке оперативной памяти сервера — и экономить каждый байт.
                        0
                        Так оптимизация по скорости и оптимизация по памяти — совсем не одно и тоже.
                        Я и пишу, что заголовок про скорость, а реальное содержание немножко про другое ;)

                    Only users with full accounts can post comments. Log in, please.