Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как оптимизировать запрос  [new]
Быдло_____кодер
Guest
Есть тормозящий запрос, выбирающий данные из нескольких многомилионных таблиц
В одной из "толстых" таблиц (главной, той к которой доджойниваются остальные) есть 2 поля, назовем их DepartmentId, StatusID
Логика построена таким образом, что из главной таблицы всегда нужны только данные по конкретному DepartmentId и в 95% случаев по конкретному StatusID, Т.е запрос выглядит так:
  SELECT val1, val2... FROM big_table master
  JOIN big_table_history detail
  ON master.actualDetailId = detail.detailId
  WHERE DepartmentId = @DepartmentID AND 
   (StatusID = @StatusID OR @StatusID is NULL)


Стоит ли делать в таблице big_table кластеризированный индекс по полям BigTableId, DepartmentID?
Стоит ли делать в таблице big_table кластеризированный индекс по полям BigTableId, DepartmentID, StatusID ?
Стоит ли делать в таблице big_table НЕкластеризированный индекс по полям BigTableId, DepartmentID но с добавочными столбцами по всем полям что есть в запросе?
Стоит ли материализировать таблицу big_table добавив туда актуальные значения из таблицы big_table_history так чтобы не обращаться к таблице - детали?
Как факт добавления в кластеризированный индекс DepartmentID повлияет на вставку новых записей, правильно ли я понимаю: физически на диске данные в таблице все равно хранятся в случайном порядке просто для доступа к ним ВСЕГДА используется кластеризированный индекс и если кто-то вставит запись с DEpartmentId = 1 это не приведет к переписыванию записей с DEpartmentId = 2,3,4... просто перестроится один из узлов индекса?
11 июн 17, 15:20    [20556633]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
aleks2
Guest
Если оптимизировать под именно этот запрос:

1. Кластерный индекс big_table(DepartmentID, StatusID, BigTableId )
2. Кластерный индекс big_table_history (detailId)
11 июн 17, 16:06    [20556669]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
И +можно ещё вот так:
SELECT val1, val2...
FROM big_table master
JOIN big_table_history detail ON master.actualDetailId = detail.detailId
WHERE DepartmentId = @DepartmentID
  AND StatusID = @StatusID
UNION ALL
SELECT val1, val2...
FROM big_table master
JOIN big_table_history detail ON master.actualDetailId = detail.detailId
WHERE DepartmentId = @DepartmentID
  AND @StatusID IS NULL;
11 июн 17, 16:11    [20556675]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
vavisv
Member

Откуда:
Сообщений: 8
aleks2,
А почему именно кластерный индекс?

Быдло_____кодер
Как факт добавления в кластеризированный индекс DepartmentID повлияет на вставку новых записей, правильно ли я понимаю: физически на диске данные в таблице все равно хранятся в случайном порядке просто для доступа к ним ВСЕГДА используется кластеризированный индекс и если кто-то вставит запись с DEpartmentId = 1 это не приведет к переписыванию записей с DEpartmentId = 2,3,4... просто перестроится один из узлов индекса?

Кластерный индекс и есть данные, то есть данные физически хранятся согласно порядку, диктуемому кластерным индексом.
И да, если кто-то вставит данные не в порядке добавления в конец индекса - получишь много split_page, что очень плохо скажется на производительности.

Попробуй дать запрос database tuning advisor-у и посмотри, что он тебе предложит. Для оптимизации запросов лучше смотреть в сторону некластерных индексов или статистики, а кластерные индексы лучше применять для оптимизации хранения.
15 июн 17, 11:34    [20566141]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
aleks2
Guest
vavisv
aleks2,
А почему именно кластерный индекс?

Быдло_____кодер
Как факт добавления в кластеризированный индекс DepartmentID повлияет на вставку новых записей, правильно ли я понимаю: физически на диске данные в таблице все равно хранятся в случайном порядке просто для доступа к ним ВСЕГДА используется кластеризированный индекс и если кто-то вставит запись с DEpartmentId = 1 это не приведет к переписыванию записей с DEpartmentId = 2,3,4... просто перестроится один из узлов индекса?

Кластерный индекс и есть данные, то есть данные физически хранятся согласно порядку, диктуемому кластерным индексом.
И да, если кто-то вставит данные не в порядке добавления в конец индекса - получишь много split_page, что очень плохо скажется на производительности.

Попробуй дать запрос database tuning advisor-у и посмотри, что он тебе предложит. Для оптимизации запросов лучше смотреть в сторону некластерных индексов или статистики, а кластерные индексы лучше применять для оптимизации хранения.


Уймись, студент.
И осознай тривиальную истину "некластерные индексы" хранятся не в вакууме.
А database tuning advisor - это для таких как ты придумали.
15 июн 17, 12:20    [20566380]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
vavisv
Member

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

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

database tuning advisor + sys.dm_db_missing_index* с индексами очень даже помогут.
15 июн 17, 13:26    [20566728]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
msLex
Member

Откуда:
Сообщений: 7979
vavisv
никак не влияют на расположение полей таблицы, не входящих в индекс

Расположение полей таблицы ГДЕ?
15 июн 17, 13:40    [20566811]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
vavisv
Member

Откуда:
Сообщений: 8
msLex
vavisv
никак не влияют на расположение полей таблицы, не входящих в индекс

Расположение полей таблицы ГДЕ?


Физически на страницах. Кластерный распределяет данные всех полей таблицы по страницам, некластерный лежит на совсем других страницах. Я не просто так упомянул split_page.

Как-то мы ушли от вопроса по оптимизации запроса.
15 июн 17, 14:15    [20566978]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
msLex
Member

Откуда:
Сообщений: 7979
vavisv
split_page

А что, некластерный индекс не подвержен это "напасти"?
15 июн 17, 14:18    [20566994]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
vavisv
Member

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

Определенно подвержен. Но чем меньше данных надо перемещать, тем быстрее будет выполнена операция.
15 июн 17, 16:12    [20567553]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
aleks2
Guest
vavisv
msLex,

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


Т.е.
2 (два) раза записать + 1 раз переместить
быстрее, чем
1 (один) записать + 1 раз переместить
?

Бредите?

ЗЫ. А если еще учесть, что при расщеплении страниц ничего никуда не перемещается...
15 июн 17, 16:46    [20567679]     Ответить | Цитировать Сообщить модератору
 Re: Как оптимизировать запрос  [new]
gaijin
Member

Откуда:
Сообщений: 1
Быдло_____кодер,
В перспективе наверняка будут использоваться и некластерные индексы, поэтому кластерный имеет смысл сделать по уникальному набоору столбцов и как можно меньше. Предполагаю, что BigTableId будет достаточно.
У некластеризованного индекса должна быть высокая селективность, чтобы использовать его наиболее эффективно, иначе даже при наличии индекса оптимизатор скорее всего выполнит table/clustered index scan.
Вряд ли в миллионной таблице есть такого же порядка количество DepartmentId. А вот по статусу предполагаю, что эти 95% -- что-то вроде активных на текущий момент и их намного меньше, чем остальных, архивных. Можно попробовать сделать filtered index по DepartmentId с условием по статусу (те самые 95% + null). Если по остальным кейсам результат устраивать не будет, то сделать индекс по DepartmentId,StatusId (первый ставим с наибольшей селективностью). В зависимости от вытаскиваемых колонок можно ещё добавить include — actualDetailId + все колонки в select (тут уж думать самому, если таких колонок много).
В big_table_history, соответственно, индекс по detailId.

Возможно более опытные товарищи меня поправят :)
21 июн 17, 00:29    [20579073]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить