Обсуждение реализации разбиения на страницы большого набора результатов в ASP.NET.

В веб-приложениях разбивка на страницы большого набора результатов базы данных является хорошо известной проблемой. Проще говоря, вы не хотите, чтобы все данные запроса отображались на одной странице, поэтому более подходящим будет отображение с разбиением на страницы. Хотя в традиционном ASP это непростая задача, в ASP.NET элемент управления DataGrid упрощает этот процесс до нескольких строк кода. Таким образом, в asp.net разбиение по страницам очень простое, но событие разбиения по страницам DataGrid по умолчанию считывает все записи из базы данных и помещает их в веб-приложение asp.net. Когда у вас более миллиона данных, это вызовет серьезные проблемы с производительностью (если вы не верите в это, вы можете выполнить запрос в своем приложении и посмотреть потребление памяти aspnet_wp.exe в диспетчере задач. Ситуация). Вот почему необходимо настроить поведение пейджинга, чтобы гарантировать получение только тех записей данных, которые необходимы текущей странице.

В Интернете есть много статей и постов об этой проблеме, а также некоторые зрелые решения. Моя цель при написании этой статьи — не показать вам хранимую процедуру, которая решит все ваши проблемы, а оптимизировать существующие методы и предоставить вам приложение для тестирования, чтобы вы могли делать это в соответствии с вашими потребностями.

Но меня не очень устраивают методы, которые сейчас представлены в Интернете. Во-первых, используется традиционный ADO, написанный явно для «древнего» ASP. Остальные методы представляют собой хранимые процедуры SQL Server, и некоторые из них непригодны из-за слишком медленного времени отклика, как вы можете видеть из результатов производительности в конце статьи, но есть несколько, которые привлекли мое внимание.

Обобщение

Я хочу тщательно проанализировать три наиболее часто используемых в настоящее время метода: временные таблицы (TempTable), динамический SQL (DynamicSQL) и подсчет строк (Rowcount). В дальнейшем я предпочитаю называть второй метод методом (по возрастанию-убыванию) Asc-Desc. Я не думаю, что динамический SQL — хорошее название, поскольку логику динамического SQL можно применить и в другом методе. Общая проблема всех этих хранимых процедур заключается в том, что вам необходимо оценить, по каким столбцам вы собираетесь сортировать, а не только по столбцам первичного ключа (столбцы PK), что может привести к ряду проблем - для каждого запроса вам необходимо отображать его посредством разбиения по страницам, что означает, что для каждого отдельного столбца сортировки у вас должно быть много разных запросов на разбиение по страницам, а это означает, что вы либо выполняете другую хранимую процедуру для каждого столбца сортировки (независимо от того, какой метод разбиения по страницам используется), либо вам нужно поместите эту функциональность в хранимую процедуру с помощью динамического SQL. Эти два метода незначительно влияют на производительность, но повышают удобство обслуживания, особенно если вам нужно использовать этот метод для отображения разных запросов. Поэтому в этой статье я попытаюсь использовать динамический SQL для суммирования всех хранимых процедур, но по некоторым причинам мы можем добиться только частичной универсальности, поэтому для сложных запросов все равно придется писать независимые хранимые процедуры.

Вторая проблема с разрешением всех полей сортировки, включая столбцы первичного ключа, заключается в том, что если эти столбцы не проиндексированы должным образом, ни один из этих методов не поможет. Во всех этих методах сначала необходимо отсортировать источник подкачки. Для больших таблиц данных стоимость сортировки с использованием неиндексных столбцов незначительна. В этом случае все хранимые процедуры невозможно использовать в реальных ситуациях из-за большого времени отклика. (Соответствующее время варьируется от нескольких секунд до нескольких минут, в зависимости от размера таблицы и первой полученной записи). Индексы в других столбцах могут вызвать дополнительные нежелательные проблемы с производительностью, например, они могут стать очень медленными, если вы импортируете много данных каждый день.

Временная таблица

Сначала я расскажу о методе временной таблицы. Это широко рекомендуемое решение, с которым я несколько раз сталкивался в своих проектах. Давайте разберемся в сути этого метода:

CREATE TABLE #Temp(
ID int ИДЕНТИЧНОСТЬ ПЕРВИЧНЫЙ КЛЮЧ,
ПК /*здесьPKtype*/
)

INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn

SELECT FROM Table JOIN # Temp temp ON Table.PK = temp .PK ORDER BY temp .ID WHERE ID > @StartRow AND ID< @EndRow

путем копирования всех строк во временный файл In table, мы можем дополнительно оптимизировать запрос (SELECT TOP EndRow...), но ключом является наихудший сценарий: таблица, содержащая 1 миллион записей, создаст временную таблицу с 1 миллионом записей.

по возрастанию-убыванию

в своем тесте.

Этот метод использует сортировку по умолчанию в подзапросе и обратную сортировку в основном запросе. Принцип такой:

ОБЪЯВИТЬ @temp TABLE(
ПК /* Тип ПК */
НЕ НУЛЕВОЙ ПЕРВИЧНЫЙ
)

