Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос для пагинации (время выполнения и использованная память)  [new]
FRoST86
Member

Откуда:
Сообщений: 121
В таблице Table1 порядка 400000 записей.
запрос представленный ниже выполняется порядка 2 секунд и дает прирост используемой памяти порядка 0,03 ГБ
declare @ExternalSystemId int = 0;
select Table1.Id
       ,row_number() over(ORDER BY Table1.CreateDate desc) AS RowNum
from Table1
join ExternalSystems on ExternalSystems.Id = Table1.ExternalSystemId
where ExternalSystemId = @ExternalSystemId


следующий запрос использует предыдущий как вложенный во фразе from, при этом время выполнения увеличивается до 15 секунд, а используемая память до 2 ГБ.
declare @RowsFrom int = 1;
declare @RowsTo int = @RowsFrom + 10;
declare @ExternalSystemId int = 0;
select Id
from (select Table1.Id
             ,row_number() over(ORDER BY Table1.CreateDate desc) AS RowNum
        from Table1
        join ExternalSystems on ExternalSystems.Id = Table1.ExternalSystemId
        where ExternalSystemId = @ExternalSystemId) as AllRows (Id, RowNum)
where RowNum >= @RowsFrom and RowNum < @RowsTo

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

приведенный выше запрос планируется использовать для разбиения на страницы всех данных, через join по выбранному Id.
2 апр 14, 11:35    [15820157]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
Glory
Member

Откуда:
Сообщений: 104751
FRoST86
подскажите из-за чего это происходит?

Разные запросы выполняются по-разному
Это можно увидеть в планах выполнения
2 апр 14, 11:41    [15820189]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
FRoST86
Member

Откуда:
Сообщений: 121
Glory,

Посмотрел план запроса, в нем 99% общей стоимости запроса занимает key lookup.
Посмотрел, что борются с этим добавлением, покрывающего все поля запроса, индекса. Но я ведь не могу добавить в индекс поле row_number(). Или я что то не так понял?

К сообщению приложен файл. Размер - 26Kb
2 апр 14, 13:10    [15820970]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
Glory
Member

Откуда:
Сообщений: 104751
FRoST86
Но я ведь не могу добавить в индекс поле row_number().Или я что то не так понял?

Ага. Как может быть key lookup для поля, которого нет в таблице ?
2 апр 14, 13:17    [15821033]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
FRoST86,

Попробуйте создать на таблице Table1, в плане у вас она именуется как EspedValidationLog, такой индекс (если id ключ кластерного, то уберите include (Id)):
create index ix_ExternalSystemId_CreateDate on EspedValidationLog(ExternalSystemId, CreateDate desc) include (Id);
2 апр 14, 13:30    [15821120]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
Yagrus2
Member

Откуда: Москва
Сообщений: 902
FRoST86,
А как вы мониторите прирост памяти?
2 апр 14, 13:39    [15821183]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
FRoST86
Member

Откуда:
Сообщений: 121
Yagrus2
FRoST86,
А как вы мониторите прирост памяти?

Через Task Manager.
2 апр 14, 13:49    [15821240]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
FRoST86
Member

Откуда:
Сообщений: 121
SomewhereSomehow
FRoST86,

Попробуйте создать на таблице Table1, в плане у вас она именуется как EspedValidationLog, такой индекс (если id ключ кластерного, то уберите include (Id)):
create index ix_ExternalSystemId_CreateDate on EspedValidationLog(ExternalSystemId, CreateDate desc) include (Id);


Спасибо, помогло, теперь план выглядит иначе, нет прироста памяти и скорость выполнения очень высока.
Но есть один момент, поле CreateDate выбирается в этом запроса опционально, т.е. это может быть совсем не Createdate, а, например поле Name из таблицы ExternalSystems или другие поля из таблицы EspedValidationLog.
Как следует поступить с индексами при таких условиях, добавить все возможные поля, доступные для сортировки в индекс?

К сообщению приложен файл. Размер - 19Kb
2 апр 14, 14:10    [15821384]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
FRoST86,

Основным тормозом, скорее всего, был Index Scan на внутренней стороне Nested Loops. Скан был, т.к. не было индекса, в котором поле ExternalSystemId (условие соединения) стояло бы первым и позволило бы выполнить поиск по внутренней таблице.

Включение поля CreateDate в индекс имело две цели – первая, избежать явной сортировки. Вторая избежать Lookup в кластерный индекс - тем самым уменьшив стоимость этой операции поиска и повысив вероятность использования поиска, против сканирования.

