Каков наилучший способ выполнения разбиения на страницы на SQL Server?

Ответ 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 (@[email protected])
END

это запрос, который я использую для подкачки. используйте его, и u получит ur желаемых 10 записей за 4-5 секунд. я получаю 10 записей за 3 секунды, а итоговые записи в моем db составляют 10 миллионов, не используют 10 лучших, он будет приносить только 10 записей каждый раз. в моем случае я поддерживаю размер страницы и номер строки запуска (@FromRow) в сеансе, и я передаю эти два значения в приведенную ниже хранимую процедуру и получаю результат. Более того, если вы используете SQL 2012, вы можете использовать OFFSET и Fetch следующие 10 строк. найдите в google ключевое слово OFFSET, и вы увидите нужный результат сверху.

спасибо

Ответ 2

Используйте ROW_NUMBER() и реализуйте статическую служебную функцию (как GetPaginatedSQL в моем коде), которая автоматически обертывает ваш исходный запрос SQL в ограниченный/разбитый на страницы.

Это я использую:

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()
            );
        }
    }
}

Вышеупомянутая функция может быть улучшена, но является начальной версией.

Затем, в ваших DAO, вы должны просто сделать что-то вроде этого:

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();
}

Надеюсь, что это поможет.

Ответ 3

Я использую следующий шаблон для (автоматически) создания подкачки подкачки:

select top (@takeN) <your-column-list>
from (
    select qSub2.*, _row=row_number() over (order by SomeColumn Asc, SomethingElse Desc)
    from (
        select top (@takeN + @skipN) <your-column-list> 
        from ( 
            select <your-subquery-here>
        ) as qSub1 
        order by SomeColumn Asc, SomethingElse Desc
    ) as qSub2
) qSub3
where _row > @skipN
order by _row

Примечания к этому шаблону:

• Подпрограмма концептуально пропускает строки @skipN, а затем берет следующие строки @takeN.
• Если вам не нужен дополнительный столбец _row в результате, вы можете заменить <your-column-list> на *; Я использую явный список столбцов, потому что он позволяет мне подмножать набор столбцов во время выполнения, что может быть полезно, например. для поиска только столбцов ключевых слов и т.п.
• Ваши предложения order by должны быть одинаковыми; sqm server optmizer, как правило, достаточно умен, чтобы понять это. Дублирование является побочным эффектом предложения top, используемого для обрезания результатов; top не является законным для несортированных подзапросов. И верх полезен, чтобы помочь оптимизатору запросов понять, что этот запрос, скорее всего, вернет несколько строк.
• Причины использования @takeN и @skipN в отличие от параметров на основе номера страницы + размера довольно незначительны. Во-первых, он немного более гибкий и немного более простой в запросе, а во-вторых, он немного повышает производительность серверов sql-серверов: БД не особенно блестяще оптимизирует эти запросы в первую очередь, и надежда что внешнее, простое верхнее предложение, как это делает тривиальным для оптимизатора, чтобы понять максимальное количество строк. В общем, я стараюсь избегать выполнения вычислений в sql. Я мог бы одинаково хорошо работать в коде, поскольку он имеет тенденцию путать оптимизатор (хотя в конкретном случае экспериментов @pagecount * @pagesize показано, что это не огромная проблема)

Обратите внимание, что SQL Server 2012 поддерживает новый смещение... предложение выборки именно для этого сценария, который намного проще.