/pagination

Pagination implementation over big amount of data that requires pagination and sorting

Primary LanguageC#

pagination

Pagination implementation over big amount of data that requires pagination and sorting

Sample data

Currently the database has 4 examples on 4 diffeerent tables which one contains a different number of records but shares the same schema. All this data is automatically added to the database on the first run.

select count(*) from 1_FirstExample; -- 1k 
select count(*) from 2_SecondExample; -- 100k
select count(*) from 3_ThirdExample; -- 1M 
select count(*) from 4_ForthExample; -- 5M

Results

  • Each of tests below tries to load the 2 first pages of the dataset.
  • Dabase with 2 cores and 8GB ram (docker)
  • App runing on a Windows machine, 32gb ram, i5 9th gen

CursorPaginationBenchmark Cursor pagination using ID order only without ordering data

Method Mean Error StdDev
RunCursorPaginationPagination_Over_1k 4.580 ms 0.0464 ms 0.0434 ms
RunCursorPaginationPagination_Over_100k 4.085 ms 0.0783 ms 0.0870 ms
RunCursorPaginationPagination_Over_1million 4.049 ms 0.0617 ms 0.0577 ms
RunCursorPaginationPagination_Over_5million 4.081 ms 0.0598 ms 0.0559 ms
Run time: 00:01:04 (64.7 sec), executed benchmarks: 4
Global total time: 00:01:09 (69.45 sec), executed benchmarks: 4

Database CPU usage < 30%

LimitOffsetPaginationBenchmark Limit Offset paging without ordering data

Method Mean Error StdDev
RunLimitOffsetPagination_Over_1k 4.412 ms 0.0855 ms 0.0878 ms
RunLimitOffsetPagination_Over_100k 3.964 ms 0.0777 ms 0.0983 ms
RunLimitOffsetPagination_Over_1million 3.970 ms 0.0760 ms 0.0813 ms
RunLimitOffsetPagination_Over_5million 4.048 ms 0.0762 ms 0.1940 ms
Run time: 00:01:42 (102.49 sec), executed benchmarks: 4
Global total time: 00:01:47 (107.15 sec), executed benchmarks: 4

Database CPU: <30%

CursorPaginationWithOrderBenchmark: Cursor with order on Sorting_FirstName (contains firstname + cursor unique identifier)

Method Mean Error StdDev
RunCursorPaginationPagination_OrderByFirstName_Over_1k 6.344 ms 0.1158 ms 0.0967 ms
RunCursorPaginationPagination_OrderByFirstName_Over_100k 142.488 ms 2.4281 ms 2.2712 ms
RunCursorPaginationPagination_OrderByFirstName_Over_1million 1,797.274 ms 35.7267 ms 45.1828 ms
RunCursorPaginationPagination_OrderByFirstName_Over_5million 8,408.856 ms 46.6687 ms 43.6540 ms
Run time: 00:05:51 (351.37 sec), executed benchmarks: 4
Global total time: 00:05:55 (356 sec), executed benchmarks: 4

Database CPU 100%

LimitOffsetWithOrderPaginationBenchmark Limit offset Ordering by FirstName

Method Mean Error StdDev
RunLimitOffsetPagination_OrderByFirstName_Over_1k 6.223 ms 0.0610 ms 0.0571 ms
RunLimitOffsetPagination_OrderByFirstName_Over_100k 128.780 ms 1.7591 ms 1.5594 ms
RunLimitOffsetPagination_OrderByFirstName_Over_1million 1,566.146 ms 9.9261 ms 8.7992 ms
RunLimitOffsetPagination_OrderByFirstName_Over_5million 7,613.383 ms 35.0308 ms 31.0539 ms
Run time: 00:04:39 (279.97 sec), executed benchmarks: 4
Global total time: 00:04:45 (285.06 sec), executed benchmarks: 4

Database CPU 100%

Test using 100 page size + navigation through 100 pages

comparison only between paginations and no ordering involved

LIMIT OFFSET - DATABASE GOT 61%+

Method Mean Error StdDev
RunLimitOffsetPagination_Over_1k 234.5 ms 4.65 ms 12.33 ms
RunLimitOffsetPagination_Over_100k 463.7 ms 9.20 ms 14.05 ms
RunLimitOffsetPagination_Over_1million 461.4 ms 8.93 ms 11.29 ms
RunLimitOffsetPagination_Over_5million 464.0 ms 9.01 ms 8.85 ms

CURSOR PAGINATION - DATABASE 27%

Method Mean Error StdDev Median
RunCursorPaginationPagination_Over_1k 22.98 ms 0.342 ms 0.285 ms 22.92 ms
RunCursorPaginationPagination_Over_100k 197.84 ms 3.872 ms 10.201 ms 194.55 ms
RunCursorPaginationPagination_Over_1million 192.28 ms 3.827 ms 5.109 ms 193.91 ms
RunCursorPaginationPagination_Over_5million 193.53 ms 3.861 ms 5.285 ms 194.67 ms

Running

Database should be running

docker run --cpus=2.000 --memory="8g" --name pagination-mysql8 -e MYSQL_ROOT_PASSWORD="root" -p 3306:3306 -d mysql:8

Run migrations

dotnet tool install --global dotnet-ef
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet ef migrations add Baseline
dotnet ef database update

Navigate to pagination folder where the csproj is.

Run the project (the first run will take time as it will populate all mock data)

dotnet run -c Release

It will be prompted all options to run each benchmark separately.

Legends

  Mean   : Arithmetic mean of all measurements
  Error  : Half of 99.9% confidence interval
  StdDev : Standard deviation of all measurements
  Median : Value separating the higher half of all measurements (50th percentile)
  1 ms   : 1 Millisecond (0.001 sec)