A Little About Paging Queries And Batch Mode In SQL Server

Become a member! / @erikdarlingdata
Click here for 50% off a health check: training.erikd...
If you like what you see here, you'll love my advanced performance tuning training:
training.erikd...
Paul White articles:
1. qa.sqlserverce...
2. qa.sqlserverce...

[@douglascoats7081]

Erik "Sweet Meteor of Death" Darling

[@ErikDarlingData]

Someday, someday.

[@andreysamykin1143]

Interesting! Thanks a lot!

[@ErikDarlingData]

Glad you liked it! 🥸

[@ahmadjuran5988]

Thanks Erik 😀

[@ErikDarlingData]

🫡

[@clerincg]

It's unfortunate that a method that was documented 15 years ago is still a preferred method when I think that using the OFFSET ... FETCH structure is more readable/understandable (but doesn't use BATCH mode). I'll look forward to a future video from Erik Darling that demonstrates that it works now 😁

[@ErikDarlingData]

You may be waiting an uncomfortable amount of time for that follow up.

[@jackiecooper9439]

Fetching data with a large offset can significantly slow down performance, as the query needs to process and skip the preceding rows before returning the desired result.

[@gotqn2103]

Thanks 👍

[@ErikDarlingData]

For you? Anything.

[@FlaggedStar]

02:10 Honestly, I quite liked that opening. It's rare to see a coverage of when to make a columnstore index that is neither ridiculously basic nor extremely advanced. I liked having two minutes spent on how people use columnstore.

[@ErikDarlingData]

Well thanks, yeah, it's difficult to tell someone fully when to use or not use one from afar, but I think a lot of people got scared about it because they didn't get a high column score.

[@philippecloquet812]

Hi Erik, I replayed the same queries on my box and I have a different result, the first query (with fetch) took 10 seconds on the first run (4.2M pages read from disk), 2 seconds the next run. The second query with the row_number function was not faster altough batch mode was used. I double checked the execution plans and they are exactly the same as yours. Am I missing something? PS I use PoorMansTSqlFormatter for my formatting 🙂, and thanks for all these unforgettable parties 🥳🥳🥳

[@ErikDarlingData]

I don't have a quick answer for you here. I'm sure it's possible, but I'm not sure where things differ.

[@douglascoats7081]

fyi you forgot to include the Paul White links in the description

[@ErikDarlingData]

Fixed!

[@oblako77]

It is just a simple case of querying 1 table, so join is trivial. Not much use when you have a complex query with many rows to return so it needs to be paged 😢

[@ErikDarlingData]

I totally disagree. Most people have over complicated the situation in various ways and don’t really understand which table drives the pagination. Even if there’s a core set of joins that does it, you can apply the idea here to those. It’s a problem I’ve solved for many people who made similar crying emojis.

[@oblako77]

That is probably right for a specific query, but in my case, our code had to add paging logic to an arbitrary query created by the user. So customer concatenated tables whatever they wanted, and then the result must be returned in pages.

[@ErikDarlingData]

@@oblako77 yep, it’s still a very solvable problem. You’re not special 😃