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

SQL-инструмент для создания личных кабинетов на сайте

Суть подхода и история создания Falcon Space

Платформа Falcon Space

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

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

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

по ходу эксплуатации программы

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

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

Веб-приложения на MS SQL. Партнерская программа для разработчиков и веб-студий

Вы можете разрабатывать самостоятельно или сотрудничать с нами в плане веб-разработки на платформе Falcon Space, используя только SQL и HTML.
Смотреть примеры с кодом SQL
Документация по платформе

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