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

Откуда:
Сообщений: 351
Добрый день!
Уже очень давно не имела дело с СУБД, возможно забыла какую-то базовую вещь, прошу подсказать.

Есть запрос такого вида:

select flt.id, bt_child.value1, bt_child.value2, bt.fieldbt
from BigTable bt // таблица примерно на 3-4 млн записей с кластерным индексом по id
inner join #SmallTable flt // временная таблица примерно на 10-20 тыс записей, неиндексирована
on flt.id= bt.id_contract // поле id_contract таблицы bt - индексировано
inner join BigTable_Child bt_child // таблица примерно на 300 млн записей с кластерным индексом по id
on bt_child .id_subcontract= bt.id // поле id_subcontract таблицы bt - индексировано
where bt_child.stringfield in('string1','string2') // поле stringfield - не индексировано.

запрос выполняется долго (десятки минут).
План запроса показывает, что что индекс по полю bt_child .id_subcontract не задействован, вместо него используется кластерный индекс BigTable_Child.id, и эта операция дает 95% стоимости запрос. Рекомендует создание индекса по полю bt_child.stringfield.
Но этого делать бы не хотелось, так как для таблицы критично время вставки.

Я думала, что индекса по полю bt_child .id_subcontract будет достаточно. Что я не учла? В чем может быть проблема?
15 фев 18, 08:38    [21192750]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
Kateryne
Member

Откуда:
Сообщений: 351
Прошу прощения - забыла тег. Поправляюсь.

select flt.id, bt_child.value1, bt_child.value2, bt.fieldbt
from BigTable bt // таблица примерно на 3-4 млн записей с кластерным индексом по id
inner join #SmallTable flt // временная таблица примерно на 10-20 тыс записей, неиндексирована
on flt.id= bt.id_contract // поле id_contract таблицы bt - индексировано
inner join BigTable_Child bt_child // таблица примерно на 300 млн записей с кластерным индексом по id
on bt_child .id_subcontract= bt.id // поле id_subcontract таблицы bt - индексировано
where bt_child.stringfield in('string1','string2') // поле stringfield - не индексировано. 
15 фев 18, 08:43    [21192757]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
Добрый Э - Эх
Guest
Kateryne,

всё правильно сервер сделал.... у тебя никаких условий фильтрации по индексным полям нет. соответственно, ему всё одно придется перебрать все строки. Логично, что он начал с чтения кластерного индекса по самой большой таблицы....
15 фев 18, 09:10    [21192802]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
Добрый Э - Эх
Guest
Kateryne,

и пока не пришли остальные адепты...
статистика по таблицам-индексам - актуальна ?
индексы не фрагментированы в лоскуты-клочья?
15 фев 18, 09:18    [21192825]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
Гость, проходящий мимо
Guest
Kateryne,

select a,s,c
from table1
join table2 on table1.id = table2.id with (index(indexname), nolock)
15 фев 18, 13:13    [21193663]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
Kateryne
Member

Откуда:
Сообщений: 351
Добрый Э - Эх
Kateryne,

всё правильно сервер сделал.... у тебя никаких условий фильтрации по индексным полям нет. соответственно, ему всё одно придется перебрать все строки. Логично, что он начал с чтения кластерного индекса по самой большой таблицы....


Спасибо за ответ! Но не совсем поняла - у меня собственно и был вопрос, почему оптимизатор в данном случае не хочет сначала использовать условие соединения в выборке по индексированному полю (это дало бы 10-20 тыс строк), а потом по ним уже фильтровать по неиндексированному?
Я же правильно помню, что при inner join порядок - что будет раньше - where или join - выбирает оптимизатор? Или ошибаюсь?

Статистика актуальна, индекс по полю id_subcontract создан с нуля, так что по идее не должен быть расплющен по диску. Но - вообще где-то в этом направлении проблема может быть, так как данные в этой большой таблице на 300 млн перезаливались с нуля с другой такой же большой таблицы с почти такой же структурой.
15 фев 18, 18:39    [21194884]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
Kateryne
Member

Откуда:
Сообщений: 351
Гость, проходящий мимо
Kateryne,

select a,s,c
from table1
join table2 on table1.id = table2.id with (index(indexname), nolock)


спасибо! можно и так попробовать, сделаем завтра тест, но хотелось бы понять первопричину без подобных хитростей...
15 фев 18, 18:40    [21194886]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Kateryne
Гость, проходящий мимо
Kateryne,

