CODEWITHSUNDEEP
sql-server-2008paginationrow-number
Lion.k
Lion.k

Reputation: 2769

Too many number of executions in execution plan when using ROW_NUMBER paging in sql server

IN SQL SERVER 2008.

I have a table having huge rows. I implemented paging using row_number. But I think it isn't effective cost.

Execution plan enter image description here

The Number of Executions => 15005. If I change 15005 to 150005 then, The number of Excutions increases to 150005.

Is it ok? Is there any method reducing this number of excutions?

This is my query

SELECT *
FROM 
(
    select *, ROW_NUMBER() OVER(ORDER BY send_time) as row_num
    FROM [dbo].[GlobalMessage]
    WHERE active =1
) as T
WHERE row_num >= 15000 and row_num < 15005

and Table info

CREATE TABLE [dbo].[GlobalMessage](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [active] [int] NOT NULL,
    [message] [nvarchar](200) NOT NULL,
    [send_time] [int] NOT NULL,
 CONSTRAINT [PK_GlobalMessage] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20160823-125945] ON [dbo].[GlobalMessage]
(
    [active] ASC,
    [send_time] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Upvotes: -1

Views: 583

Answers (1)

Freddy Ayala
Freddy Ayala

Reputation: 49

You might get better results using OFFSET/FETCH with the following syntax (Assuming SQL Server 2012 or higher):

;WITH pg AS 
(
  SELECT [key_column] 
  FROM [dbo].[GlobalMessage]
  ORDER BY send_time
  OFFSET @PageSize * (@PageNumber - 1) ROWS
  FETCH NEXT @PageSize ROWS ONLY
)
SELECT t.*
  FROM [dbo].[GlobalMessage] AS t
  INNER JOIN pg ON t.[key_column] = pg.[key_column] -- or EXISTS
  ORDER BY send_time;

Here is an excellent article about the performance and use of this approach: http://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch

Adding an alternative for SQL Server 2008:

declare @rowsPerPage as bigint; 
declare @pageNum as bigint; 
set @rowsPerPage=25; 
set @pageNum=10;   

With SQLPaging As   ( 
    Select Top(@rowsPerPage * @pageNum) ROW_NUMBER() OVER (ORDER BY ID asc) 
    as resultNum, * 
    FROM [dbo].[GlobalMessage] 
     WHERE active =1
    )
select * from SQLPaging with (nolock) where resultNum > ((@pageNum - 1) * @rowsPerPage)

Upvotes: 0

Related Questions