Common Table Expressions and pagination

Hello!

Common Table Expressions or Russian generalized table expressions is a technology that appeared in MS SQL Server 2005 and is 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 selection, then calculate the line numbers that belong 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 can 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 always need to be extracted. And to generate a page, you usually need a larger number of columns but a small number of rows. The gain is due to the fact that we use a small and fast non-clustered index to determine the lines of a particular page, and to extract the rows of a single page we use a clustered index with a small number of lines.

Here is an example of what request was used on my forum before optimization:

select * from forummessages where TopicID=310 order by messageid

when it was executed, 7815 logical readings were made.

And here is an example request 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 performance of data extraction for displaying one page has increased by almost 115 times and there is no need to calculate in the program code which lines belong to the desired page.

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

Here is an example linq code for extracting the same 10 lines: and here is what runs on SQL Server (captured using SQL Profile) As a result of this query, we get 4889 logical readings, which is almost 72 times more than in case of using CTE and one and a half times less than retrieving all rows of a sample that is not divided into pages.

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;

}



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