Most effective query plan using CTE and Offset/Fetch Next while still supplying a where clause

Question

How does one utilize fetch and offset in a cte to shorten the query plan?

How can it be limited to a where clause, not just selecting all?

Below is a full test that i believe isn't as efficient as it could be. Can this be optimized?

if exists(select 1 from sys.tables where name = 'TestTableSize')
begin
drop table TestTableSize;
end

CREATE TABLE dbo.TestTableSize
(
    MyKeyField VARCHAR(10) NOT NULL,
    MyDate1 DATETIME NOT NULL,
    MyDate2 DATETIME NOT NULL,
    MyDate3 DATETIME NOT NULL,
    MyDate4 DATETIME NOT NULL,
    MyDate5 DATETIME NOT NULL
);
go

DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @InsertDate DATETIME

SET @Lower = -730
SET @Upper = -1
SET @RowCount = 0

WHILE @RowCount < 100000
BEGIN
    SET @RowString = CAST(@RowCount AS VARCHAR(10))
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    SET @InsertDate = DATEADD(dd, @Random, GETDATE())
    
    INSERT INTO TestTableSize
        (MyKeyField
        ,MyDate1
        ,MyDate2
        ,MyDate3
        ,MyDate4
        ,MyDate5)
    VALUES
        (REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString
        , @InsertDate
        ,DATEADD(dd, 1, @InsertDate)
        ,DATEADD(dd, 2, @InsertDate)
        ,DATEADD(dd, 3, @InsertDate)
        ,DATEADD(dd, 4, @InsertDate))

    SET @RowCount = @RowCount + 1
END


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

WITH cte AS(
   select * from TestTableSize
   where MyDate1 between '1/1/2015' and '2/1/2015'
), cteCount AS (
    SELECT COUNT(1) AS MaxRows FROM cte
)
SELECT *
FROM cte, cteCount
ORDER BY cte.MyKeyField
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY

Really well presented question. I ran it and it took only 17 seconds. The only thing I can suggest is to put a clustered index on MyDate1. — Missy, Jul 31 '16 at 03:32
So the query above returns your desired result as is, or is there additional `WHERE` criteria you wanted to add? — Hart CO, Jul 31 '16 at 03:40

score 1 · Answer 1 · answered Jul 31 '16 at 03:50

1

I assume you're not looking to improve the performance of your test table build, so the only thing I see for cleanup is that your 2nd cte isn't needed, you can just use COUNT(*) OVER() in the top cte:

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

WITH cte AS(
   select *,COUNT(*) OVER() AS CT from TestTableSize
   where MyDate1 between '1/1/2015' and '2/1/2015'
)
SELECT *
FROM cte
ORDER BY cte.MyKeyField
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY

Beyond that, some indexing will help, MyDate1 and MyKeyField should certainly be indexed.

answered Jul 31 '16 at 03:50

Hart CO

34,064
6
48
63

Hart - from what I gather Count(*) Over() is very expensive, thats why I use the 2nd cte. http://stackoverflow.com/a/20130331/5304320 – JL1 Jul 31 '16 at 12:49
Sorry the problem with the above query, is that the first CTE having a where clause causes additional plan steps when just a select * doesn't – JL1 Jul 31 '16 at 12:51
That's interesting, I'm skeptical about it performing worse than the 2 `cte` method, but I'll have to test that out. It makes sense that filtering adds cost, I don't think you can refactor the query in a significant way, I think indexing is the best chance of improving performance. – Hart CO Jul 31 '16 at 13:48

Most effective query plan using CTE and Offset/Fetch Next while still supplying a where clause

1 Answers1