SQL Server – Optimistic Paging and Separate COUNT Query

pagingperformancequery-performancesql serversql-server-2012

I am using SQL Server 2012 FETCH/OFFSET after searching a lot that this will give me the best performance. But for paging to work, I need a total count.

There are 2 ways to achieve this:

Paging query can include COUNT(*) OVER() column
Separate query

Which one will be best? Here is my SQL:

ALTER PROCEDURE [dbo].[GetAllProducts] (@PageNumber int,
@PageCount int)
AS
BEGIN
    SELECT
        Products.[ID] AS [ID1],
        Products.[Name] AS [Name],
        Products.[Price] AS [Price],
        Products.[NewPrice] AS [NewPrice],
        Products.[ShortDescription] AS [ShortDescription],
        Products.[SKU] AS [SKU],
        Products.[ProductTypeID] AS [ProductTypeID],
        Products.[ImageID] AS [ImageID],
        Products.[Promotion] AS [Promotion],
        Products.[ParentID] AS [ParentID],
        Products.[Attributes] AS [Attributes],
        ROW_NUMBER() OVER (ORDER BY Products.[ID] ASC) AS [row_number]
    FROM [dbo].[Products] AS Products
    INNER JOIN [dbo].[Images] AS Images
        ON Products.[ImageID] = Images.[ID]
    ORDER BY Products.[ID] ASC
    OFFSET ((@PageNumber - 1) * @PageCount) ROWS
    FETCH NEXT @PageCount ROWS ONLY;
END

The Products table has more than 50,000 rows, sometimes static, sometimes changing a lot.

Best Answer

Assuming you need an exact count and not an approximation, and you want this to go fast without index scanning (yes, please!), a good place to start would be to create an indexed view that maintains the count of rows:

CREATE VIEW dbo.MyView
    WITH SCHEMABINDING
AS
    SELECT
        COUNT_BIG(*) AS NumberOfRows
        FROM [dbo].[Products];
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyView
    ON dbo.MyView(NumberOfRows);

^{(Note: an ID column can't be added to this view and then the clustered index created on it.)}

Yes, this will add a small amount of overhead to writes to the base table; it doesn't sound like that's going to be a problem in this case.

Then it's really easy and efficient to read a transactionally-consistent row count:

SELECT NumberOfRows FROM dbo.MyView WITH(NOEXPAND);

which can either be included in the same query, or separately. Personally, I'd try to keep it separate and return the value as an OUTPUT parameter. In either case, for an exact count, I'd strongly recommend using snapshot isolation to avoid out-of-sync issues.

This type of structure could also be created with a user table and a trigger, or a "business logic" approach... but because of the number of moving parts in either of those solutions, I really wouldn't recommend them unless there's no other choice.

If you do go this route, be sure to patch SQL Server to at least:

Cumulative Update 7 for SQL Server 2012 SP2
Cumulative Update 1 for SQL Server 2014 Service Pack 1
Cumulative Update 8 for SQL Server 2014 RTM

See the Knowledge Base article:

Incorrect results are returned in an indexed view after an Insert or Delete operation on the base table occurs in SQL Server 2014

For more details and background, see An Indexed View Bug with Scalar Aggregates by Paul White.

Related Solutions

Sql-server – Paging and grouping using CTE

I was able to do this using multiple CTEs, similar to the following:

;with cte as
(
  SELECT DISTINCT d.AppId,
    d.AppName,
    d.AppType,
    Tags = STUFF((SELECT ', ' + t.TagName
                  FROM AppTags t
                  where d.AppID = t.AppID
                  FOR XML PATH (''))
                  , 1, 1, '') 
  FROM AppDetails d
  WHERE d.AppID = '1'
),
results as (
    SELECT ROW_NUMBER() OVER (ORDER BY AppID) AS RowNumber, * FROM cte
        where tags like '%test1%'
)
select *
from results
WHERE (RowNumber >  @pageStart) AND (RowNumber <=  @pageStart + @pageLength)

SQL Server – Impact of Column Count in SELECT Query on Buffer Cache

SQL Server stores all the columns for one row together in a single disk page. (It's more complex than this but for int and char etc. this is more-or-less true.) To retrieve any column's value the whole page is read into RAM. So once you have any column available for a given row, all of the columns for that row are available.

There are several buffers / caches involved in responding to a query. One is the great big page bufferpool where pages read from disk are held in memory so execution plans can act on them. And yes, if one query causes a page to be loaded into the bufferpool all subsequent queries will use the same page from the bufferpool and not have to suffer the IO overhead. This is an incidental performance gain, however, and shouldn't be relied upon because SQL Server may choose to evict any page from the bufferpool at any time it needs the space.

Another set of buffering occurs when the output from your query gets sent over your connection to your application. This is (mostly) on a per-connetion basis so duplicating the work duplicates the resources required (more or less).

Another cost is the optimisations you miss out on when you return all columns. Covering indexes and "INCLUDES" columns are meaningless. Every read will have to retrieve the underlying clustered index (or heap) page. It's a fair bet that SQL Server will not be using specific indexes it might otherwise have chosen because a scan-the-cluster plan is cheaper than an index-and-cluster plan.

Best Answer

Related Solutions

Sql-server – Paging and grouping using CTE

SQL Server – Impact of Column Count in SELECT Query on Buffer Cache

Related Question