T-sql: как выполнить оптимизированную Подкачку страниц?
Я написал следующий код, он хорошо работает, но он берет как 3 секунды, чтобы завершиться, если таблица содержит миллион записей. Есть ли способ оптимизировать следующий код.
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DECLARE @Page_Size int;
DECLARE @Page_Number int;
DECLARE @Lower_Bound int;
DECLARE @Upper_Bound int;
SET @Page_Size = 30;
SET @Page_Number = 30000;
SET @Lower_Bound = (@Page_Number - 1) * @Page_Size;
--SET @Upper_Bound = @Page_Number * @Page_Size;
WITH Customers AS--(Row_Numbr, Record_Id, First_Name,
Middle_Name, Last_Name, Email, Telephone) AS
(
SELECT ROW_NUMBER()
OVER
(ORDER BY Account.Customer.Record_Id) AS Row_Numbr, *
FROM Account.Customer
)
SELECT top(@Page_Size) *
FROM Customers
WHERE Row_Numbr > @Lower_Bound--
AND Row_Numbr <= @Upper_Bound -- This is suppose to be faster
--SELECT * FROM Customers
--WHERE Row_Numbr > @Lower_Bound
-- AND Row_Numbr <= @Upper_Bound
2 ответа
Во-первых, почему DBCC DROPCLEANBUFFERS; ? Это аппаратный холодный сброс буферного пула. Если вы не хотите измерять и настраивать производительность ввода-вывода своих жестких дисков, никого не волнует производительность холодного кеша. Это , а не , как ваша система будет работать. Кэширование страниц в буферном пуле - наиболее критический аспект производительности в базах данных, и вы его уберете. Это все равно, что приехать в Ferrari без двигателя и спросить, почему он такой медленный. Для измерения производительности вы должны сделать точно напротив : выполнить запрос 4-5 раз, чтобы прогреть кеш, затем измерить.
Во-вторых, какова ваша структура таблицы? Порядок индекса кластера таблицы Account.Customer равен Record_id ? Если нет, вы никогда не получите желаемой производительности, как бы вы ни выражали свой T-SQL.
И последнее, но не менее важное: какая у вас система? Достаточно ли оперативной памяти для кэширования всей базы данных в памяти? Если нет, купите больше оперативной памяти. Существуют ли другие процессы, конкурирующие за память, например IIS / Asp? Если да, выгоните их на их собственный сервер, вам не следует когда-либо запускать базу данных на том же хосте, что и веб-сервер , если производительность важна.
В качестве альтернативы быстрой подкачки рассмотрите решения, управляемые набором ключей:
/* moving up */
SELECT top(@Page_Size) *
FROM Account.Customer
WHERE Record_Id > @lastPageRecordId
ORDER BY Record_Id;
/* moving down */
SELECT top(@Page_Size) *
FROM Account.Customer
WHERE Record_Id < @firstPageRecordId
ORDER BY Record_Id DESC;
Решение, управляемое набором ключей, может искать прямо до последней позиции, а затем диапазон просматривает следующую / предыдущую страницу, используя позицию ключа кластеризованного индекса. Логика (состояние) разбиения на страницы должна запоминать последний и первый ключи на отображаемой странице, чтобы продолжить оттуда, вместо запоминания номера страницы.
Решения на основе Rowcount (а также LIMIT в MySQL) менее эффективны, чем решения на основе набора ключей, потому что они всегда должны подсчитывать записи, чтобы позиционировать себя, вместо того, чтобы искать прямо в позиции, как это могут сделать наборы ключей.
Я использую эту хранимую процедуру :
CREATE PROCEDURE sp_PagedItems
(
@Page int,
@RecsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
Name varchar(50),
Price currency
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF