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

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

UPD. Вместо табличных переменных @result исопльзуйте временные таблицы #ids (id int) с primary key. Для больших таблиц это будет работать гораздо быстрее. 

CREATE PROCEDURE [dbo].[crud_example_getItems]
	@filters CRUDFilterParameter READONLY,
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
         -- объявляем таблицу
	 IF (OBJECT_ID('tempdb..#ids') IS NOT NULL) DROP TABLE #ids
	CREATE TABLE #ids ( id int primary key)

	-- filters...
	declare @filterName nvarchar(128) = (select top 1 Value from @filters where [Key] = 'name')
	declare @itemID int = (select top 1 try_cast(Value as int) from @filters where [Key] = 'itemID')
	-- извлекаем нужные id на основе фильтров
	insert into #ids
	select id
	from as_menu
	where (isnull(@filterName, '')='' or name like '%'+@filterName+'%')
	
	-- SELECT 1 - выдаем нужные данные на основе таблицы с id in #ids
	select *
	
	from as_menu
	where id in (select id from #ids)
	order by
		case when @sort = 'name' and @direction = 'down' then name end desc,
		case when @sort = 'name' and @direction = 'up' then name end asc
	OFFSET @PageSize * (@Page - 1) ROWS
	FETCH NEXT @PageSize ROWS ONLY;
	
	-- SELECT 2
	select count(*) from #ids	

	-- SELECT 3 Settings
	-- SELECT 4 Footer data or kanban/gantt data
	-- SELECT 5 Predefined filters
    drop table #ids
END