INSERT INTO @temp SELECT TOP @PageSize PK FROM
(
ВЫБРАТЬ ВЕРХ(@StartRow + @PageSize)
ПК,
SortColumn /* Если столбец сортировки отличается от PK, SortColumn должен
тоже будет извлечено, иначе нужен только ПК
*/

ORDER BY SortColumn
/*
порядок по умолчанию – обычноASC
*/
)

ПОРЯДОК ПО Сортировочному столбцу
/*
обратный порядок по умолчанию – обычно DESC
*/

SELECT FROM Table JOIN @Temp temp ON Table .PK= temp .PK
Упорядочить по столбцу сортировки
/*
заказ по умолчанию
*/

подсчета строк

опирается на выражение SET ROWCOUNT в SQL, благодаря чему можно пропустить ненужные строки и получить нужные записи строк:

DECLARE @Sort /* тип сортирующего столбца */

SET ROWCOUNT @ StartRow

SELECT @Sort=SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize

SELECT FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

подзапроса

, которые я рассмотрел, и их источники различны. Первый — это хорошо известный метод тройного запроса или самозапроса. В этой статье я также использую аналогичную общую логику, охватывающую все другие хранимые процедуры. Идея здесь состоит в том, чтобы подключиться ко всему процессу, я внес некоторые сокращения в исходный код, поскольку в моих тестах счетчик записей не нужен)

SELECT FROM Table WHERE PK IN(
ВЫБЕРИТЕ TOP @PageSize PK ИЗ ТАБЛИЦЫ, ГДЕ PK НЕ ВХОДИТ
(
ВЫБРАТЬ TOP @StartRow PK ИЗ таблицы ORDER BY SortColumn)
ЗАКАЗАТЬ ПО столбцу сортировки)
ORDER BY SortColumn

Cursor

Просматривая дискуссионную группу Google, я нашел последний метод. Этот метод использует динамический курсор на стороне сервера. Многие люди стараются избегать использования курсоров, потому что они неуместны и неэффективны из-за их упорядоченности. Но, оглядываясь назад, можно сказать, что разбиение на страницы на самом деле является упорядоченной задачей. Независимо от того, какой метод вы используете, вам придется вернуться к начальной строке для записи. В предыдущем методе вы сначала выбираете все строки перед началом записи, добавляете необходимые строки для записи, а затем удаляете все предыдущие строки. Динамические курсоры имеют опцию FETCH RELATIVE, которая выполняет магические переходы. Основная логика следующая:

DECLARE @PK /* PKType */

DECLARE @tblPK

TABLE(
PK /*PKType*/ NOT NULL ПЕРВИЧНЫЙ КЛЮЧ
)

ОБЪЯВИТЬ PagingCursor CURSOR DYNAMICREAD_ONLY FOR
ВЫБРАТЬ @PK ИЗ таблицы ORDER BY SortColumn

OPEN PagingCursor
ВЫБРАТЬ ОТНОСИТЕЛЬНЫЙ @StartRow ИЗ PagingCursor В @PK,

ПОКА @PageSize>0 И @@FETCH_STATUS =0
НАЧИНАТЬ
ВСТАВИТЬ ЗНАЧЕНИЯ @tblPK(PK)(@PK)
ВЫБРАТЬ СЛЕДУЮЩИЙ ИЗ PagingCursor В @PK
SET @PageSize = @PageSize - 1
КОНЕЦ

ЗАКРЫТЬ
ПейджингКурсор
ВЫДЕЛИТЬ
PagingCursor

SELECT FROM Table JOIN @tblPK temp ON Table .PK= temp .PK

Обобщение сложных запросов

в ORDER BY SortColumn

Ранее я указывал, что все хранимые процедуры используют динамический SQL для достижения обобщения, поэтому теоретически они могут использовать любые сложные запросы. Ниже приведен пример сложного запроса на основе базы данных Northwind.

ВЫБРАТЬ Customers.ContactName AS Customer, Customers.Address + ' , ' + Customers.City + ', '+ Customers.Country
Адрес AS, SUM([OrderDetails].UnitPrice*[OrderDetails].Количество)
AS [Totalmoneyspent]

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

INNER JOIN [ OrderDetails ] ON Orders.OrderID = [ OrderDetails].OrderID

WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexico'

ГРУППИРОВАТЬ ПО Клиенты.Имя контакта,Клиенты.Адрес,Клиенты.Город, Клиенты.Страна
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ].Quantity)) > 1000

ORDER BY Customer DESC ,Address DESC

возвращает вызов подкачивающего хранилища второй страницы следующим образом:

EXECResultName

/*Tables */

'
Клиенты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Заказы ON Customers.CustomerID=Orders.CustomerID
ВНУТРЕННЕЕ СОЕДИНЕНИЕ [OrderDetails] ON Orders.OrderID=[OrderDetails].OrderID
'
,
/* ПК */

'
Клиенты.CustomerID
'
,
/* ЗАКАЗ */

'
Клиенты.Имя контакта DESC,Клиенты.АдресDESC
'
,

/*Номер страницы */

2
,

/*Размер страницы */

10
,

/*Поля */

