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

Откуда:
Сообщений: 22
Добрый день.
У меня возникла следующая проблема.
Имеется таблица, скажем, событий, примерно такого вида:

CREATE TABLE Events (
    EventID INT PRIMARY KEY IDENTITY (1, 1) NOT NULL,
    EventDate DATETIME PRIMARY KEY NOT NULL,
    EventText VARCHAR(255) NOT NULL
)

И вот записей в этой самой таблице накопилось уже довольно много, причем скорость роста также увеличивается. Запросы работают все медленнее, несмотря на все индексы.
К таблице в основном обращаются два вида запросов:

SELECT * FROM Events WHERE EventID = xxx
SELECT * FROM Events WHERE EventDate BETWEEN xxx AND yyy

Появилось желание сделать partitioned view. То есть время от времени отрезать старые куски от данной таблицы и делать отдельные архивные таблицы. Предположим, что EventID и EventDate растут равномерно, то есть в архивной таблице получится повесить CHECK на оба этих поля. В BOL сказано, что partitioned column должен быть частью PK. Делаем PK на оба поля. И все равно, при указанных селектах в execution plan присутствует clustered index scan по обоим таблицам. Хотя в представление получается сделать INSERT, что вроде бы свидетельствует о том, что оно работает как partitioned.

Приветствуются любые соображения, что я делал неправильно и как это вообще можно было бы делать.
1 ноя 05, 11:56    [2026509]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Я бы по дате секционировал и все
1 ноя 05, 12:07    [2026579]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
aleks2
Guest
А я бы сделал
кластерный индекс по (EventDate, EventID)
и не парился бы с partitioned view.
1 ноя 05, 12:32    [2026683]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
Crimean
Member

Откуда:
Сообщений: 13148
2 aleks2
А я бы сделал
кластерный индекс по (EventDate, EventID)
и не парился бы с partitioned view.


не глядя на запросы? ну монстр просто!
а если там агрегаты по хитрым условиям?
типа первое / последнее событие нужного типа за диапазон дат?
а индексы уже в память не лезут?
ИМХО только секционирование...
А дата - весьма неплохой и достаточно беспроблемный критерий
1 ноя 05, 13:28    [2026986]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
Crimean
Я бы по дате секционировал и все
А чисто EventDate позволит создать Primary Key?
1 ноя 05, 14:01    [2027159]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
Crimean
Member

Откуда:
Сообщений: 13148
> А чисто EventDate позволит создать Primary Key

а если нет , то что?

BOL, Partitioning Column Rules
Partitioning columns must be a part of the primary key


если даже существующий ПК этому противоречит , то меняем его на UNIQUE , делаем ПК , частью которого есть EventDate и - далее по тексту
но , сдается мне , кластерный индекс на EventDate может пойти на пользу... не факт , конечно , что пойдет :)
запросы смотреть надо - в который раз повторяю ... все от них зависит .
эффективность секционирования - тоже
1 ноя 05, 14:20    [2027289]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
Crimean
а если нет , то что?
А то, что для эффективной выборки при использовании partitioned view нужно в условии where, например, указывать все столбцы из primary key.
1 ноя 05, 14:32    [2027362]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
Crimean
Member

Откуда:
Сообщений: 13148
> А то, что для эффективной выборки при использовании partitioned view нужно в условии where, например, указывать все столбцы из primary key

позволю себе не согласиться с вышепроцитированным - достаточно указать только Partitioning Column для работы всей схемы с секционированием
хотя , безусловно , никакого вреда , кроме пользы , от указания ВСЕХ полей primary key в каждом запросе не будет , но , боюсь , такая ситуация малореальна в жизни . хотя , конечно , если primary key у нас clustered или сделан как покрывающий индекс - то почему нет?
1 ноя 05, 14:43    [2027424]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
veil_of_maya
Member

Откуда:
Сообщений: 22
Только EventDate использовать в качестве PK, к сожалению не получится. Это значение вполне себе может дублироваться.

Сейчас вырисовывается такая схема:
Критерий секционирования - EventDate
PK - EventDate+EventID

Встает новый вопрос - как быть с генерацией EventID, который в исходной таблице был IDENTITY. Видимо, нужно сделать INSTEAD OF триггер, который будет подставлять это поле, соблюдая уникальность среди всех таблиц ? Хотя без триггера не обойтись все равно - ибо иначе придется модифицировать запросы, вставляющие данные, что есть геморрой.
1 ноя 05, 14:49    [2027459]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Нужно делать так:
1. создаеш PK составной, который включает в себя первое поле Date и все последующие поля которые у тебя уникально идентифицируют запись.
Это индекс необходим только для принятия решения о том - какую партицию задействовать при выборке, и больше ничего. И обеспечивает избыточную уникальность, что бы инсерты не падали.

2. Создаеш UQ кластерный индекс по которому и будут осуществляться основные выборки и join. И уникальность обеспечивается в нужном объеме.

Соответственно и обновляемым это представление то же можно сделать, без никаких хитрых и никому ненужных INSTEAD OF триггеров.

to aleks2 - IT монстр.
1 ноя 05, 15:08    [2027564]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
aleks2
Guest
aleksey_fomchenko


to aleks2 - IT монстр.


Монстр - не монстр, а попробуйте...
-------------------
ибо шевелить руками надо с умом.
1 ноя 05, 15:15    [2027591]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
to - Монстр, если у тебя 100.000.000 таблица, то идея простого кластерного индекса по указанып полям уже не греет.
Тем более, что селективность у первого указанного тобой поля (EventDate) очень мала.
Вывод: абсурд строить кластерный индекс по EventDate, EventID.

П.С.
Конечно и на запросы для начала надо посмотреть.
1 ноя 05, 15:19    [2027623]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
veil_of_maya
Member

Откуда:
Сообщений: 22
aleksey_fomchenko

2. Создаеш UQ кластерный индекс по которому и будут осуществляться основные выборки и join. И уникальность обеспечивается в нужном объеме.

Соответственно и обновляемым это представление то же можно сделать, без никаких хитрых и никому ненужных INSTEAD OF триггеров.


Не разрешает MS делать никаких индексов на partitioned view. Видимо, уникальность предлагается обеспечивать с помощью CHECK().

К тому же, раз нет индекса на вью, то если сервер решит прочесать все партиции, он пройдет отдельно по каждому индексу для каждой таблицы, а потом будет данные слеплять вместе, что будет не шибко быстро. Я правильно понимаю ?
1 ноя 05, 17:55    [2028492]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Итого

Простой вариант:

ПК CLUSTERED по EventID был , он сносится и вместо него делается UNIQUE CLUSTERED
ПК NONCLUSTERED делается как EventDate, EventID
Рассматривается вариант сделать кластерный таки по EventDate, EventID
По EventDate делаются CHECK - секционирование идет по нему
EventID остается IDENTITY
Вставка направляется ТОЛЬКО в последнюю секцию таблицы

Параноидальный вариант:

делается таблица AllEvents
в ней EventID INT PRIMARY KEY IDENTITY (1, 1) NOT NULL
А с таблиц (ВСЕХ!) Events_* на нее ФК вешается
Что не мешает в Events_* сделать EventID UNIQUE
Ссылки, которые были на Events_* переносятся на AllEvents, то есть и DRI не страдает
При БОЛЬШОМ желании в AllEvents делается поле "секция", которое описывает местоположение секции - пригодится, когда секции выкините на другой сервер.
1 ноя 05, 18:49    [2028689]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
to Crimean
Строить паспределенные секционированные обновляемые представления с IDENTITY нельзя.

to veil_of_maya
строить индексы надо не по View а по исходным таблицам.

Так фишка же в том, что бы иметь обновляемое распределенное представление
и серез него все таблицы сами будут обновляться!!!
Что вы пелосипед изобретаете?
1 ноя 05, 21:18    [2028931]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
toc99
Guest
советую попобовать добавить в таблицу два индекса покрывающих два ваших запроса (index covered query)

1. EventId, EventDate, EventText
2. EventDate, EventId, EventText

+ должно ускорить селекты
- точно не ускорит инсёрты
+ не надо никаких вьюх
! желательно использовать ХП

sql mcp
2 ноя 05, 00:51    [2029145]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
aleks2
Guest
toc99
советую попобовать добавить в таблицу два индекса покрывающих два ваших запроса (index covered query)

1. EventId, EventDate, EventText
2. EventDate, EventId, EventText

+ должно ускорить селекты
- точно не ускорит инсёрты
+ не надо никаких вьюх
! желательно использовать ХП

sql mcp


Полку монстров прибыло! Приветствую тебя, брат по разуму, тьфу.... по отсутствию оного.
---------------------------
Ибо здешние корифеи говорят: нихрена индексами не добъешся.
2 ноя 05, 06:49    [2029262]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
aleksey_fomchenko
Member

Откуда: Москва
Сообщений: 1014
Индексы хорошы, но есть и физические пределы, например когда база выгребает за 100GB и в ближайшие 3 года ожидаем прирост до 1TB.

При сложных запросах связывания оптимизатор выдает очень глупые планы.
Итог - секционируем.
2 ноя 05, 11:08    [2029946]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
toc99
Guest
Очень интересно чтож это за база такая!

Яндекс свой пишете, АБС или логи сайта?
Поделитесь пожалуйста, если не секрет.
2 ноя 05, 12:14    [2030380]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с partitioned view  [new]
WiRuc
Member

Откуда: Воронеж
Сообщений: 1280
Aleks2 не так уж неправ. Секционированием тут дело особо не улучшить, а вот кластерный индекс должен помочь. Для тех двух запросов должно хватить кластерного индекса по EventDate+EventId и некластерного по EventID.
2 ноя 05, 12:44    [2030555]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить