René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"[Order 06][P2] Replace offset checkpoint paging with keyset paging","articleBody":"## Priority order\n\nOrder: 06 of 09\nPriority: P2 performance\n\n## Problem\n\nCheckpoint reads use offset/ROW_NUMBER paging instead of advancing by the last seen checkpoint.\n\nEvidence:\n\n- `src/NEventStore.Persistence.Sql/SqlPersistenceEngine.cs:347`\n- Checkpoint reads pass a fixed checkpoint token.\n- Common SQL uses `LIMIT @Limit OFFSET @Skip`.\n- SQL Server transforms checkpoint reads to `ROW_NUMBER()` paging in `MsSqlDialect.CommonTableExpressionPaging`.\n\n## Impact\n\n- Long catch-up reads become increasingly expensive as `@Skip` grows.\n- SQL Server recomputes row numbers for every page.\n- This is a hot path for polling clients and projections.\n\n## Mandatory test-first workflow\n\n- Add tests first that verify ordered, complete multi-page checkpoint reads.\n- Tests must cover result counts greater than the configured page size.\n- Tests must cover from/to checkpoint ranges.\n- Do not change production paging logic until these tests are in place.\n\n## Proposed action\n\n- Page checkpoint reads by last seen `CheckpointNumber` instead of offset.\n- Update sync and async paging delegates for checkpoint queries to set `@CheckpointNumber` to the last row.\n- Keep `@ToCheckpointNumber` as the upper bound for range reads.\n\n## Acceptance criteria\n\n- Multi-page checkpoint reads remain ordered and gap-free.\n- Bucket-scoped and global checkpoint reads are both covered.\n- A benchmark or integration timing plan is documented for large catch-up reads.\n\nSource: `docs/Project-Analysis-Issue-Drafts.md`.","author":{"url":"https://github.com/AGiorgetti","@type":"Person","name":"AGiorgetti"},"datePublished":"2026-06-11T09:21:23.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/63/NEventStore.Persistence.SQL/issues/63"}