Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Не задействуется индекс - прошу помочь с поиском причины / 13 сообщений из 13, страница 1 из 1
15.02.2018, 08:38
    #39602127
Kateryne
Kateryne
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Добрый день!
Уже очень давно не имела дело с СУБД, возможно забыла какую-то базовую вещь, прошу подсказать.

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

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 будет достаточно. Что я не учла? В чем может быть проблема?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 08:43
    #39602128
Kateryne
Kateryne
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Прошу прощения - забыла тег. Поправляюсь.

Код: sql
1.
2.
3.
4.
5.
6.
7.
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 - не индексировано.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 09:10
    #39602140
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Kateryne,

всё правильно сервер сделал.... у тебя никаких условий фильтрации по индексным полям нет. соответственно, ему всё одно придется перебрать все строки. Логично, что он начал с чтения кластерного индекса по самой большой таблицы....
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 09:18
    #39602144
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Kateryne,

и пока не пришли остальные адепты...
статистика по таблицам-индексам - актуальна ?
индексы не фрагментированы в лоскуты-клочья?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 13:13
    #39602283
Гость, проходящий мимо
Гость, проходящий мимо
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Kateryne,

select a,s,c
from table1
join table2 on table1.id = table2.id with (index(indexname), nolock)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 18:39
    #39602603
Kateryne
Kateryne
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Добрый Э - ЭхKateryne,

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

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

Статистика актуальна, индекс по полю id_subcontract создан с нуля, так что по идее не должен быть расплющен по диску. Но - вообще где-то в этом направлении проблема может быть, так как данные в этой большой таблице на 300 млн перезаливались с нуля с другой такой же большой таблицы с почти такой же структурой.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 18:40
    #39602605
Kateryne
Kateryne
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Гость, проходящий мимоKateryne,

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

спасибо! можно и так попробовать, сделаем завтра тест, но хотелось бы понять первопричину без подобных хитростей...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 18:45
    #39602609
TaPaK
TaPaK
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
KateryneГость, проходящий мимоKateryne,

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

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

в теории если менть ничего не хотите OPTION(FORCE ORDER) может помочь
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 19:25
    #39602635
Добрый Э - Эх
Добрый Э - Эх
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Kateryne,

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

но на правах метода "пальцем в небо" предположу - возможно, сервер ошибается в оценке кардинальности множеств
сходу не вспомню, поэтому придется тебе самой почитать - как оценивает сервер количество строк во временной таблице... Видимо, он не понимает, что у тебя там именно 10-20 т. строк, а не одна или не миллион....
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 20:28
    #39602652
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Kateryne
Код: sql
1.
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 из таблицы. А это дорогая операция.

Чтобы убедиться, можете сравнить финальную стоимость запроса в оценочных планах для
Код: sql
1.
2.
3.
4.
5.
6.
7.
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 - не индексировано.

и
Код: sql
1.
2.
3.
4.
5.
6.
7.
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 - не индексировано.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 20:35
    #39602656
Kateryne
Kateryne
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
invmНичего не путаете? Может все-таки таблицы bt_child?
[/quot]
Да, конечно - опечаталась.

Тогда всем спасибо большое за советы, завтра попробую - сейчас доступа нет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 21:29
    #39602665
Mind
Mind
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Добрый Э - Эх,

Да даже если сервер не ошибается, то очень грубо это будет примерно так:
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%, что достаточно много чтобы сервер выбрал поиск по индексу + лукап. Скан дешевле.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.02.2018, 21:52
    #39602667
Kateryne
Kateryne
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Не задействуется индекс - прошу помочь с поиском причины
Mind,
ага... все, теперь понятно. Логично. Проведу завтра эксперименты, но очень похоже на правду, и теперь мне логика понятна.
Спасибо за комментарий.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Не задействуется индекс - прошу помочь с поиском причины / 13 сообщений из 13, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]