Если условий критериев сортировки может быть несколько, то я бы постарался определить, 1-2 наиболее часто используемых, и для них сделал бы 1-2 «идеальных» индекса (отдельных, т.к. нет смысла во избежание сортировки включать два поля подряд, если только нет сортировки по двум полям подряд).

Для остальных можно сделать один покрывающий индекс, с ключом ExternalSystemId, при помощи директивы include (либо, сделать кластерный индекс по такому же ключу, уже без include). Тогда у вас сохраниться возможность поиска по ExternalSystemId и индекс останется покрывающим. Правда, добавится сортировка, но для редких вызовов и небольшого объема данных (а их, скорее всего, не сильно много должно быть по одному значению ExternalSystemId), должно тоже работать быстро.

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

Если же критерии сортировки, а главное, критерии поиска (например, поиск не только по @ExternalSystemId) могут быть абсолютно любыми, то нужно либо попытаться определить набор индексов, который бы покрывал все условия. Либо оптимизировать саму бизнес-логику поиска.
2 апр 14, 14:39    [15821659]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
FRoST86
Member

Откуда:
Сообщений: 121
SomewhereSomehow,

Спасибо большое, вы дали пишу для размышления.
Мне пока слабо представляется как можно унифицировать индексы для сортировки по различным полям, при том что полей сортировки действительно может быть несколько
Кроме того join может быть еще к одной таблице, как отдельно так и вместе с ExternalSystems.

Если есть еще какие-нибудь советы, буду очень благодарен.
2 апр 14, 15:41    [15822134]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8353
Если дело только в сортировке, то клиент прекрасно с этим справится.
2 апр 14, 15:55    [15822257]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Владислав Колосов
Если дело только в сортировке, то клиент прекрасно с этим справится.

Тут на основе сортировки определяется набор выводимых данных а не их порядок.

FRoST86,
вот вам каноничные статейки на тему динамических фильтров и на тему paging:
Dynamic Search Conditions in T-SQL
Optimising Server-Side Paging - Part I
Думайте, экспериментируйте, глядишь чего путное и получится!
2 апр 14, 16:08    [15822366]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
FRoST86
Member

Откуда:
Сообщений: 121
SomewhereSomehow,

Еще раз, благодарю.
2 апр 14, 16:25    [15822528]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8353
Пейджинг можно выполнить и другими средствами, ORDER BY.... OFFSET.
2 апр 14, 17:20    [15822967]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
FRoST86
Member

Откуда:
Сообщений: 121
Владислав Колосов,

пробовал, эффект такой же.
2 апр 14, 17:42    [15823094]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Владислав Колосов
Пейджинг можно выполнить и другими средствами, ORDER BY.... OFFSET.
Это не другое средство, это другой синтаксис тех же средств (внутренних механизмов).
2 апр 14, 23:33    [15824405]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
aleks2
Guest
FRoST86
В таблице Table1 порядка 400000 записей.
запрос представленный ниже выполняется порядка 2 секунд и дает прирост используемой памяти порядка 0,03 ГБ
declare @ExternalSystemId int = 0;
select Table1.Id
       ,row_number() over(ORDER BY Table1.CreateDate desc) AS RowNum
from Table1
join ExternalSystems on ExternalSystems.Id = Table1.ExternalSystemId
where ExternalSystemId = @ExternalSystemId


может быть как то более грамотно переписать запрос?



Я один такой тупой или всеж...
Нахера тут JOIN?

Разве результат изменится?
declare @ExternalSystemId int = 0;
select Table1.Id
       ,row_number() over(ORDER BY Table1.CreateDate desc) AS RowNum
from Table1
--join ExternalSystems on ExternalSystems.Id = Table1.ExternalSystemId
where ExternalSystemId = @ExternalSystemId

Ну, т.е. он, канешно, может измениться в некоторых случаях, только, вангую, тут не тот случай.
3 апр 14, 06:58    [15824928]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для пагинации (время выполнения и использованная память)  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
aleks2
Я один такой тупой или всеж...
Нахера тут JOIN?


автор
...полей сортировки действительно может быть несколько
Кроме того join может быть еще к одной таблице, как отдельно так и вместе с ExternalSystems.


aleks2,
Я так понял, что у ТС проблема на самом деле шире, чем обозначена вначале. Проблема не конкретно в этом запросе. ТС нужно сделать постраничный вывод на, по-сути, динамическом запросе, по-этому я ссылку и привел на статью по динамическим условиям поиска.
3 апр 14, 08:04    [15824993]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить