Попробуем разобраться, как можно оптимизировать сложные процедуры SQL Server. История возникновения таких процедур, как всегда безобидна и начинается с создания таблиц, потом простых SELECT запросов и т.д. Все это нормально работает. Но проходит немного времени, но иногда месяцев, кто-то начинает их модернизировать и улучшать, добавляя новый функционал, множество параметров. Получается много строк кода 400-500 и более, все это начинает медленно работает и раздражать пользователей. И так пришло время, и вы начинаете все это анализировать и пытаетесь понять, почему все это так медленно работает.
Анализ таблиц
С чего начать анализ процедуры. Конечно, с полей таблиц, с чего ранее и началась разработка SP процедуры. Для этого находим в процедуре таблицы и открываем их для анализа в Microsoft SQL Server Management Studio (MSSMS). Обращаем внимание на следующее
- Есть поле Country в таблице Customers, но у него нет значения, а в самой процедуре стоит проверка типа ISNULL(Country,"")=="". Логики в этом коде никакой. Это нужно тогда, когда выводится группа данных, а NULL поле мешает связывать текстовые поля. Решение. Задаем значение по умолчанию пробел или "-", а потом удаляем проверку на NULL из процедуры. Скорость увеличится, причем значительно. P.S. Мне приходилось встречать десятки таких проверок на 1 процедуру. Конечно, в этом случае надо посмотреть на интерфейс, как он отреагирует на эти изменения.
- Есть поле Country, но кто-то сделал заполнение поля через скалярную функции (которая включает в себя SQL запрос), т.е. Country вычисляется, а не является полем хранения данных. Другой программист сделал запрос SELECT TOP 100 * FROM Customers и добавил его в SP процедуру. На экране MSSMS он получит свой результат, но то, что SQL Server сделает еще 100 запросов он не узнает. Решение. 1 вариант. Лучше не использовать в полях таблиц скалярные функции с SELECT запросами. С другой стороны, если этого не избежать в нашем случае, то можно объединить через JOIN таблицы клиентов и стран. 2 вариант, просто указать вместо *, нужные поля без Country, если логика SP процедуры это позволяет
- Есть поле Country, где оно определено как текстовое с длиной 120 символов. В этом случае, кажется уже ничего нельзя сделать. Но если подумать, то размерность поля надо увеличить до 128 символов. Бред какой-то, растет база данных. Объяснение. Минимальным объектом хранения данных в базе является байт (8 бит) и желательно текстовые поля округлять до 8 знаков в большую сторону. Об этом я узнал, изучая Microsoft Access 2003, были проведены тесты на эту тему. На сайте, в разделе программированию в Microsoft Access должна быть такая информация.
- Продолжаем думать дальше. Зачем нам текстовое поле Country в таблице Customers. У нас же Реляционная база данных. Решение. Превращаем Country в целое CountryID, а все страны выводим в отдельную таблицу, где задаем уникальный индекс по CountryID. Такое решение увеличит скорость работы и уменьшит размер базы данных.
- Изображения, что делать с ними. Вот статья как избежать хранения в базе данных BLOB изображений. Она дает решение на C#, об автоматической замене таких данных на ссылку на графический файл https://leadersoft.ru/web/post/Plagin-Sokhranenie-kartinok-bloga-na-diske
- Продолжение следует ...
Анализ текста процедуры
- Смотрим начало процедуры, в ней описаны параметры, которые потом будут использованы для фильтрации данных. Если параметров много, то надо задуматься, а нужны ли они здесь. Наша процедура нужна лишь для вывода финансовой информаци�� в Excel. Решение. Excel имеет отличный фильтр на листе. В заголовке полей, есть списки, открывая их мы можем отфильтровать и сортировать данные. Таким образом, все параметры SP закомментируем или обнуляем (=NULL), а часть работы с параметрами передадим пользователю. Пусть он сам в Excel их и назначает. В результате получим 2 (два) параметра по начальной и конечной дате. Результат. Это увеличит скорость прилично, а метод запомним, он нам пригодится для создания временных таблиц
- SELECT запросы могут выполняться достаточно медленно. Это называется блокировкой. Решение. SELECT * FROM Customers with(nolock) Ключевое слово with(nolock) должно улучшить ситуацию.
- Продолжение следует ...