I currently have this query (SQL Server):

DECLARE @PageNum int = 1;
DECLARE @PageSize int = 2;
SELECT *
FROM (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY Color ORDER BY Name) AS Row
  FROM Orders
) t1
WHERE Row BETWEEN ((@PageNum-1)*@PageSize 1) AND (@PageNum*@PageSize)
ORDER BY Color,Name

What this query does is it selects "pages" with 2 records for every Color. Meaning if the table contains:

Then the above query would result in:

At page 1 and

At page 2.

This works. However, in my real-world example, this query takes a very long time to execute--about 10 minutes in fact. I have a static table with over 800K records which never changes. Since this table never changes, I created an index for every column I would filter/sort by. This table also has a column RowId, which is practically just the row number, and it has a unique index.

The execution plan can be seen here: https://www.brentozar.com/pastetheplan/?id=BkeWfVCSY

What can I do to speed up this query?

CodePudding user response：

The first operation in your execution plan is a table scan, so I assume this is a heap table (otherwise we should see a clustered index scan) and the indexes you created are not being used.

Also, once this is a static table that never changes, according to the description, fragmentation should not be a problem.

I don't have SQL Server installed in this machine so I can't compare the execution plans before and after, but I believe that for your query, we may see some improvements with a clustered index, ordering the keys as Martin Smith suggests in his comment.

CREATE CLUSTERED INDEX IX_Order ON dbo.Order(OrderType ASC, From DESC)

Please let me know if this improves your query. If not, I can create a similar scenario to test during the day.