Попробуем разобраться, как можно оптимизировать сложные процедуры 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 процедуры это позволяет Ещё...
Не будем засорять этот раздел разными простыми задачами, нам нужны необычные идеи. Вот, например, такая задача. Она встречается в банках, где стек разработки на SQL достигает 90%. На разработку дается максимум 24 часа (т.е. есть время и другими делами позаниматься). Мне удалось ее решить вечером часа за 3, успев сделать и некоторые домашние дела.
Но это не все, задача не имеет типового решения, всегда есть еще и другие способы. Подумайте над этим. Даю описание.
Задача - написать запрос, выводящий средний остаток из таблицы на заданный период. Для проверки результата вывести средний остаток с 9 янв по 17 янв. Примечание. В банке, в части экономии ресурсов SQL сервера, остатки по счету хранятся не на каждый день, а дифференцировано, когда были проводки. С другой стороны клиенту банка надо знать, какая сумма у него на счете за ЛЮБОЙ день. Исходные данные даны в таблице tblFactAmount ниже.
create table tblFactAmount (fAmount float, dtValueChange date)
;
insert into tblFactAmount (fAmount, dtValueChange)
values
(3, '20210101'),
(5, '20210110'),
(1, '20210114'),
(10,'20210116'),
(2, '20210117')
Вот мое решение. Оформил его в виде SP процедуры. Прежде, чем читать решение, подумайте 5 минут. Это намного интереснее. Вдруг ваше решение окажется лучше ...
Ещё...