Выбор способа оптимизации запроса

← →
Александр Иванов © (2008-12-16 16:40) [0]

Добрый день!

Есть БД в MS SQL Server 2005. Памяти на сервере 16 Гб, 2 четырехядерных ЦПУ.
В БД таблица с 10-15 млн. записей.
По ней выполняется запрос по выборке 50 записей с пейджингом.
Алгоритм выборки примерно следующий:

ALTER PROCEDURE [dbo].[GetAllByPage]
@OnPage int,
@PageNumber int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM (
SELECT TOP (@OnPage * @PageNumber) *
FROM Table) AS T1
ORDER BY Price)
AS PTable
WHERE PTable.ID NOT IN
(SELECT TOP (@OnPage * (@PageNumber - 1)) ID FROM Table) AS T2
ORDER BY Price)
END

Кроме того существуют условия отбора по полям этой таблицы, которые могут отсутствовать, т.е. фильтры.
Если указаны все фильтры, то запрос выполняется примерно за 0,5 - 0.8 сек, если не указаны, то скорость доходит до 30 секунд, наверно и больше - 30 секунд таймаут.
Прогонял Tuning Advisor он мне сказал, что индексы уже не помогут увеличить скорость.
Кластерный индекс в таблице по полю Price.
Как можно увеличить быстродействие?
Рассматриваю все варианты, включая перепроектирование таблицы.

← →
clickmaker © (2008-12-16 16:42) [1]

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

← →
Александр Иванов © (2008-12-16 16:45) [2]

Нереально практически. Запросы очень сильно отличаются.

← →
tesseract © (2008-12-16 16:48) [3]

VIEW попробуй приложить, exists возможно поможет, попробуй в ключах всё-таки использовать фильтры, а то у тебя индексы явно не задействованы- например перебором. На dbf базах такой трюк срабатывает.

← →
Александр Иванов © (2008-12-16 16:50) [4]

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

← →
Ega23 © (2008-12-16 16:57) [5]

А данные в таблице часто обновляются?

← →
Правильный$Вася (2008-12-16 17:00) [6]

> Если указаны все фильтры, то запрос выполняется примерно
> за 0,5 - 0.8 сек, если не указаны, то скорость доходит до 30 секунд

если нет ограничений выбора, то тормоза во многом не от того, что сервер не справляется, а от того, что все эти миллионы нужно на клиента запхать (по сети + память выделить + ...)

← →
Bless © (2008-12-16 17:21) [7]

хотелось бы глянуть на план выполнения.

← →
clickmaker © (2008-12-16 17:44) [8]

> [4] Александр Иванов © (16.12.08 16:50)

подгружаешь страницы по мере открытия, но при возврате, н-р, к первой берешь ее из кэша.
Правда, кэш имеет свойство протухать. Но тут, как всегда: компромисс между актуальностью данных и быстродействием

← →
Skyle © (2008-12-17 06:57) [9]

А может подойдёт какая-нибудь конструкция вида

DECLARE @Ids TABLE (ID INT, Price money) INSERT @Ids SELECT TOP (@OnPage * @PageNumber) Id, Price FROM Table ORDER BY Price SELECT T.* FROM (SELECT TOP (@OnPage) Id, Price FROM @Ids ORDER BY Price DESC) Q INNER LOOP JOIN Table T ON T.ID = Q.Id

Проверял на таблице с миллионом записей. Основные тормоза, конечно же, на сортировке, так как индекса по полю Price нет.
Вызов
exec TTT, 10, 1 exec TTT, 10, 2

отработал за три секунды.

← →
Карелин Артем (2008-12-17 08:55) [10]

> Проверял на таблице с миллионом записей. Основные тормоза,
> конечно же, на сортировке, так как индекса по полю Price
> нет.

Ну задал бы индекс. В переменных типа таблица они при обьявлении заводятся.

← →
Skyle © (2008-12-17 09:27) [11]

> Карелин Артем (17.12.08 08:55) [10]

А мне-то зачем индекс? :)

← →
sniknik © (2008-12-17 10:23) [12]

> По ней выполняется запрос по выборке 50 записей с пейджингом.
использовать для условия отбора нумерацию записей в выборке (нумерация есть в 2005м), и выбирать только необходимые 50 записей (т.е. простой запрос с условием > MinPageRecNo and < MaxPageRecNo)...
и без всяких TOP и обрезаний с начала/конца практически всех записей (нафига их запросу тогда выбирать, если они "отрежутся"? чисто для солидности?)

← →
NkzAlex (2008-12-17 14:36) [13]

Запрос с точки зрения производительности не оптимизирован, отсюда и тормоза. Особенно конструкция вида:
PTable.ID NOT IN (SELECT TOP (@OnPage * (@PageNumber - 1)) ID FROM Table)
От нее избавляться однозначно.

> А данные в таблице часто обновляются?

Обновлением можно пренебречь. Т.е. обновляются часто, но актуальность не нужна, если будет за последние пару часов - это отличный результат, за последний день - неплохой.

> подгружаешь страницы по мере открытия, но при возврате,
> н-р, к первой берешь ее из кэша.Правда, кэш имеет свойство
> протухать. Но тут, как всегда: компромисс между актуальностью
> данных и быстродействием

Да, идею понял, спасибо. Избавит сложного запроса. Актуальность не важна.

> использовать для условия отбора нумерацию записей в выборке
> (нумерация есть в 2005м), и выбирать только необходимые
> 50 записей (т.е. простой запрос с условием > MinPageRecNo
> and < MaxPageRecNo)... и без всяких TOP и обрезаний с начала/конца
> практически всех записей (нафига их запросу тогда выбирать,
> если они "отрежутся"? чисто для солидности?)

А можно ссылку?

> А можно ссылку?

http://msdn.microsoft.com/ru-ru/library/ms186734.aspx

Конкретно для 2005-го:
http://msdn.microsoft.com/ru-ru/library/ms186734(SQL.90).aspx

> Конкретно для 2005-го:http://msdn.microsoft.com/ru-ru/library/ms186734(SQL.
> 90).aspx

Спасибо

Выбор способа оптимизации запроса Найти похожие ветки