Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Индексируем табличку из 3ех полей для любого запроса.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Таблица из трех полей с естественным ключом по всем полям.
Надо ее индексировать так, что бы при любом запросе (при четком указании полей) было максимально быстро.
И вот что у меня получилось

CREATE table Table1 (ref1 int, ref2 int, ref3 int, primary key clustered(ref1, ref2, ref3))
CREATE index IX_Table1_ref2 on Table1 (ref2, ref3)
CREATE index IX_Table1_ref3 on Table1 (ref3)


select * from Table1 where ref1=0
select * from Table1 where ref2=0
select * from Table1 where ref3=0

select * from Table1 where ref1=0 and ref2 = 0
select * from Table1 where ref1=0 and ref3 = 0
select * from Table1 where ref2=0 and ref3 = 0


Только таблица стала весить в 4 раза больше.
Может есть варианты получше (без columnstore)?

А еще если сделать вот так
CREATE table Table1 (ref1 int, ref2 int, ref3 int, primary key clustered(ref1, ref2, ref3))
CREATE table Table2 (ref2 int, ref3 int, ref1 int, primary key clustered(ref2, ref3, ref1))
CREATE table Table3 (ref3 int, ref1 int, ref2 int, primary key clustered(ref3, ref1, ref2))

select * from Table1 where ref1=0
select * from Table2 where ref2=0
select * from Table3 where ref3=0

select * from Table1 where ref1=0 and ref2 = 0
select * from Table3 where ref1=0 and ref3 = 0
select * from Table2 where ref2=0 and ref3 = 0

Ты мы экономим место(в 3 раза увеличится только, вместо 4ех), так еще и в скорости получаем прирост на некоторых запросах.
Но конечно с этим очень не удобно работать.
8 июн 17, 15:34    [20550478]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Rankatan
Member

Откуда:
Сообщений: 250
CREATE table Table1 (ref1 int, ref2 int, ref3 int, primary key clustered(ref1, ref2, ref3))
CREATE index IX_Table1_ref2 on Table1 (ref2, ref3)
CREATE index IX_Table1_ref3 on Table1 (ref3)

Когда индексы создаете то каждая запись индекса за собой тащит еще PK из главной таблицы. Может имеет смысл вот так сделать:

CREATE table Table1 (ref1 int, ref2 int, ref3 int)
CREATE index IX_Table1_ref1 on Table1 (ref1, ref2, ref3)
CREATE index IX_Table1_ref2 on Table1 (ref2, ref3)
CREATE index IX_Table1_ref3 on Table1 (ref3)
8 июн 17, 15:45    [20550525]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Rankatan
Member

Откуда:
Сообщений: 250
CREATE table Table1 (ref1 int, ref2 int, ref3 int)

CREATE clustered index IX_Table1_ref1 on Table1 (ref3)
CREATE index IX_Table1_ref2 on Table1 (ref1, ref2)
CREATE index IX_Table1_ref2 on Table1 (ref2)
8 июн 17, 15:57    [20550586]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Rankatan
Member

Откуда:
Сообщений: 250
CREATE table Table1 (ref1 int, ref2 int, ref3 int)
CREATE clustered index IX_Table1_ref1 on Table1 (ref3)
CREATE index IX_Table1_ref2 on Table1 (ref1, ref2)
CREATE index IX_Table1_ref2 on Table1 (ref2)

+ если возможно нужно изменить CREATE index на CREATE UNIQUE index тогда должно меньше места занимать.
8 июн 17, 15:58    [20550600]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Rankatan
CREATE table Table1 (ref1 int, ref2 int, ref3 int, primary key clustered(ref1, ref2, ref3))
CREATE index IX_Table1_ref2 on Table1 (ref2, ref3)
CREATE index IX_Table1_ref3 on Table1 (ref3)

Когда индексы создаете то каждая запись индекса за собой тащит еще PK из главной таблицы.
Да, я так и посчитал. Размер потому и получился в 12 полей int.


Rankatan
CREATE table Table1 (ref1 int, ref2 int, ref3 int)
CREATE index IX_Table1_ref1 on Table1 (ref1, ref2, ref3)
CREATE index IX_Table1_ref2 on Table1 (ref2, ref3)
CREATE index IX_Table1_ref3 on Table1 (ref3)
Если так, то получается еще больше
CREATE table Table1 (ref1 int, ref2 int, ref3 int) -- 3 +1 (псевдо id)
CREATE index IX_Table1_ref1 on Table1 (ref1, ref2, ref3) --3 +1
CREATE index IX_Table1_ref2 on Table1 (ref2, ref3) --2+1
CREATE index IX_Table1_ref3 on Table1 (ref3, ref1) --2+1

Итого 4+4+3+3=14
И еще мы получаем с индека 2 и 3 лукап на таблицу, что совсем плохо.
8 июн 17, 16:00    [20550606]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
CREATE clustered index IX_Table1_ref1 on Table1 (ref3)
Кластерный всегда уникальный. Нужно быть уверенным что ref3 уникально. Из задачи этого не следует.
8 июн 17, 16:02    [20550616]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Rankatan
Member

Откуда:
Сообщений: 250
LSV
CREATE clustered index IX_Table1_ref1 on Table1 (ref3)
Кластерный всегда уникальный. Нужно быть уверенным что ref3 уникально. Из задачи этого не следует.

Нет, это PRIMARY KEY уникальный. Если ключ кластерного индекса не уникален сервер делает его уникальным (добавляет еще один невидимый столбец по сути)
8 июн 17, 16:05    [20550632]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Rankatan
CREATE table Table1 (ref1 int, ref2 int, ref3 int)

CREATE clustered index IX_Table1_ref1 on Table1 (ref3)
CREATE index IX_Table1_ref2 on Table1 (ref1, ref2)
CREATE index IX_Table1_ref2 on Table1 (ref2)

CREATE table Table1 (ref1 int, ref2 int, ref3 int) --0

CREATE clustered index IX_Table1_ref1 on Table1 (ref3)--3 + 1 (псевдо id)
CREATE index IX_Table1_ref2 on Table1 (ref1, ref2) --2 +2
CREATE index IX_Table1_ref2 on Table1 (ref2) --1+2

Получается 11.
8 июн 17, 16:06    [20550638]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Rankatan
Member

Откуда:
Сообщений: 250
Deff
Rankatan
CREATE table Table1 (ref1 int, ref2 int, ref3 int)

CREATE clustered index IX_Table1_ref1 on Table1 (ref3)
CREATE index IX_Table1_ref2 on Table1 (ref1, ref2)
CREATE index IX_Table1_ref2 on Table1 (ref2)

CREATE table Table1 (ref1 int, ref2 int, ref3 int) --0

CREATE clustered index IX_Table1_ref1 on Table1 (ref3)--3 + 1 (псевдо id)
CREATE index IX_Table1_ref2 on Table1 (ref1, ref2) --2 +2
CREATE index IX_Table1_ref2 on Table1 (ref2) --1+2

Получается 11.


Псевдо id он мало может весить, поэтому это не 1.
8 июн 17, 16:09    [20550652]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Так можно выиграть в 1 поле, но мы не теряем контроль уникальности.
8 июн 17, 16:11    [20550658]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Rankatan
Псевдо id он мало может весить, поэтому это не 1.
Это надо уточнить. Это интересно.
8 июн 17, 16:12    [20550665]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30778
Rankatan
Если ключ кластерного индекса не уникален сервер делает его уникальным (добавляет еще один невидимый столбец по сути)
Так он же будет большой, 16 байт вроде.
ИМХО лучше добавить identity id как PRIMARY KEY, если это возможно.
8 июн 17, 19:02    [20551289]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33260
Блог
Deff,

лучше задать вопрос про исходную задачу,
чем обсуждать решение без постановки
8 июн 17, 21:24    [20551480]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
Критик
Deff,

лучше задать вопрос про исходную задачу,
чем обсуждать решение без постановки
Задача как раз теоретическая.
Но как знакомый нам пример таблица из двух полей для реализации отношения многие ко многим.
Отсюда вытекают и задачи для перемножения трех сущностей, и более.

Для двух бытовой пример это ассортиментная матрица "Магазин" - "Товар". И понятно, что мы хотим посмотреть и матрицу по магазину, и посмотреть в каких магазинах товар.

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

Задач на самом деле много подобных.

Для отчетности помогают кубы или колоночные индексы. Но и для оперативного учета бывает необходимость получать всю информацию.
8 июн 17, 21:56    [20551542]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
aleks2
Guest
Deff
Критик
Deff,

лучше задать вопрос про исходную задачу,
чем обсуждать решение без постановки
Задача как раз теоретическая.
Но как знакомый нам пример таблица из двух полей для реализации отношения многие ко многим.
Отсюда вытекают и задачи для перемножения трех сущностей, и более.

Для двух бытовой пример это ассортиментная матрица "Магазин" - "Товар". И понятно, что мы хотим посмотреть и матрицу по магазину, и посмотреть в каких магазинах товар.

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

Задач на самом деле много подобных.

Для отчетности помогают кубы или колоночные индексы. Но и для оперативного учета бывает необходимость получать всю информацию.


Ну и какие тяготы и лишения вы испытываете?

Три - не десять. 3! = 6

("Магазин", "Товар", "дата")
("Товар", "Магазин", "дата")
("дата", "Товар", "Магазин")
("дата", "Магазин", "Товар")
("Товар", "дата", "Магазин")
("Магазин", "дата", "Товар")

Исчерпывающий набор индексов.
9 июн 17, 08:12    [20551871]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30778
aleks2
Ну и какие тяготы и лишения вы испытываете?

Три - не десять. 3! = 6

("Магазин", "Товар", "дата")
("Товар", "Магазин", "дата")
("дата", "Товар", "Магазин")
("дата", "Магазин", "Товар")
("Товар", "дата", "Магазин")
("Магазин", "дата", "Товар")

Исчерпывающий набор индексов.
Для озвученной задачи третье поле можно даже в INCLUDE
Deff
Для отчетности помогают кубы или колоночные индексы.
Помогают они за счёт увеличения размера хранения.
Вот и тут - делаете набор индексов.
Будет медленнее вставка, будет больше места, но зато быстрые выборки.
9 июн 17, 08:26    [20551886]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
skyANA
Member

Откуда: Зеленоград
Сообщений: 27661
Deff
Критик
Deff,

лучше задать вопрос про исходную задачу,
чем обсуждать решение без постановки
Задача как раз теоретическая.
Но как знакомый нам пример таблица из двух полей для реализации отношения многие ко многим.
Отсюда вытекают и задачи для перемножения трех сущностей, и более.

Для двух бытовой пример это ассортиментная матрица "Магазин" - "Товар". И понятно, что мы хотим посмотреть и матрицу по магазину, и посмотреть в каких магазинах товар.

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

Задач на самом деле много подобных.

Для отчетности помогают кубы или колоночные индексы. Но и для оперативного учета бывает необходимость получать всю информацию.

А потом статистика показывает, что на самом деле интересна только вот такая выборка, а остальными пользуются считанные проценты пользователей.
Но индексов на таблицу уже понавешали :)
9 июн 17, 08:36    [20551898]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18323
skyANA
Deff
пропущено...
Задача как раз теоретическая.
Но как знакомый нам пример таблица из двух полей для реализации отношения многие ко многим.
Отсюда вытекают и задачи для перемножения трех сущностей, и более.

Для двух бытовой пример это ассортиментная матрица "Магазин" - "Товар". И понятно, что мы хотим посмотреть и матрицу по магазину, и посмотреть в каких магазинах товар.

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

Задач на самом деле много подобных.

Для отчетности помогают кубы или колоночные индексы. Но и для оперативного учета бывает необходимость получать всю информацию.

А потом статистика показывает, что на самом деле интересна только вот такая выборка, а остальными пользуются считанные проценты пользователей.
Но индексов на таблицу уже понавешали :)
А без индексов в те редкие моменты уходить на фулл скан?
9 июн 17, 08:48    [20551919]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
skyANA
Member

Откуда: Зеленоград
Сообщений: 27661
Deff
skyANA
пропущено...

А потом статистика показывает, что на самом деле интересна только вот такая выборка, а остальными пользуются считанные проценты пользователей.
Но индексов на таблицу уже понавешали :)
А без индексов в те редкие моменты уходить на фулл скан?

Для начала я бы задумался о том, а может не стоит тратить время и придумывать для пользователя интерфейс, где он и так и сяк может изгаляться.
А сфокусироваться на том, что ему действительно нужно, а редкие точки феодал выпилить.

Вообщем если хочется сделать оптимально, то следует плясать от бизнес-логики, а не от базы.
9 июн 17, 09:03    [20551949]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
skyANA
Member

Откуда: Зеленоград
Сообщений: 27661
Точки входа
9 июн 17, 09:03    [20551950]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
skyANA
Member

Откуда: Зеленоград
Сообщений: 27661
Deff, и пример Вы выбрали не лучший.

Не думаю, что на практике кто-то будет просто выбирать три идентификатора и дату.
Обязательно будет JOIN с таблицами "Магазины" и "Товары", а значит достаточно одного индекса.
9 июн 17, 09:17    [20551973]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Rankatan
Member

Откуда:
Сообщений: 250
alexeyvg
Rankatan
Если ключ кластерного индекса не уникален сервер делает его уникальным (добавляет еще один невидимый столбец по сути)
Так он же будет большой, 16 байт вроде.
ИМХО лучше добавить identity id как PRIMARY KEY, если это возможно.

Откуда информация про 16 байт? Мне человек с кучей скилов говорил, что этот размер динамический. Кто точно знает как определяется размер "скрытого столбца" поделитесь.
9 июн 17, 10:30    [20552172]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30778
Rankatan
alexeyvg
пропущено...
Так он же будет большой, 16 байт вроде.
ИМХО лучше добавить identity id как PRIMARY KEY, если это возможно.

Откуда информация про 16 байт? Мне человек с кучей скилов говорил, что этот размер динамический. Кто точно знает как определяется размер "скрытого столбца" поделитесь.
Да, неправильно сказал, типично 6-8 байт.
9 июн 17, 10:58    [20552269]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Rankatan
Member

Откуда:
Сообщений: 250
alexeyvg
Rankatan
пропущено...

Откуда информация про 16 байт? Мне человек с кучей скилов говорил, что этот размер динамический. Кто точно знает как определяется размер "скрытого столбца" поделитесь.
Да, неправильно сказал, типично 6-8 байт.

От чего зависит размер? Там может быть 0-2 байт? Вот это интересует
9 июн 17, 10:59    [20552280]     Ответить | Цитировать Сообщить модератору
 Re: Индексируем табличку из 3ех полей для любого запроса.  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7399
Для такой задачи надо смотреть в сторону решения Business Analisys в табличном режиме.
9 июн 17, 11:25    [20552401]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить