Keyset vs offset pagination in EF Core: 8ms vs 57ms

[00:00:00]Your pagination is slow and as your data set grows, it's only going to get worse.

[00:00:07]Let's fix it with key set pagination and stay tuned to the end of the video where we'll show you how much quicker key set pagination can be.

[00:00:17]With key set pagination, you get the last record in the data set and append it to the query string. The advantage with this is that it will take the next records based on the ID. So, it doesn't have to scan all the records before it.

[00:00:34]To write the query, the first thing you need to do is to query the last ID record. This is because you'll need the properties to do the filtering. So, the way that you do that is you await the query. So, we stored it in a separate variable up here which queries the products table. And the reason why we've done that is that we'll need it for later.

[00:00:56]So, we query that based on last ID. And then we select the properties that we need and add them into a DTO.

[00:01:05]So, we create a new product DTO and the properties that we'll need is the ID, the name. We'll also need the price and we'll also need the category name.

[00:01:16]And then we only want the first record.

[00:01:18]So, we call first or default a sync and pass in the cancellation token. Now, you could add this into the query string and that would save you an extra query. So, that's another option.

[00:01:31]Now, we only want to do the pagination filter if the anchor can be found. If it can't be found, we'll throw an exception.

[00:01:41]If it can be found, we can then apply the key set filter.

[00:01:45]So, we've created a separate method for that. And we're going to pass in the query, the sort by, the sort direction, and the anchor.

[00:01:54]We'll show you how to set up the apply key set filter next.

[00:01:59]Watching YouTube tutorials are fine, but if you really want to get familiar with Entity Framework Core, you really need to use it in a real-world application, and that's where my minimal API course comes in.

[00:02:14]We use Entity Framework Core throughout to build a real web API from scratch.

[00:02:20]Check out the link in the description for more details. Let's now apply the key set filter.

[00:02:28]If we're ordering the products by product name in ascending order, the way that we filter is we call the query, and we specify where the name in the product, and we compare it to the anchor name. If that is bigger than zero, then we return the record set.

[00:02:45]Now, there is a situation where two product names can be the same or even more. So, we need to combat that. If the product name is equal to the anchor name, and the ID is bigger than the anchor ID.

[00:03:00]Now, to do it in descending order, it's very similar, so we can copy and paste that, and we change it from greater than to less than for each of these queries.

[00:03:11]Now, if you have multiple sort by options, you'll need to set one up for ascending and descending. So, we've done it here for the price, and it's very similar, but because the price is a decimal, you can just use the greater than and less than sign.

[00:03:27]We've also set one up for the category name, but if you've got multiple sort by options, then this is going to get very big.

[00:03:36]The final part is to apply the ordering to the query.

[00:03:40]We need to set up an order by based on each of the sort by options. So, for the example with the product in ascending order, we order by the product name, and then we've also got that secondary ordering just in case the product name is the same in two records. So, if that's the case, we then order by the product ID.

[00:04:01]We've also done it for each of the sort by options in ascending and descending order.

[00:04:08]The next step is to run the query and see what SQL it outputs. And if you're getting value from this content, hit the subscribe button as we're going to be doing many more videos on Entity Framework.

[00:04:22]And this is an example of the SQL that it outputs.

[00:04:27]So, all the pagination filtering happens in the where clause. So, we're saying where the name is bigger than the anchor name or the name is equal to the anchor name and the ID is bigger than the anchor ID just in case the name is the same on more than one record. Bear this in mind because next we're going to show you offset pagination and it outputs the SQL differently.

[00:04:51]Offset pagination allows you to skip a number of records and then get a paginated result set from that.

[00:04:59]Now, this can cause performance issues, particularly if you've got a large data set. The reason being is that it needs to read all the records before getting your paginated result set.

[00:05:13]But, it's a lot more simple to write.

[00:05:16]So, with the key set filtering, if you remember, we had to get the anchor and then we had to apply the filtering based on that anchor record.

[00:05:24]But, with offset pagination, we don't have to do that. We just get the products and we can still call the apply order that we used with the key set pagination, but all we need to do additionally is just to skip the number of records. We then take the records that we want, select the products, and then return it as part of the response.

[00:05:46]And this is an example of the sequel that it outputs. Now, the key differences is that there's no where clause. Instead, there's this offset line where we offset a number of rows, and then we fetch the next rows depending on the size of our pagination.

[00:06:02]And it's that line that slows down the performance with offset pagination.

[00:06:08]Shortly, we'll show you a speed comparison between keyset and offset pagination. But, to get the full benefits of keyset pagination, we must add indexes.

[00:06:19]The products data set has 3 million records, and we haven't added any indexes to it.

[00:06:26]Now, we're going to get the keyset pagination and return 500 results. When we do that, it takes over a second to run.

[00:06:34]And it's a similar story with the offset pagination. When we run that, it also takes over a second.

[00:06:41]Now, this is running on my local machine where the database and the application are on the same machine. You imagine if your database is located elsewhere, you'd also have to add latency time to that response time.

[00:06:57]We'll want to add an index for each of the columns that we're filtering at the same time.

[00:07:02]So, we're filtering on the ID and the name. We're also filtering on the price and the ID. So, we want to create separate indexes for each of these.

[00:07:13]So, in this migration, we've created indexes for the product name and also for the price, including the ID in each of them.

[00:07:21]Now, because we're also filtering by category name, we can't do that on the products, but we can create a separate index for the category name.

[00:07:31]The moment of truth. The indexes have been applied to the database. We're going to do it from page 100 with a page size of 500. So, with the offset pagination, it's gone down to 57 milliseconds. We do it a few more times.

[00:07:46]It's around the 50 to 60 milliseconds mark. Let's compare it with the keyset pagination and see if it's any quicker.

[00:07:54]So, we've worked out the last ID for it, and it's 9 milliseconds. So much quicker. 8 milliseconds. Still one more time. 8 milliseconds. So, that's how much quicker keyset pagination can be.

[00:08:09]But, to get the last ID in the keyset pagination, we actually have to go to the offset pagination and run page 99 and get the last record from it, so we could perform that query.

[00:08:22]And that's one of the disadvantages if you're showing page numbers. You can't really do that unless you've got the last ID. So, you need a way of storing that somehow.

[00:08:34]But, if you don't need that and you have a large data set, then keyset pagination might be the way to go. Just as long as your sort by logic isn't too complex.

[00:08:46]However, that might not be the reason why your queries are slow. In this video, I look at five different reasons and how you can fix slow queries. So, I'll see you over there.