Common Table Expressions and dividing tables into pages

Hello!

Common Table Expressions or Russian generic table expressions is a technology that appeared in MS SQL Server 2005 and represents one of the forms of reusing the results of one SQL query in another.

In web development, quite often it is necessary to make a selection, divide it into pages and display the rows of the table that belong to one of the pages. One of the common approaches is to extract the entire sample, then calculate the line numbers that relate to the page we need and generate the output html with the necessary lines. The remaining data is not used and retrieved in vain.
')
CTE allows you to increase the efficiency of such data extraction. The bottom line is that usually to determine which lines are required to display a particular page, we need a key field and a field by which sorting is performed, which, however, does not even always need to be extracted. And to generate a page you usually need more columns but a small number of rows. The winnings are due to the fact that we use a small and fast nonclustered index to determine the rows of a particular page, and a clustered index but with a small number of rows to retrieve the rows of one page.

Here is an example of what query was used in my forum before optimization:

select * from forummessages where TopicID=310 order by messageid

during its execution, 7815 logical readings were produced.

Here is an example of a query using CTE

declare @pagenumber int, @pagesize int
set @pagesize=20
set @pagenumber=10
;with rowpaging
as
(select ROW_NUMBER() over(order by messageid) as rn,messageid from forummessages where TopicID=310)
select * from ForumMessages as m JOIN rowpaging as r ON m.MessageID=r.MessageID
where rn between @pagesize*(@pagenumber-1)+1 and @pagesize*@pagenumber
order by m.messageid

during its execution, 68 logical readings were made.

As a result, the data extraction performance for displaying one page has increased almost 115 times and at the same time it is not necessary in the program code to calculate which lines belong to the necessary page.

UPD:
Let's take a look at how a LINQ query works using the Skip () and Take () methods.

Here is an example of linq code to extract the same 10 lines:

DBM dbm = new DBM();
var items = (from m in dbm.Context.ForumMessages
where m.TopicID == 310
orderby m.MessageID
select m).Skip(200).Take(10);
string r = "";
foreach (var x in items)
{
r += x.Body;

}

and this is what is done on SQL Server (captured using the SQL Profile)

SELECT TOP (10)
[Filter1].[MessageID] AS [MessageID],
[Filter1].[TopicID] AS [TopicID],
[Filter1].[UserID] AS [UserID],
[Filter1].[Body] AS [Body],
[Filter1].[CreationDate] AS [CreationDate],
[Filter1].[Visible] AS [Visible],
[Filter1].[IPAddress] AS [IPAddress],
[Filter1].[Rating] AS [Rating],
[Filter1].[Deleted] AS [Deleted],
[Filter1].[WhoDelete] AS [WhoDelete]
FROM ( SELECT [Extent1].[MessageID] AS [MessageID], [Extent1].[TopicID] AS [TopicID], [Extent1].[UserID] AS [UserID], [Extent1].[Body] AS [Body], [Extent1].[CreationDate] AS [CreationDate], [Extent1].[Visible] AS [Visible], [Extent1].[IPAddress] AS [IPAddress], [Extent1].[Rating] AS [Rating], [Extent1].[Deleted] AS [Deleted], [Extent1].[WhoDelete] AS [WhoDelete], row_number() OVER (ORDER BY [Extent1].[MessageID] ASC) AS [row_number]
FROM [dbo].[ForumMessages] AS [Extent1]
WHERE 310 = [Extent1].[TopicID]
) AS [Filter1]
WHERE [Filter1].[row_number] > 200
ORDER BY [Filter1].[MessageID] ASC

As a result of the execution of this query, we get 4889 logical reads, which is almost 72 times more than in the case of using the CTE and one and a half times less than extracting all the rows of the sample, not divided into pages.