select a,s,c
from table1
join table2 on table1.id = table2.id with (index(indexname), nolock)


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

получите ещё один лукап

в теории если менть ничего не хотите OPTION(FORCE ORDER) может помочь
15 фев 18, 18:45    [21194895]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
Добрый Э - Эх
Guest
Kateryne,

ты же не показала план запроса, как можно говорить что-то более конкретно? (а если будешь показывать, то лучше не картинку, как это делают некоторые, а полный xml вложи к сообщению)

но на правах метода "пальцем в небо" предположу - возможно, сервер ошибается в оценке кардинальности множеств
сходу не вспомню, поэтому придется тебе самой почитать - как оценивает сервер количество строк во временной таблице... Видимо, он не понимает, что у тебя там именно 10-20 т. строк, а не одна или не миллион....
15 фев 18, 19:25    [21194999]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
Kateryne
on bt_child .id_subcontract= bt.id // поле id_subcontract таблицы bt - индексировано
Ничего не путаете? Может все-таки таблицы bt_child?

Индекс сделайте на bt_child как (id_subcontract) include (value1, value2)
Если просто по id_subcontract, то потребуется Key Lookup, чтобы достать value1, value2 из таблицы. А это дорогая операция.

Чтобы убедиться, можете сравнить финальную стоимость запроса в оценочных планах для
select flt.id, bt_child.value1, bt_child.value2, bt.fieldbt
from BigTable bt // таблица примерно на 3-4 млн записей с кластерным индексом по id
inner join #SmallTable flt // временная таблица примерно на 10-20 тыс записей, неиндексирована
on flt.id= bt.id_contract // поле id_contract таблицы bt - индексировано
inner join BigTable_Child bt_child // таблица примерно на 300 млн записей с кластерным индексом по id
on bt_child .id_subcontract= bt.id // поле id_subcontract таблицы bt - индексировано
where bt_child.stringfield in('string1','string2') // поле stringfield - не индексировано. 
и
select flt.id, bt_child.value1, bt_child.value2, bt.fieldbt
from BigTable bt // таблица примерно на 3-4 млн записей с кластерным индексом по id
inner join #SmallTable flt // временная таблица примерно на 10-20 тыс записей, неиндексирована
on flt.id= bt.id_contract // поле id_contract таблицы bt - индексировано
inner loop join BigTable_Child bt_child with(index = ...)// таблица примерно на 300 млн записей с кластерным индексом по id
on bt_child .id_subcontract= bt.id // поле id_subcontract таблицы bt - индексировано
where bt_child.stringfield in('string1','string2') // поле stringfield - не индексировано. 
15 фев 18, 20:28    [21195088]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
Kateryne
Member

Откуда:
Сообщений: 351
invm
Ничего не путаете? Может все-таки таблицы bt_child?
[/quot]
Да, конечно - опечаталась.

Тогда всем спасибо большое за советы, завтра попробую - сейчас доступа нет.
15 фев 18, 20:35    [21195098]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Добрый Э - Эх,

Да даже если сервер не ошибается, то очень грубо это будет примерно так:
1. Отношение BigTable к BigTable_Child примерно 1 к 75 (4 млн/300 млн)
2. Отношение BigTable к #SmallTable - 1 к 1? Сомневаюсь. id_contract выглядит как внешний ключ, скорее всего неуникальный. Ну предположим, что 1 к 1.
3. Итого 20К строк из BigTable превращаются в 20К*75 = 1.5 млн BigTable_Child
4. И теперь нам нужно сделать 1.5 млн лукапов. Что уже достаточно много, и полный скан скорее всего предопчтителен.
5. Ну и совсем уже пальцем в небо: если предположить что 1 строка в BigTable_Child занимает 500 байт, то всего на странице может быть максимум 16 строк, получается 18,750,000 страниц на листовом уровне. Если же делать лукапы то нужно прочитать 1.5 млн x 4 = 6 млн страниц. По сравнению с полным сканом это 32%, что достаточно много чтобы сервер выбрал поиск по индексу + лукап. Скан дешевле.
15 фев 18, 21:29    [21195165]     Ответить | Цитировать Сообщить модератору
 Re: Не задействуется индекс - прошу помочь с поиском причины  [new]
Kateryne
Member

Откуда:
Сообщений: 351
Mind,
ага... все, теперь понятно. Логично. Проведу завтра эксперименты, но очень похоже на правду, и теперь мне логика понятна.
Спасибо за комментарий.
15 фев 18, 21:52    [21195181]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить