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

Откуда: UK
Сообщений: 523
Всем привет.

Имеется таблица с сессиями пользователей: Table (LoginID bigint, LoginTime datetime2, LogoutTime datetime2)
Таблица большая, порядка 10 млн записей в день.
В основном используется в ETL и все джойны к ней по LoginID. Так же пользователи хотят иметь возможность делать запросы по LoginTime.

Думаю делать ее партицированной по LoginTime, что решит вопрос как запросами пользователей, так и упрощает maintenance (Например, если потребуется архивировать/удалить старые сессии).

CREATE TABLE Table1
(
  LoginID bigint not null,
  LoginTime datetime2 not null,
  LogoutTime datetime2 not null,
  contraint PK_Table1 primary key (LoginID, LoginTime)
  ON Partition_schema(LoginTime)
) ON Partition_schema(LoginTime)


При таком подходе для запросов только по LoginTime отлично отрабатывает partition elimination и делается скан только необходимых партиций. При джойне на маленькие таблицы по LoginID тоже все отлично, clustered index seek. Однако при джойне на таблицы, в которых больше чем пара сотен строк, вместо желаемого seek'a идет scan всех партиций.

Пока вижу только вариант делать ее не партицированной с отдельным индексом по LoginTime, но может быть есть еще какие-то варианты?
15 фев 17, 21:01    [20215504]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
лолл
Member

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

"Однако при джойне на таблицы, в которых больше чем пара сотен строк" - это что значит? По каким полям соединение идет?
Я предположу, что для различных запросов могут потребоваться два индекса (один из них кластерный) - LoginID, LoginTime и LoginTime, LoginID
16 фев 17, 10:19    [20216642]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Remind,

Вообще партицирование поможет только удалению/пеоемещению, для чтения хорошо если останетесь на прежнем уровне. А если у вас не 24/7 то своё обслуживание можете и так проводить
16 фев 17, 10:44    [20216775]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
Remind
Member

Откуда: UK
Сообщений: 523
лолл
Remind,

"Однако при джойне на таблицы, в которых больше чем пара сотен строк" - это что значит? По каким полям соединение идет?
Я предположу, что для различных запросов могут потребоваться два индекса (один из них кластерный) - LoginID, LoginTime и LoginTime, LoginID


Соединение идет только по полю LoginID. Как видно из примера кластерный индекс по LoginID, LoginTime есть (PK), но оптимизатор решает использовать скан (хотя в таблице сессий в 1000+ раз больше строк чем в той, с которой ее джойню).
16 фев 17, 13:32    [20217586]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
iljy
Member

Откуда:
Сообщений: 8711
Remind
лолл
Remind,

"Однако при джойне на таблицы, в которых больше чем пара сотен строк" - это что значит? По каким полям соединение идет?
Я предположу, что для различных запросов могут потребоваться два индекса (один из них кластерный) - LoginID, LoginTime и LoginTime, LoginID


Соединение идет только по полю LoginID. Как видно из примера кластерный индекс по LoginID, LoginTime есть (PK), но оптимизатор решает использовать скан (хотя в таблице сессий в 1000+ раз больше строк чем в той, с которой ее джойню).


План покажите.
16 фев 17, 13:55    [20217702]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
так там скан по партиции а не по всей таблице посмотрите в плане
16 фев 17, 13:59    [20217720]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
Remind
Member

Откуда: UK
Сообщений: 523
TaPaK,

Scan/seek идет по всей таблице. Но это и понятно, SQL Server не знает в какой партиции лежит конкретный LoginID. Проблема в том что кол-во операций, которое делает сервер чтобы найти этот LoginID в партицированной (по дате) таблице больше, чем в непартицированной. Т.к. если я правильно понимаю в данном случае он делает отдельный "маленький" seek в каждой из партиций, а если этих партиций 1000, то это практически в 1000 раз больше операций, чем seek одного индекса, в котором на несколько уровней больше.
16 фев 17, 17:16    [20218704]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Понятно что для партицированной таблицы поиск будет использовать параллелизм, но тем не менее кол-во операций получается настолько большим, что для того чтобы использовать seek вместо scan'a кол-во строк должно быть < 0,05% (и я так понимаю чем больше партиций, тем это значение будет меньше), и при этом такой же seek на непартицированной таблице будет в разы быстрее.
16 фев 17, 17:23    [20218749]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Remind,

дайте уже план, а то маленький seek, большой seek...
и если у вас эта таблица справа в соединении то как иначе как не думать о всех секциях, пробуйте статистику повесить если нет, RECOMPILE на запрос
16 фев 17, 17:30    [20218789]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
Remind
Member

Откуда: UK
Сообщений: 523
TaPaK,

Пожалуйста.

К сообщению приложен файл (partitioned.sqlplan - 40Kb) cкачать
16 фев 17, 17:44    [20218842]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
Remind
Member

Откуда: UK
Сообщений: 523
И аналогичный запрос к непартицированной таблице.

К сообщению приложен файл (nonpartitioned.sqlplan - 37Kb) cкачать
16 фев 17, 17:45    [20218846]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Remind,

т.е. секционируем по дате а соединяем по Id и ждём чуда... LoginTime же есть там, почему не включить в запрос? :) Секционирование не решает вопросы скорости, а только администрирования.
16 фев 17, 18:02    [20218905]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
Владислав Колосов
Member

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

ну как не решает, зачем так категорично, сканирование меньших объемов сокращает время запроса.
Бывают ситуации, когда выгоднее не строить 100500 индексов, а разбить на секции и обходиться просто сканами секций.

автор
Соединение идет только по полю LoginID

а даты, по которым разбиты секции, где? Не забывайте, что типы данных (времени) должны иметь строгое соответствие или быть явно приведены к типу данных секционирования.
16 фев 17, 18:41    [20219021]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
Remind
Member

Откуда: UK
Сообщений: 523
Конкретно в данном запросе можно конечно добавить условие по LoginTime.
Однако при загрузке фактов мы не знаем время начала сессии. Мы можем сканировать только сессии, которые начались не старше месяца назад, т.к. 99,9% сессий не живет больше месяца, но таким образом мы теряем этот самый 0,01% и при этом всегда сканируем целый месяц, даже когда все сессии начались в этот же день.
16 фев 17, 18:51    [20219041]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Remind,

так чего вы тогда ждёте? если вы говорите месяц то добавьте условия если не передали (@Date IS NULL AND LoginTime BETWEEN startMonth AND EOM) OR ваши условия или - месяц иначе вы всегда получите поиск по всем секциям
16 фев 17, 20:20    [20219271]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
TaPaK
Member

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

автор
ну как не решает, зачем так категорично, сканирование меньших объемов сокращает время запроса.
Бывают ситуации, когда выгоднее не строить 100500 индексов, а разбить на секции и обходиться просто сканами секций.

при высокой селективности это будет выгодно
16 фев 17, 20:21    [20219273]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
лолл
Member

Откуда:
Сообщений: 450
Интересно, каково было бы быстродействие, если переписать механизм через темпоральные талицы + in-memory...
17 фев 17, 10:04    [20220154]     Ответить | Цитировать Сообщить модератору
 Re: Помогите спроектировать таблицу  [new]
aleksrov
Member

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

Ну не все так просто, в 2014 у in memory туча ограничений, в 2016 уже получше (добавили check, unique, foreign между in memory таблицами и т.д.), но с учетом того что к таблице будут обращаться через обычный T-SQL, т.е. interpreted mode, а не через Natively Compiled Stored Procedures то прирост может быть не таким уж и огромным, как говорится надо разбирать каждый случай индивидуально.
17 фев 17, 10:22    [20220206]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить