Which is the best way to perform pagination on SQL Server?

Question

I have a database with more than 2 million records and I need to perform a pagination to show on my web application, which must have 10 records per page in a DataGrid.

I already tryied to use ROW_NUMBER(), but this way will select all the 2 million records and then get only 10 records. I also tryied to use TOP 10, but I would have to save the first and last id to control the pages. And I've read that using DataAdapter.Fill() will select all the content and then get the 10 records that I need.

Which is the best way? Should I use DataAdapter.Fill()? Or use the SQL Server's function ROW_NUMBER()? Or try to use TOP 10?

Answer 1

ALTER PROCEDURE [dbo].[SP_tblTest_SelectSpecificRecordsWithCTE]
    @FromRow int = 1000000,
    @PgSize int = 10
AS
BEGIN
    ;WITH RecordsRN AS
    (
        select ID, colValue, ROW_NUMBER() over(order by colvalue) as Num from tblTest
    )
    SELECT ID Value, colValue Text FROM RecordsRN WHERE Num between @FromRow AND (@FromRow+@PgSize-1)
END

that is the query i am using for paging. use it and u will get ur desired 10 records in 4-5 seconds. i am getting 10 records in 3 seconds and total records in my db are 10 million, dont use top 10 it will only bring same 10 records everytime. in my case i am maintaining page size and starting row number (@FromRow) in the session and i pass these two values to the below given stored procedure and get the result. Further more if you are using SQL 2012 you might want to use OFFSET and Fetch next 10 rows kind of thing. search on google about OFFSET keyword and you will see your desired result on top.

thanks

Answer 2

Use ROW_NUMBER() and implement an static utility function (as GetPaginatedSQL in my code), that automatically wraps your original SQL query into a limited/paginated one.

This is the one I use:

namespace Persistence.Utils
{
    public class SQLUtils
    {
        /// <summary>
        /// Builds a paginated/limited query from a SELECT SQL.
        /// </summary>
        /// <param name="startRow">Start row</param>
        /// <param name="numberOfRows">Number/quatity of rows to be expected</param>
        /// <param name="sql">Original SQL (without its ordering clause)</param>
        /// <param name="orderingClause">MANDATORY: ordering clause (including ORDER BY keywords)</param>
        /// <returns>Paginated SQL ready to be executed.</returns>
        /// <remarks>SELECT keyword of original SQL must be placed exactly at the beginning of the SQL.</remarks>
        public static string GetPaginatedSQL(int startRow, int numberOfRows, string sql, string orderingClause)
        {
            // Ordering clause is mandatory!
            if (String.IsNullOrEmpty(orderingClause))
                throw new ArgumentNullException("orderingClause");

            // numberOfRows here is checked of disable building paginated/limited query
            // in case is not greater than 0. In this case we simply return the
            // query with its ordering clause appended to it. 
            // If ordering is not spe
            if (numberOfRows <= 0)
            {
                return String.Format("{0} {1}", sql, orderingClause);
            }
            // Extract the SELECT from the beginning.
            String partialSQL = sql.Remove(0, "SELECT ".Length);

            // Build the limited query...
            return String.Format(
                "SELECT * FROM ( SELECT ROW_NUMBER() OVER ({0}) AS rn, {1} ) AS SUB WHERE rn > {2} AND rn <= {3}",
                orderingClause,
                partialSQL,
                startRow.ToString(),
                (startRow + numberOfRows).ToString()
            );
        }
    }
}

The function above might be improved, but is an initial implementation.

Then, in your DAOs, you should be just making something like this:

using (var conn = new SqlConnection(CONNECTION_STRING))
{
    using (var cmd = conn.CreateCommand())
    {
        String SQL = "SELECT * FROM MILLIONS_RECORDS_TABLE";
        String SQLOrderBy = "ORDER BY DATE ASC "; //GetOrderByClause(Object someInputParams);
        String limitedSQL = GetPaginatedSQL(0, 50, SQL, SQLOrderBy);

        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter();

        cmd.CommandText = limitedSQL;

        // Add named parameters here to the command if needed...

        adapter.SelectCommand = cmd;
        adapter.Fill(ds);

        // Process the dataset...
    }
    conn.Close();
}

Hope it helps.