Повышение производительности

Заратустра

Спецвыпуск: Хакер, номер #052, стр. 052-050-3

Основное заблуждение при разработке баз данных: для повышения производительности достаточно проиндексировать все возможные столбцы. Эти действия не только бесполезны, но и чреваты снижением производительности в несколько раз. Суть проблемы выбора индексов в том, что SQL-сервер должен изменять их при любых "реформах" в таблицах (INSERT, UPDATE, DELETE). Если индексов один или два, больших потерь производительности не будет. Но если их намного больше, SQL-сервер оказывается перегруженным работой с таблицами.

Другая проблема: после оптимизации запроса базой данных индекс может может стать неиспользуемым. Иногда SQL-серверу потребуется меньше времени на перебор таблицы, чем на использование индекса. Такие индексы служат балластом и, по-хорошему, должны быть удалены. Их можно отловить анализатором SQL-запросов, который даст полную картину обработки запросов.

В идеале весь код Transact-SQL, используемый в приложениях, должен находиться в хранимых процедурах, а не запускаться в виде динамического SQL или скриптов. Это уменьшает сетевой трафик (передается только CALL или EXECUTE) и ускоряет выполнение самого кода Transact-SQL, так как код в хранимых процедурах является прекомпилированным.

Но и тут тебя ждут подводные камни. Когда хранимая процедура выполняется в первый раз (и у нее не определена опция WITH RECOMPILE), она оптимизируется, для нее создается план выполнения запроса, который кешируется SQL-сервером. Если та же самая хранимая процедура вызывается снова, она будет использовать кешированный план выполнения запроса, что сэкономит время и увеличит производительность. Но если запрос динамический (изменяется от одного выполнения хранимой процедуры к другому), оптимизации не будет, а производительность только пострадает. Если известно, что запрос будет меняться каждый раз при выполнении хранимой процедуры, нужно добавить опцию WITH RECOMPILE, которая заставит перекомпилировать хранимую процедуру заново и оптимизировать запрос.

Другая неприятная особенность, с которой можно столкнуться, - блокировки (deadLock). Это тот случай, когда два процесса пытаются заблокировать два объекта, причем каждый процесс пытается заблокировать объект, который принадлежит другому процессу. В этом случае SQL-сервер прерывает один из процессов, откатывает его транзакцию, тем самым позволяя второму процессу продолжить работу. Этого можно избежать, если получать доступ к объектам в одном и том же порядке и не допускать пользовательского ввода во время транзакций. То есть получить все необходимые данные до начала транзакции.

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

- Чем меньше код триггера, тем лучше и тем быстрее выполняются операции.

- Не используй триггеры для задач, которые могут быть реализованы другими, более эффективными способами. Например, для проверки значений лучше использовать CHECK, а не триггер.