Category: Query Tuning

Aaron Bertrand continues digging into SQL Server pagination performance:

In my previous tip, Pagination Performance in SQL Server, I showed how to make SQL pagination more predictable – turning O(n) into O(1). I materialized and cached row numbers to page through instead of calculating them on every request. It wasn’t the whole story, though; real pagination queries rarely get to sort without filtering. Users always want more control, and filtering can threaten that predictability.

Read on for examples of how to handle a few different scenarios.

Aaron Bertrand takes life one page at a time:

Many web applications and APIs implement pagination to control how much data is returned or displayed. Many paging solutions suffer from the linear scaling problem (often referred to as O(n)), meaning the amount of work increases as you get into higher page numbers. If a user has ever clicked “next page” or “last page” and your CPUs caught on fire, you may have been a victim of linear scaling. Are there any creative solutions that will achieve constant-time performance (O(1))?

Aaron’s answer is interesting, particularly if you’re able to define the valid set of filters. At a prior job, I was responsible for filtering of arbitrary combinations of 30+ different columns across multiple dimensions and a fact table in a warehouse. That was a royal pain. The best we could do was run the query once, using ROW_NUMBER() to capture the sort order, and then store that ordering in a specialized table with an identifier token that was a hash of the incoming session info, and cache that data for a pre-set amount of time—which, if I remember correctly, was 5 minutes. Somewhat similar to what Aaron shows but much more ephemeral and it caused the first load to be consistently slower while making subsequent paging activities much faster.

Kendra Little wants to know what’s happening right now with this query:

I frequently need to see rowcounts and execution time for queries while they’re running. Maybe I’m troubleshooting a slow query that’s still executing, or I want to understand which operators are causing the slowdown before the query completes.

Last week at the PASS Summit I learned some little nuances about how this works that I’d missed.

Click through to learn what Kendra learned (and now what I learned).