database paging design

Question

I'm fetching data for my grid like this

SELECT
   Orders.CustomerID,
   Orders.OrderTime,
   OrderItems.ProductID,
   OrderItems.Quantity
   FROM
   dbo.Orders INNER JOIN dbo.OrderItems
       ON Orders.ID = OrderItems.OrderID

I also need the total count for the pagination.

There're two options.

1- Do an another fetch

SELECT count(*) FROM dbo.Orders

2- Put the count statement in the query

SELECT
   Orders.CustomerID,
   Orders.OrderTime,
   OrderItems.ProductID,
   OrderItems.Quantity,
   (SELECT count(*) FROM dbo.Orders) as Count
   FROM
   dbo.Orders INNER JOIN dbo.OrderItems
       ON Orders.ID = OrderItems.OrderID

Which way should I go ?

Answer 1

Of the 2 methods you've put forward, the first (separate query) is better. The second method means the count will appear in every row returned which is a bit unnecessary. Also if the query returns 20 rows, the select count(*) will be executed 20 times (if i remember right, guess this could depend on which database engine you're using).

Additionally, depending on how much traffic you're envisaging and how big the table is likely to get, you can improve upon this by caching the result of select count(*) somewhere, and then refreshing it upon insertions / deletions to the table.

Answer 2

If you are using oracle you can use COUNT(*) OVER ( ) CNT. This one was more efficient as it takes single table scan

   SELECT
   Orders.CustomerID,
   Orders.OrderTime,
   OrderItems.ProductID,
   OrderItems.Quantity,
   COUNT(*) OVER ( ) CNT as Count
   FROM
   dbo.Orders INNER JOIN dbo.OrderItems
       ON Orders.ID = OrderItems.OrderID

Answer 3

As @Mailslut suggests, you should probably use two queries. However, you should probably add a WHERE clause to the query that fetches the data, so you only fetch the data that you actually need to show (unless you are caching it).

If more than one thread is accessing the database at a time, you will also need to somehow make sure that the count is kept in sync with the database.

Answer 4

I would consider something different, because what you are trying to do is not very simple, but quite necessary. Have you considered using the SQL Server row_number function? This way you will know how many records there are by looking at the max row_number returned, but also in the order you want.

SELECT
   Orders.CustomerID,
   Orders.OrderTime,
   OrderItems.ProductID,
   OrderItems.Quantity,
   ROW_NUMBER() OVER(ORDER BY Orders.CustomerId) rn
   FROM
   dbo.Orders INNER JOIN dbo.OrderItems
       ON Orders.ID = OrderItems.OrderID

Answer 5

If this is for SQL Server 2005 or higher, one of the best ways to get pagination is to use a Common Table Expression.

CREATE PROC MyPaginatedDataProc
@pageNumber INT
AS

WITH OrdersCTE (CustomerID, OrderTime, ProductID, Quantity, RowNumber)
AS
(
    SELECT
        Orders.CustomerID,
        Orders.OrderTime,
        OrderItems.ProductID,
        OrderItems.Quantity,
        ROW_NUMBER() OVER (ORDER BY OrderItems.OrderID) AS RowNumber
    FROM
        dbo.Orders INNER JOIN dbo.OrderItems ON Orders.ID = OrderItems.OrderID
)

SELECT
    CustomerID,
    OrderTime,
    ProductId,
    Quantity
FROM
    OrdersCTE
WHERE
    RowNumber BETWEEN (@pageNumber * 10) AND (((@pageNumber + 1) * 10) -1)

Otherwise for getting the total row count, I'd use a separate query like Mailslut said.