SQL query Optimization-extracting data for a table

In this article, we will look at how you can optimize a query for extracting data from a table, taking into account pagination and filters.

We used to extract data in something like this way: 

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 =''



-- Preparing the output table
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

-- passing the necessary pagination data from the output table
	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;

-- calculate the total number to build the pagination
select count(*) from @result

The peculiarity of this solution is that we extract ALL the data into the table variable @result without taking into account pagination. The total number of rows can be 1000, and we need to extract 10. But at the same time, for all 1000 rows, calculations are performed in SELECT (especially if there are many subqueries). 

Solution: in the table variable, we write only the id of the entities, this will allow us to count the number of records, and in the output select, you will extract the necessary records with the id from this list. At the same time, we avoid possible duplication of filter logic. 

Optimized version: 

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

In this case, all possible subqueries in SELECT will be executed only for the selected rows. 

Results of comparing 2 queries (heavy query with a large number of reads and a large number of subqueries in the output SELECT). 

The number of reads decreased slightly. The load on the processes is noticeably lower and the processing time is also greatly reduced. 

Falcon Space is a functional web development platform on a narrow stack MS SQL/Bootstrap. Falcon Space Gettting started
{sp-shortDemostandLinks}

Falcon Space Platform

This is a reduction in the cost of ownership

at the expense of fewer people to support

This is a quick change

while using the program

This is a modern interface

full adaptation for mobile devices

Note