U4-8837 - Change paging query for SQL server 2012+ to be more efficient

Created by Shannon Deminick 09 Aug 2016, 12:31:50 Updated by Claus Jensen 10 Aug 2016, 11:13:18

Tags: Unscheduled

Currently the paging for SQL server is using paging compatible with 2008 using derived tables with ROW_Number, however in 2012+ SQL server supports Offset and Fetch which is more efficient, see: https://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-server-paging-methods/

Turns out that SQL CE also uses Offset and Fetch so we can just ask PetaPoco to use that syntax for SQL server 2012+ instead.


Shannon Deminick 09 Aug 2016, 13:34:34

Fixed in rev: c1da28dd47611d693c663d3a5225c754ceef2097

This now will execute the same paging the SQL CE uses for SQL Server 2012+ which is much more efficient and readable. In NPoco this is the only paging syntax used.

Claus Jensen 10 Aug 2016, 11:13:14

Tested with both Sql and SqlCe with no issues.

Priority: Normal

Type: Bug

State: Fixed


Difficulty: Normal


Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 7.5.0

Sprint: Sprint 39

Story Points: