Temporary table
First, I'm going to talk about the temporary table method first. This is a widely recommended solution. I've encountered it several times in my project. Here's another article explaining how it works, and an example of how to use Custom Paging in DataGrid:
DataGrid Pagination Part 2 – Customized Pagination
The methods in these two articles are to copy the primary key data into a temporary table and then join the main query to achieve query optimization. Let's take a look at the essence of this method:
Copy the codeThe code is as follows:
CREATE TABLE #Temp (
ID int IDENTITY PRIMARY KEY,
PK /* here goes PK type */
)
INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn
SELECT FROM Table JOIN #Temp temp ON = ORDER BY
WHERE ID > @StartRow AND ID < @EndRow
By copying all rows into a temporary table, we can further optimize the query (SELECT TOP EndRow…), but the key is the worst case - a table containing 1 million records will generate a temporary table with 1 million records. Considering this situation, and looking at the results of the above article, I decided to give up the method in my tests
Previous page12345Next pageRead the full text