Оптимизация запроса SQL - извлечение данных для таблицы
В статье рассмотрим как можно оптимизировать запрос на извлечение данных из таблицы с учетом пагинации и фильтров.
Раньше мы извлекали данные примерно таким образом:
declare @sort nvarchar(128) = '', @direction nvarchar(128)= '', @page int = 1, @pageSize int = 50
declare @filterName nvarchar(128)
select @filterName = ''
declare @filterDate nvarchar(128),@createdFrom date, @createdTo date
select @createdFrom = '2021-01-01'
select @createdTo =''
-- Готовим выходную таблицу
declare @result TABLE (id int, [date] nvarchar(256), username nvarchar(64),
name nvarchar(512), sub_name nvarchar(max), ordCreated datetime)
insert into @result
select id as id,
convert(nvarchar, isnull(created,'1900-01-01'),104) + ' ' +LEFT(convert(nvarchar, isnull(created,'1900-01-01'),108),9) as [date],
isnull(username,'') as username,
isnull(header,'') as name,
[text] as sub_name,
created as ordCreated
from as_trace
where code = 'exception'
and (isnull(@filterName,'') = '' or header like '%'+@filterName+'%'
or [text] like '%'+@filterName+'%')
and (isnull(@createdFrom, '') = '' or
isnull(@createdTo, '') = ''
or try_cast(created as date) between @createdFrom and @createdTo)
order by created desc
-- передаем нужные данные по пагинации из выходной таблицы
select * from @result
order by
case when @sort = 'username' and @direction = 'down' then username end desc,
case when @sort = 'username' and @direction = 'up' then username end asc,
case when @sort = 'date' and @direction = 'down' then ordCreated end desc,
case when @sort = 'date' and @direction = 'up' then ordCreated end asc
OFFSET @PageSize * (@Page - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;
-- вычисляем общее количество для построения пагинации
select count(*) from @result
Особенность данного решения - мы извлекаем ВСЕ данные в табличную переменную @result без учета пагинации. Всего строк может быть 1000, а извлекать нам надо 10. Но при этом для всех 1000 строк выполняются вычисления в SELECT (особенно если там много подзапросов).
Решение: в табличную переменную мы записываем только id сущностей, это позволит посчитать нам количество записей, а в выходном select мы извлечем нужные записи с id из этого списка. При этом мы избегаем возможного дублирования логики фильтров.
Оптимизированный вариант:
declare @sort nvarchar(128) = '', @direction nvarchar(128)= '', @page int = 1, @pageSize int = 50
declare @filterName nvarchar(128)
select @filterName = ''
declare @filterDate nvarchar(128),@createdFrom date, @createdTo date
select @createdFrom = '2021-01-01'
select @createdTo =''
-- таблица для хранения id отобранных сущностей.
declare @ids table(id int)
insert into @ids
select id
from as_trace
where code = 'exception'
and (isnull(@filterName,'') = '' or header like '%'+@filterName+'%'
or [text] like '%'+@filterName+'%')
and (isnull(@createdFrom, '') = '' or
isnull(@createdTo, '') = ''
or try_cast(created as date) between @createdFrom and @createdTo)
order by created desc
select id, convert(nvarchar, isnull(created,'1900-01-01'),104) + ' '
+LEFT(convert(nvarchar, isnull(created,'1900-01-01'),108),9) as [date],
isnull(username,'') as username,
isnull(header,'') as name,
[text] as sub_name,
created as ordCreated
from as_trace where id in (select id from @ids)
order by
case when @sort = 'username' and @direction = 'down' then username end desc,
case when @sort = 'username' and @direction = 'up' then username end asc,
case when @sort = 'date' and @direction = 'down' then created end desc,
case when @sort = 'date' and @direction = 'up' then created end asc
OFFSET @PageSize * (@Page - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;
select count(*) from @ids
В этом случае все возможные подзапросы в SELECT будут выполняться только для отобранных строк.
Результаты сравнения 2 запросов (тяжелый запрос с большим количеством чтений и большим количеством подзапросов в выходном SELECT).
Количество чтений чуть снизилось. Заметно ниже нагрузка на процессор и время обработки также сильно уменьшилось.
Google поиск по нашей документации
- Руководства
- Основа Falcon Space
- Основные компоненты
- Возможности
- Коммуникация с пользователем
- Дизайн, стилизация
- Лендинги
- Универсальный API
- Примеры интеграций
- Каталоги
- Навигация
- Документы
- Дополнительные компоненты
- Продвижение, SEO
- Системные моменты
- Системное администрирование
- HOWTO
- Таблицы Таблицы. Как сделать сортировку в AS CRUD Таблицы. Как сделать Editable для галочки (Да/Нет) в AS CRUD Таблицы. Как редактировать колонку с датой Таблицы. Как связать 2 таблицы Таблицы. Как сделать таблицу в модальном окне (открывается в диалоговом окне) Таблицы. Как убрать показ количества строк результата в заголовке таблицы? Таблицы. Как связать таблицу и редактирование сущности (зависимая страница) Таблицы. Как использовать комментарии в таблицах Таблицы. Как добавить в фильтре значение Не выбрано со значением Таблицы. Как установить ширину колонки в таблице Таблицы. Как добавить диапазон даты или чисел (слайдер) в фильтр Таблицы. Как сделать операции только для некоторых строк Таблицы. Как делать различный набор столбцов одной таблицы для разных ролей Таблицы. Как реализовать подтаблицу (вложенная таблица), подформу в таблице Таблицы. Как скрыть строчные операции в таблице для определенных строк Таблицы. Как добавить коллбек после загрузки таблицы Таблицы. Как сделать таблицу на смартфоне в виде карточек Таблицы. Как работать с галочками в таблице Таблицы. Частые ошибки при настройке таблицы (почему не работает таблица) Таблицы. Как обновить подтаблицу после выполнения некой операции Таблицы. Как обрабатывать групповые операции через модальную форму Таблицы. Как создать предустановленные фильтры для таблицы Как сделать сворачивание таблицы (collapse table) Таблицы. Загрузка таблицы по ссылке Как убрать старые dict процедуры в формах и таблицах Таблица. Как сделать фильтр с деревом галочек Таблицы. Как отключить сохранение состояния таблицы (фильтры) Таблицы. Как сделать ссылку на всю строку таблицы Таблицы. Как передать через URL значение фильтра Таблицы. Как сделать обрезание ячеек таблицы Сортировка строк в таблице Таблица. Создание сущности с учетом значений фильтров Оптимизация запроса SQL - извлечение данных для таблицы Кастомная разметка в таблице Таблицы. Как настроить дополнительные шапку и подвал у таблицы Таблица. Режим кастом вывода через JS (custom) Таблица. Как сделать зависимые фильтры в таблице Импорт данных в формате файлов txt, csv через таблицу Как гибко управлять видимостью столбцов таблицы Таблица. Как сделать раскрытие подстроки через любую ссылку Таблица. Режим быстрой фильтрации строк без обращения на сервер
- Формы
- Загрузка файлов, картинок
- Работа с SQL
- HOWTO JS
- HOWTO Верстка
- Решение проблем
Falcon Space
Это снижение стоимости владения
за счет меньшего количества людей для поддержки и узкого стека разработки. Про снижение стоимости владения продуктом
Это быстрое внесение изменений
по ходу эксплуатации программы. Как создается функционал на платформе
Это простой удобный интерфейс
адаптация под мобильные устройства. Про юзабилити платформы