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

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