Paging with SQL Server and MySQL

Paging sets of database records in a web application efficiently has long been a relatively tricky undertaking. Whilst web form controls such as the GridView offer control based paging very easily, they will still hit the database for all the records before processing the paging within the web application, and hence unnecessary amounts of data are passed between database and web server.

Database based techniques - only retrieving the particular rows you require in each request - are therefore generally to be preferred. But some of those provide their own headaches - for example as well as retrieving the particular set of rows for a page, you will often want to know what the full count of records to be able to display a message along the lines of: 200 records found. Page 1 of 20.

SQL Server Paging

Prior to the 2005 version, I would do this using SQL Server with two queries - one to get the page of data and another, using the same WHERE clause, to get the count. With SQL Server 2005 though, you can get both with a single query, using the following syntax:

CREATE PROCEDURE getProducts
  @page int,
  @recordsPerPage int
AS
WITH Results AS (
  SELECT ROW_NUMBER() OVER ( ORDER BY Name ) RowNumber,
      ID, Name
  FROM Products
)
SELECT ID,Name,
  (SELECT Count(*) FROM Results) TotalRows
FROM Results
WHERE @page = 0 OR @recordsPerPage = 0 OR RowNumber BETWEEN @recordsPerPage * (@page - 1) AND @recordsPerPage * @page
ORDER BY Name

With this stored procedure, passing 0 for the page number or the records per page simply returns all of them, but with non-zero values passed only the required page of data is returned. In addition, every row will have an additional column TotalRows that returns the full count of the query.

MySQL Server Paging

On a recent project we had need to port an SQL server database to MySQL, at which point we discovered this is one of the features that isn't so easy to translate. MySQL supports the LIMIT clause in SELECT statements. It also supports a clause called SQL_CALC_FOUND_ROW which returns the total number of records returned irrespective of the LIMIT clause. These two together can be used as the basis of a similar paging technique to that described above.

The crux of the problem though is that MySQL doesn't support the use of parameters in the LIMIT clause in functions (stored procedures).

Instead you have to make use of prepared statements and execute two of them within the function to have the same result as the SQL server version. An example follows:

CREATE PROCEDURE `getProducts`(page INT,@recordsPerPage INT)
BEGIN

SET @sql = CONCAT(
  "SELECT SQL_CALC_FOUND_ROWS ID,Name
  FROM products
  LIMIT ", page,",",@recordsPerPage,"; "
);

set @sql2 = "SELECT found_rows();";

prepare stmt from @sql;
execute stmt;

prepare stmt from @sql2;
execute stmt;

drop prepare stmt;

END;

In this case, rather than having one result set with an additional column holding the total number of rows, there will be a second result set containing just a single field where the total record count can be extracted.

In .Net the IDataReader method NextResult can be used to access this additional recordset.