'
Клиенты. Контактное имя КАК Клиент,
Клиенты.Адрес+'' , '' +Клиенты.Город+ '' , '' +Клиенты.Страна ASAddress, SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity)AS[Totalmoneyspent]
'
,

/*Фильтр */

'
Customers.Country<>'' США '' ANDCustomers.Country<> '' Мексика ''' ,

/*GroupBy */

'
Клиенты.КлиентКод,Клиенты.ИмяКонтакта,Клиенты.Адрес,
Клиенты.Город,Клиенты.Страна
HAVING(SUM([Детали заказа].ЕдиницаЦена*[Детали заказа].Количество))>1000
'

Стоит отметить, что вы использовали псевдонимы в инструкции ORDER BY в исходном запросе, но лучше не делать этого в страничной хранимой процедуре, поскольку пропуск строк перед началом записи отнимает много времени. На самом деле существует множество методов реализации, но принцип заключается в том, чтобы не включать все поля в начале, а включать только столбец первичного ключа (эквивалент столбца сортировки в методе RowCount), что может ускорить завершение процесса. задача. Только на странице запроса получены все обязательные поля. Более того, в итоговом запросе нет псевдонимов полей, а в запросах пропуска строк необходимо заранее использовать индексные столбцы.

Существует еще одна проблема с хранимой процедурой RowCount. Для достижения обобщения в инструкции ORDER BY допускается использование только одного столбца. Это также проблема метода по возрастанию-убыванию и метода курсора, хотя они могут сортировать несколько столбцов. необходимо обеспечить наличие только одного поля в первичном ключе. Я думаю, эту проблему можно решить с помощью более динамичного SQL, но, на мой взгляд, оно того не стоит. Такие ситуации возможны, но случаются они не очень часто. Обычно вы можете использовать приведенные выше принципы для независимого разбиения хранимых процедур на страницы.

Тестирование производительности

. При тестировании я использовал четыре метода. Если у вас есть метод получше, мне будет интересно узнать. В любом случае мне нужно сравнить эти методы и оценить их эффективность. Прежде всего, моя первая идея — написать тестовое приложение asp.net, содержащее DataGrid подкачки, а затем протестировать результаты страницы. Конечно, это не отражает истинное время ответа хранимой процедуры, поэтому больше подходит консольное приложение. Я также включил веб-приложение, но не для тестирования производительности, а в качестве примера совместной работы DataGrid с пользовательской нумерацией страниц и хранимыми процедурами.

В тесте я использовал автоматически созданную большую таблицу данных и вставил около 500 000 фрагментов данных. Если у вас нет такой таблицы для экспериментов, вы можете нажать здесь, чтобы загрузить дизайн таблицы и сценарий хранимой процедуры для генерации данных. Вместо использования столбца первичного ключа с автоматическим приращением я использовал уникальный идентификатор для идентификации записи. Если я использую упомянутый выше сценарий, вы можете рассмотреть возможность добавления столбца с автоматическим приращением после создания таблицы. Данные с автоматическим приращением будут отсортированы по числам на основе первичного ключа. Это также означает, что вы собираетесь использовать хранимую процедуру с разбиением на страницы. с сортировкой по первичному ключу для получения данных текущей страницы.

Чтобы реализовать тест производительности, я вызвал определенную хранимую процедуру несколько раз в цикле, а затем вычислил среднее время ответа. Учитывая причины кэширования, чтобы более точно смоделировать реальную ситуацию - время, необходимое одной и той же странице для получения данных для нескольких вызовов хранимой процедуры, обычно не подходит для оценки. номер страницы, запрашиваемый для каждого вызова, должен быть случайным. Конечно, надо предположить, что количество страниц фиксировано, 10-20 страниц, и данные с разными номерами страниц можно получать много раз, но случайным образом.

Мы можем легко заметить одну вещь: время ответа определяется расстоянием между данными страницы, которые необходимо получить, относительно начальной позиции набора результатов. Чем дальше от начальной позиции набора результатов, тем больше записей будет. пропущено. Это также причина, по которой я не включаю 20 лучших в свою случайную последовательность. В качестве альтернативы я бы использовал 2^n страниц, а размер цикла равен количеству необходимых различных страниц * 1000, поэтому каждая страница извлекается почти 1000 раз (определенно будет отклонение по случайным причинам).

Результаты

здесь результаты моего теста:






Заключение

Тесты проводились в порядке от лучшего к худшему — количество строк, курсор, по возрастанию-убыванию, подзапрос. Одна интересная вещь заключается в том, что обычно люди редко посещают страницы после первых пяти страниц, поэтому в этом случае вам может подойти метод подзапроса, в зависимости от размера вашего набора результатов и того, насколько далеко он находится. Для прогнозирования частоты появления страниц. , вы также, вероятно, будете использовать комбинацию этих методов. Если бы это был я, я бы в любом случае предпочел метод подсчета строк, он работает довольно хорошо, даже для первой страницы, «любой случай» здесь представляет некоторые случаи, когда обобщение затруднено. В этом случае я бы использовал курсор. (Я бы, вероятно, использовал метод подзапроса для первых двух и метод курсора после этого)