Оптимизация запроса 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). 

Количество чтений чуть снизилось. Заметно ниже нагрузка на процессор и время обработки также сильно уменьшилось. 

Falcon Space - функциональная веб-платформа разработки на узком стеке MS SQL/Bootstrap. Вводная по Falcon Space
Насколько полезной была статья?

Google поиск по нашей документации

Falcon Space

Это снижение стоимости владения

за счет меньшего количества людей для поддержки и узкого стека разработки. Про снижение стоимости владения продуктом

Это быстрое внесение изменений

по ходу эксплуатации программы. Как создается функционал на платформе

Это простой удобный интерфейс

адаптация под мобильные устройства. Про юзабилити платформы

Нужна бесплатная консультация?
Получить оценку проекта
Создайте концепцию проекта на основе нашего шаблона и получите оценку проекта в виде КП.
Демо-сайт решений
Базисные решения, которые можно гибко адаптировать под себя: менять внешний вид, бизнес-логику и даже структуру базы данных.
Сайт использует Cookie. Правила конфиденциальности OK