Getting total row count from OFFSET / FETCH NEXT

by

I encountered some performance issues using the COUNT() OVER() method. (I’m not sure if it was the server as it took 40 seconds to return 10 records and then later didn’t have any issues.) This technique worked under all conditions without having to use COUNT() OVER() and accomplishes the same thing:

DECLARE 
    @PageSize INT = 10, 
    @PageNum  INT = 1;

WITH TempResult AS(
    SELECT ID, Name
    FROM Table
), TempCount AS (
    SELECT COUNT(*) AS MaxRows FROM TempResult
)
SELECT *
FROM TempResult, TempCount
ORDER BY TempResult.Name
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY

More Related Contents:

  1. What is the best way to set a register to zero in x86 assembly: xor, mov or and?
  2. Why are loops always compiled into “do…while” style (tail jump)?
  3. Enhanced REP MOVSB for memcpy
  4. Why is Skylake so much better than Broadwell-E for single-threaded memory throughput?
  5. Efficiently compute pairwise squared Euclidean distance in Matlab
  6. Understanding the impact of lfence on a loop with two long dependency chains, for increasing lengths
  7. How to speed up A* algorithm at large spatial scales?
  8. Comparing BSXFUN and REPMAT
  9. Branch alignment for loops involving micro-coded instructions on Intel SnB-family CPUs
  10. Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures?
  11. What methods can be used to efficiently extend instruction length on modern x86?
  12. Should I use multiplication or division?
  13. Spark: Inconsistent performance number in scaling number of cores
  14. Lost Cycles on Intel? An inconsistency between rdtsc and CPU_CLK_UNHALTED.REF_TSC
  15. Faster implementation of verbal arithmetic in Prolog
  16. postgresql COUNT(DISTINCT …) very slow
  17. bsxfun implementation in matrix multiplication
  18. R: speeding up “group by” operations
  19. Why should recursion be preferred over iteration?
  20. LCP time between LightHouse and Performance – Google Chrome
  21. simplest tool to measure C program cache hit/miss and cpu time in linux?
  22. How slow (how many cycles) is calculating a square root?
  23. Performance optimisations of x86-64 assembly – Alignment and branch prediction
  24. How are cache memories shared in multicore Intel CPUs?
  25. Ways to improve git status performance
  26. why is data structure alignment important for performance?
  27. Return address prediction stack buffer vs stack-stored return address?
  28. When should we use prefetch?
  29. Relative performance of x86 inc vs. add instruction
  30. Efficient sse shuffle mask generation for left-packing byte elements

Leave a Comment