Another problem is IDs with (unknown) gaps in between. Here, finding the cursor value becomes even more difficult or even impossible (this is precisely the reason why the database cannot do an index seek, but has to count lines by scanning). A small addition from me, however: this technique is wonderful for updating or deleting in large tables. You can update/delete rows in blocks by working with TOP and WHERE ID < x in a loop.
Thank you very much. It's nice to know that I've done everything right, even without having seen this video beforehand. I am currently working with realtime data, and saving the last ID helps to keep the next query fast. However, I find your example with the fixed value 200400 somewhat misleading. It's completely ok for benchmarks, but in real life I don't even know which ID I have as a starting point at the beginning. The video would have benefited from providing a practical approach to this. I personally know what to do, but @emma-vi's question shows the need for clarification.
Of course, of course... I needed something stable for the benchmark. In a real world example, we'd fetch the first page, and then use the ID of the last record as the cursor.
Hi there, great videos btw. I am just wondering, how do you know that the cursor = 200400 corresponds to the page that you are trying to access? thanks
@@MilanJovanovicTech but I mean how can apply this on a table where I don’t know which number means any page? For example I have a table where can have logical deleted rows so the number itself can’t be associated to a calculation of a page
@@osman3404 but if you add an order or a filter that doesn't work anymore right? because the cursor uses a number as an anchor to calculate the next rows
he just doesn't know it and can't provide you the answer. In fact, identifying where the cursor is for correct page is very tricky and sometime impossible if you have id as guid or non sequential id. Normally the only situation I can see we could leverage cursor pagination is infinite scroll, where we would know next exact cursor position. For normal paging that allow user to arbitrarily go to any page, cursor won't work! And the way he presented his example is confusing enough for your question to arise :D
Nice! Thank you, perhaps a naive question but you picked an id to use as your cursor. How would you know what to use as your cursor in a real life scenario? Maybe I misunderstood something.
In theory, you can add a map of ids corresponding to pages. You then will need to periodically update it and whenever querying a page, query an offset (number of records added since last map update) to add this offset to the record id from the map. This will add ability to go to arbitrary page (if you use sequential numeric ids and sort paged records by that id). May perform better than offset...limit if paged table contains many more records.
@@MilanJovanovicTech it sure would be. Not so much if you don't need to maintain maps for different users (all users see the same list of records and therefore have the same pages) but still quite messy. And I completely forgot about the removal of records being a thing... It could be accounted for relatively easily as part of the periodical map updates, but between updates some pages would intersect. OR there could be a list of records removed since last map update which can be used to calculate proper offsets when needed... But anyway, it is indeed a very complicated and messy solution that wouldn't make sense for probably anyone
Is there a concept to keep page id's in cache to use them as cursor boundries to speed up the queries? I would never hard code an Id like this, but I can imaging to have these id's collected for instance every day to cache them.
How practical is cursor pagination in a real-life scenario given: 1. You almost always want to allow the data to be sorted by user-defined criteria - order date, order value, etc. 2. Once real-life scenarios such as order deletion/cancellation/reversals start to occur the id becomes very brittle, especially when you add a where clause to the select e.g. you want to page all non-cancelled, non-reversed orders
It's not for general purpose pagination, but fits perfectly for use cases where you need an infinite-scroll solution. Examples could be social media timelines, e-commerce catalogs, e-mail, etc.
Not really, you need something you can sort on that also matches when the records were created. Otherwise, you might get a different result each time as more records are added/removed from the DB.
cursor pagination is faster, but you cannot order the data by some column except id, so if user want to order data by some column better using cursor, but if he want sort data then better is offset pagination
But why on earth would anybody want to have a pagination that filters by ID? A pagination means: Where(any condition) & OrderBy(any column) & any page & any size
Think about your Gmail inbox. You see the latest 50 emails, and can navigate to the next page, etc. The emails are sorted in the order that they arrive to your inbox - i.e. creation order. Which is exactly what an integer PK gives you - creation order.
@@MilanJovanovicTech At which moment do you decide to create/update the cursors in order to fetch the latest 50 emails? on each email arrival do you delete the cursor for said user, recalculate which Id should the cursor have to be the 51st element, then offset all the records from that point (by deleting and recreating them with Id+1) to make room for the cursor?. yes, you could potentially use a step of 2 or bigger so you always have room for creating cursors, but there is also the point of this cursor being hardcoded in the code. Seems very unwieldy at the time of updating the cursors.
@@matiasmiraballes9240you don't need to update a cursor each time. All you need to do is order emails by id descending, and create a cursor pagination backwards: where Id < x
I would not call this cursor pagination - it's just selecting by clustered index. Cursor pagination is technique that is actually using database feature CURSOR, by defining query (where you can actually use ORDER BY with other expressions than just Id) and then FETCH pages.
Wont' work in that case, you need something that's sortable + grows in "creation order" If you have to use a Guid, you'd need another column to handle the creation order part. A good solution could be a CreatedOn column
@@MilanJovanovicTech rewatch your video at 4:30. It’s easy to overlook. You wrote countasync after tolistasync and said it takes 2 trips. You accidentally called countasync on the query instead of before paging. If you are indeed doing what you are coding one round trip was enough but I’m guessing you didn’t mean to call count on the paginated results. For real pagination you should use countasync on the entire filtered query set then call tolistasync with the pagination.
Interesting that the final version will not work with the UI shown in the thumbnail 😉 If i was in a situation where a primitive unsortable next page implementation was needed, my data would not be in a sql db anyway.
It "works" in theory - you can sort a Guid, right? But it's practically useless, because a Guid is random. You want something that is increasing with creation time, like a numeric PK does.
@@MilanJovanovicTech , Thanks Milan. Wouldn't it be more readable if the "Last()" used instead of [^1]? But I appreciate the approach shown, from my understanding it'll do the job working with arrays.
