I have a query that is using
OFFSET X ROWS FETCH NEXT 50 ROWS ONLY. If X is 160745 it uses the index, if X is 160746 it does not. There are more than 400000 records. With the index, it takes about 2 seconds to run, without the index it takes ~30 seconds.
We are using Azure S0
I know I can use query hints, however is there another option? Is there something in Azure that I can set to improve this? Any ideas would be appreciated.
**Note: assume all columns are no bigger than
nvarchar(255) and only 6 columns.
Below is the query:
SELECT * FROM [TableName] ORDER BY [ColumnName] ASC OFFSET 456450 ROWS FETCH NEXT 50 ROWS ONLY
It’s hard to say without seeing query plans, the table structure, or the index definitions on the table. You should make sure that the index is covering the query. Other than that, you can encourage SQL Server to use the index even without an explicit hint to use the index. For example, if you are confident that the query should always use the index you could try setting a small rowgoal with a hint:
DECLARE @row_goal INT = 456450 SELECT * FROM [TableName] ORDER BY [ColumnName] ASC OFFSET @row_goal ROWS FETCH NEXT 50 ROWS ONLY OPTION (OPTIMIZE FOR (@row_goal = 1));
If you want to avoid hints entirely I recommend rewriting the query. Using
OFFSET with large values requires special care. It’s easy to end up with a plan that scans lots of rows and does a lot of unnecessary work just to get the next 50.
If your application pages through the data one page at a time you can consider the technique described here. The basic idea is that the application keeps track of the last value that the end user has seen. When you need a new page you can pass down the last value as a filter:
SELECT * FROM [TableName] WHERE [ColumnName] > ? ORDER BY [ColumnName] ASC OFFSET 456450 ROWS FETCH NEXT 50 ROWS ONLY;
If you need to be able to show an arbitrary 50 pages you can use the approach detailed here by Aaron Bertrand. With CTEs you can encourage the query optimizer to scan through a narrow index until it reaches the first row that you want. The query will get slower as you increase the
OFFSET but it could perform much better than what you’re doing now. The basic idea would be like this:
;WITH pg AS ( SELECT [key_column] FROM [TableName] ORDER BY [ColumnName] ASC OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY ) SELECT t.* FROM [TableName] AS t INNER JOIN pg ON t.[key_column] = pg.[key_column] -- or EXISTS ORDER BY [ColumnName] ASC;
Finally, Paul White also writes about another way to efficient page through data here that does not use
OFFSET at all.
With the right indexes I expect that at least one of the techniques described in this answer will work well for you.
We tested by turning up the DTUs from 10 (lowest) to 100 (highest for S0). Query went from 30 seconds to 6 seconds. So it looks like it could just be a matter of paying more money.