Cursor- vs. Offset-Pagination with Entity Framework in .NET 7 🚀 Performance Benchmark Inside!

hello my dear friends thank you very much for dropping by today it's time to run some benchmarks to be more precise we're gonna have a look at offset page nation and cursor pagination because in one of the last videos I was showing you how to use offset pagination with the Blazer quick grit maybe you want to check out the info card for that and in there someone asked what about cursor pagination isn't that faster well is it really well I thought let's just have a look at that quick explanation offset pagination means that you set an well offset to the data you wanna collect or retrieve from your database meaning if you have one million records for instance in the database table and you want the last 50 then well you can count yourself you would have to set an offset to 1 million minus 50 approximately right and then what Entity framework for instance does it counts and skips all the records all the entries that come before where is when you use cursor pagination then you just set an ID and that would then be the ID 1 million minus 50 for instance and that would be then the last page if your page size is 50 entries I think you get the idea so this is one of the tests we're going to run and now I would say we will have a look at the code but first if you learn something and like this video then guys please hit the like button and subscribe to my channel it does make a difference thank you very much for that and maybe you want to check out my newsletter to get these videos right into your inbox that would mean the world to me so thank you very much and now let's start with the benchmarks really quick this is the setup so what do we have here it's a console application console app one is the name pretty great name right and these are all the files we have so I tested this first I tested this with an in-memory database but the difference says haven't been learned that much actually so offset or cursor pagination pretty much the same results so I thought okay this that there must be something different here so I then switch to a SQL Server database SQL Express and this is really this this makes a big big difference so this is one important information I guess and and then I use energy framework of course with my DB context pretty simple stuff and this little class here uh The Entity with an ID and the name and then in the pagination benchmarks so these are the the functions then and the benchmarks we want to run but first maybe the program CS really simple in essence we just run our benchmarks with benchmark.net this is important to note so you can have a look here at the dependencies you see I first tried this with in memory not the best test actually but then with SQL server and here's benchmark.net and I ran some migrations of course to get the database running and regarding the database see I tested some stuff already we have the my database here you can ignore the time tracker database this is from the dotted Web Academy well maybe you want to have a look there links in the video description but for us interesting now is this entities a data base and database table actually and when we have a look here it's currently empty right and I now want to test this with several amounts of a record so maybe thousand at first and then 10 000 and then we just increase that but now here the the actual Benchmark so what do we want to do the first methods here are uh well that's a use case where the user wants to access the very last page as stated in the intro off the off the data so first with the offset pagination we first get the number of pages and then how many pages or entries should be skipped so this is this little formula here and then again we use NG framework with Skip and take to actually get the the data the records and just as a side note ID is really always one two ten thousand hundred thousand whatever we want to uh test here all right so this should really uh match and work because it would be necessary to make this test actually uh or so that the test makes sense in essence all right now regarding get the last page with cursor pagination would simply mean that again we grab the total items minus the page size then plus one for the ID we want and then we use where to well get the entry with that certain ID or starting with that ID and then we take again we use take then with the page size this is the last page and then we have two other benchmarks other functions here General offset pagination cursor pagination meaning that what if the user wants to skip through all the pages or using a crawler to skip through all the pages something like that and that would then be these benchmarks here all right so in essence it's the for Loop down here it's the while loop where we just increase the pages and get every last bit of our records okay I hope this makes sense now the first test is simply 1000 entries for that as you can see here we can run this thing and then the result is oh there is some stuff there already see then then it's good to run this here truncate and now there are zero entries and now I've got this little script here so we run we start with 1000 it executes we're done here and now regarding the count we have 1000 entries all right and first again I would say we start with get the last page so here let me just comment this out after that make sure if you want to do this by yourself make sure to set this thingy here to release so you really built the release version of the debug version and then you simply run this thing terminal should open this takes a while don't worry I will fast forward with the other test but maybe you want to see this the very first time so here you can see what benchmark.net is actually doing and you are actually sitting here just sitting then here and watching because what benchmark.net is doing is it's not running that one time or two times it's running that stuff several times and then it calculates the mean time that it takes to run the functions also shows you how much memory or how many memory is allocated much memory many many memory right and yeah that's that's really interesting so first function is done now comes the second one and this is only a thousand entries and only getting the last page so you can imagine how long this takes when you're using the other Benchmark functions and after a couple of seconds now we should be done yep there we are and you can already see this is interesting right that get last page with cursor in the mean took us 260 millisecond microseconds and offset 627 so even with only a thousand records cursor pagination is a lot faster well again the reason might be that regarding the offset Entity framework really counts the the entries that come before the actual entries you want to see and this is significantly higher I can already spoiler to you that when you have a million entries for instance but let's let's see that by ourselves so cursor is already on first place we go back to our SQL Server management Studio remove almost remove everything here Jesus and we've got zero and now the next test would be maybe ten thousand execute this one all right execute count perfect and now I'm just skipping this so let's have a look at the result and here's the result for 10 000 records now you see difference is already significant 250 microseconds in average for cursor pagination and four and a half thousands for so we're at milliseconds actually Four milliseconds for another milliseconds for the office in pagination now what's interesting is that uh benchmark.net is telling us and that it seems that the distribution is bimodal so this means this is a hint that there may be two different modes for running this Benchmark meaning that maybe there is some caching or optimization done I'm I have no idea if energy framework is doing something like that under the hood maybe it is but in the end we still have a mean value of 250 microseconds and the difference already is significant so you see that even with only 10 000 entries in your database table cursor pagination makes a lot of sense if you want to access the last page now just for fun let's do the same test again for a hundred thousand records so 100 000 it is and let's run this one more time all right no warning whatsoever maybe it had something to do with garbage collection I am not sure about that but you see here now with a hundred thousand records this is just crazy cursor pagination pretty much the same value 250 microseconds and now almost 40 milliseconds so you could really feel the difference maybe with offset pagination here a hundred and thousand records it's actually not that much for a database table and when the user wants to skip for instance with the web application to the last page then the user would have to wait a bit longer well does the user really feel that I am not sure about that but still you see if your records number grows then of course in average nation is way faster than than offset pagination if you want to skip to the last page now let's do the same tests here or run the same tests with actually skipping through the pages right so I just comment these out and now here I comment these in so offset pagination here you see the code again offset pagination with the for Loop and cursor pagination with the while loop where we just say where and then the ID we want to see and here of the pagination we skip actually the number of Records but first let's start with maybe first run the trunkage table again so we have no entries here and I'd say we just run with a hundred of test 100 all right that's really not much so let's try that we close this and go back here all right that was easy and you see wow offset pagination is faster in that case rank one took less memory and it's half of a millisecond and one point half milliseconds for cursor pagination with only 100 records that's already interesting right because if you really have database tables with not really many records then maybe offset page nation is better if you want to skip through all the pages or this is a typical use case now let's say we have a hundred records sorry I meant a thousand so no thousand records in the table let's run this now this Benchmark already took way longer and you still see that offset page nation is a winner here nine milliseconds in average to run through all the pages and 11 for cursor pagination and regarding memory also offset pagination a bit better and then cursor pagination so that's still interesting with 1000 now let's go to 10 000 records shall We alright so now 10 000 and here are the IDS you see it here in the n10 000 records freshly created freshly created you say it like that I'm not sure about it but now let's run this we skip all or we skip through all the pages with 10 000 records in the table and here's the result so now cursor pagination way faster than offset page Nation 112 milliseconds and 437 for offset page Nation again memory allocation is a bit higher but now if it's about the time then you see that cursor page Nation now with 10 000 records already again not really a lot is way faster now I could run this with a hundred thousand and a million but this takes ages so please believe me cursor pagination is then significantly better and faster than offset pagination so this means in conclusion cursor based pagination uses the ID of any starting item and directly queries records that have IDs greater than this value so this approach is very efficient as it doesn't need to Traverse through all the preceding records now offset pagination here well it has to count and skip over a large number of rows to reach for instance the last page as we've seen in the first examples right but if you do not have a lot of entries in your database table then there are not or there is not a large number of rows to skip so in that case it may be faster than cursor pagination but with a significantly large data set well here's already 10 000 entries but maybe if you have a million entries in your in your database table then this operation becomes quite costly leading to the long execution times we already see here so with that I hope you now know more if so I would really appreciate it if you hit the like button subscribe to my Channel please it does make a difference thanks a lot for that have a look at my newsletter to get these videos here in your inbox and again thank you very much for watching and I hope I see you next time take care

Channel: Patrick God

Views: 4,042

Keywords:

Id: 9k2p3BPcEAo

Length: 15min 27sec (927 seconds)

Published: Tue Jun 20 2023