Программирование на SQL | Оптимизация sp-процедур SQL Server

В этом разделе сайта попытаемся разобраться с программированием баз данных. Язык называется SQL. В этом языке нет или почти нет классов, переменных, массивов и циклов. Сравнивать его с языками высокого уровня не имеет смысла. Но без него Вы не сможете написать интерфейс для настольных приложений Windows или интернет сайтов. Для Разработчика надо обязательно надо знать SQL, библиотеки обработки данных на C#, Microsoft Visual Studio, Microsoft SQL Server Management Studio или dbForge Studio for SQL Server

Оптимизация sp-процедур SQL Server

Попробуем разобраться, как можно оптимизировать сложные процедуры 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) должно улучшить ситуацию.
  • Продолжение следует ...

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

Loading