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

Откуда:
Сообщений: 360
Столкнулся с проблемой
Есть запрос, по индексированным полям
(@P1 numeric(10),@P2 varbinary(16),@P3 varbinary(16))
SELECT
MAX(T1._Fld18362)
FROM _InfoRg18168 T1 WITH(NOLOCK)
WHERE ((T1._Fld628 = @P1)) AND ((T1._Fld18175RRef = @P2) AND (T1._Fld18169RRef = @P3))

начал исполняться медленно при перевале количества записей до 50 миллионов
Судя по плану запроса получается что запрос использует только кластерный индекс куда входит T1._Fld18169RRef = @P3
но не использует обычный индекс куда входит (T1._Fld18175RRef = @P2)
Запрос генерится платформой 1СV8 она хинтов не ставит. Перемена полей условия местами эффекта не дает

Вопрос как заставить MS SQL 2008 R2 использовать индекс если он есть, но без хинтов?

Я конечно вышел из положения поменяв запрос так (т.е. вывел одно поле в группировку а другое оставил в условии - в этом случае используются оба индекса)

(@P1 numeric(10),@P2 varbinary(16),@P3 varbinary(16))SELECT
T1._Fld18169RRef,
MAX(T1._Fld18362)
FROM _InfoRg18168 T1 WITH(NOLOCK)
WHERE ((T1._Fld628 = @P1)) AND ((T1._Fld18175RRef = @P2))
GROUP BY T1._Fld18169RRef
HAVING (T1._Fld18169RRef = @P3)

Сергей С
21 окт 16, 16:35    [19810132]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76,

а индексы то хоть какие?
если гадать то в индексе не хватает того что выбросили...

зы где вступают в эту секту?
автор
Перемена полей условия местами эффекта не дает
21 окт 16, 16:44    [19810177]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
selis76
Member

Откуда:
Сообщений: 360
Индекса два
CREATE UNIQUE CLUSTERED INDEX [_InfoR18168_ByDims_RST] ON [dbo].[_InfoRg18168]
(
[_Fld628] ASC,
[_Fld18169RRef] ASC,
[_Fld18170] ASC,
[_Fld18287] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [_InfoR18168_ByResource18178_RRST] ON [dbo].[_InfoRg18168]
(
[_Fld628] ASC,
[_Fld18175RRef] ASC,
[_Fld18169RRef] ASC,
[_Fld18170] ASC,
[_Fld18287] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
21 окт 16, 16:48    [19810198]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76,

INCLUDE(_Fld18362) в некластерный
21 окт 16, 16:51    [19810225]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
selis76
Member

Откуда:
Сообщений: 360
TaPaK
selis76,


зы где вступают в эту секту?
автор
Перемена полей условия местами эффекта не дает


Просто когда вычисляется условие AND вычисления сокращаются если первое поле = ложь , второе поле вычислять не имеет смысла.
Предположил что может помочь. Учитывая что оптимизатор SQL настолько слеп что не видит индексов - такие предположения не кажутся бредовыми
21 окт 16, 16:52    [19810234]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76
TaPaK
selis76,


зы где вступают в эту секту?
пропущено...


Просто когда вычисляется условие AND вычисления сокращаются если первое поле = ложь , второе поле вычислять не имеет смысла.
Предположил что может помочь. Учитывая что оптимизатор SQL настолько слеп что не видит индексов - такие предположения не кажутся бредовыми

вы сильно удивитесь, но скл плевать как вы там пишете :)
21 окт 16, 16:54    [19810247]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76,

автор
Учитывая что оптимизатор SQL настолько слеп что не видит индексов

все прям мечтают оскорбить оптимизатор...
21 окт 16, 16:55    [19810252]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
selis76
Member

Откуда:
Сообщений: 360
TaPaK
selis76,

INCLUDE(_Fld18362) в некластерный

Он и так там. И по плану с ним проблем нет

CREATE UNIQUE NONCLUSTERED INDEX [_InfoR18168_ByResource18874_NRST] ON [dbo].[_InfoRg18168]
(
[_Fld628] ASC,
[_Fld18362] ASC,
[_Fld18169RRef] ASC,
[_Fld18170] ASC,
[_Fld18287] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
21 окт 16, 16:56    [19810262]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76
TaPaK
selis76,

INCLUDE(_Fld18362) в некластерный

Он и так там. И по плану с ним проблем нет

CREATE UNIQUE NONCLUSTERED INDEX [_InfoR18168_ByResource18874_NRST] ON [dbo].[_InfoRg18168]
(
[_Fld628] ASC,
[_Fld18362] ASC,
[_Fld18169RRef] ASC,
[_Fld18170] ASC,
[_Fld18287] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

мммм вы индексы суммируете что ли?
в приведённом его нет
CREATE UNIQUE NONCLUSTERED INDEX [_InfoR18168_ByResource18178_RRST] ON [dbo].[_InfoRg18168] 
(
[_Fld628] ASC,
[_Fld18175RRef] ASC,
[_Fld18169RRef] ASC,
[_Fld18170] ASC,
[_Fld18287] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
21 окт 16, 16:59    [19810275]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
selis76
Member

Откуда:
Сообщений: 360
так они разные, я просто для
_Fld18362 не привел сразу индекс, так как речь шла об условии из-за которого вся проблема
21 окт 16, 17:19    [19810385]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76,
Я отказываюсь понимать вашу логику
21 окт 16, 18:02    [19810541]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
selis76
Он и так там
Вам все равно в какой индекс входит столбец, главное - чтоб в какой-нибудь входил?
21 окт 16, 18:12    [19810579]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
selis76
Member

Откуда:
Сообщений: 360
invm
selis76
Он и так там
Вам все равно в какой индекс входит столбец, главное - чтоб в какой-нибудь входил?

индексы платформа 1С создает.
Я конечно могу создать его сам, но это уже как бы не совсем штатный способ.
В принципе я думал что раз столбец уже входит в какой то индекс, то его оптимизатор его может применить.
Тем более здесь есть три индекса для разных столбцов - столбец Fld628 входит во все индексы.
Хотелось бы понять логику оптимизатора , где на эту тему можно почитать.
Изначальный вопрос был
"Вопрос как заставить MS SQL 2008 R2 использовать индекс если он есть, но без хинтов?"
21 окт 16, 19:43    [19810890]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76,

Прежде чем заставлять сервер узнайте хоть что то о индексах и их использованию. Любая книга подойдет
21 окт 16, 20:14    [19810994]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
selis76
Member

Откуда:
Сообщений: 360
TaPaK
selis76,

Прежде чем заставлять сервер узнайте хоть что то о индексах и их использованию. Любая книга подойдет

Мне на конкретный вопрос ответ интересен. Я раньше в основном занимался oracle и сертифицировался по нему поэтому общую теорию знаю, нюансы ms sql не очень
21 окт 16, 20:33    [19811034]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
o-o
Guest
вопрос-то какой?
почему при отсутствии покрывающего индекса будет использован кластерный?
потому что если нет всех нужных полей в данном индексе (индекс не покрывает запрос),
придется делать лукапы в кластерный за недостающими полями.
и если число лукапов приближается к сотой доле общего числа строк таблицы,
проще просканировать кластерный, т.к. это будет чтение подряд,
что дешевле рандомного чтения при лукапах
21 окт 16, 20:45    [19811066]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
selis76
Member

Откуда:
Сообщений: 360
o-o
вопрос-то какой?
почему при отсутствии покрывающего индекса будет использован кластерный?
потому что если нет всех нужн.....

Просто в запросе в условии только конструкция and неужели оптимизатор не может применить два разных индекса последовательно без покрывающего?
21 окт 16, 20:58    [19811091]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
iljy
Member

Откуда:
Сообщений: 8711
selis76
o-o
вопрос-то какой?
почему при отсутствии покрывающего индекса будет использован кластерный?
потому что если нет всех нужн.....

Просто в запросе в условии только конструкция and неужели оптимизатор не может применить два разных индекса последовательно без покрывающего?


Оптимизатор много чего может, но основным критерием для него является не количество задействованных индексов, а ПРЕДПОЛАГАЕМОЕ время выполнения. Если он считает, что дешевле сразу использовать кластерный, то он будет его использовать.

Вообще рекомендации в таких случаях всегда очень банальны: в первую очередь сравните предполагаемое количество строк с реальным, возможно, что просто устарела статистика.
21 окт 16, 21:02    [19811108]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
komrad
Member

Откуда:
Сообщений: 5252
selis76
Изначальный вопрос был
"Вопрос как заставить MS SQL 2008 R2 использовать индекс если он есть, но без хинтов?"

можно пошаманить с plan guides
способ, конечно, не идеальный, но иногда другого выхода нет
21 окт 16, 21:02    [19811109]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
selis76,

Модная тенденция: читать не буду, я вс знаю, то сервер плохой, расскажите мне...
21 окт 16, 21:03    [19811111]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
o-o
Guest
структуру таблицы не показал, может там в таблице, о боже, 6 полей,
а индексы у него по 4-5 полей,
ну так 2 индекса поднять с диска выйдет дороже, чем 1 кластерный.
а по поводу неиспользования одного непокрывающего

сколько строк возвращает
SELECT count(*)
FROM _InfoRg18168 T1
WHERE ((T1._Fld628 = @P1)) AND ((T1._Fld18175RRef = @P2) AND (T1._Fld18169RRef = @P3))
?
если чуть меньше миллиона, уже нет смысла индекс использовать
21 окт 16, 22:25    [19811264]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
Djjvfd
Guest
selis76
индексы платформа 1С создает.


Вы удивитесь, но внутри 1С есть механизм описания индексов, которые не исчезают даже при обновлениях 1С
21 окт 16, 22:30    [19811278]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
selis76
TaPaK
selis76,

Прежде чем заставлять сервер узнайте хоть что то о индексах и их использованию. Любая книга подойдет

Мне на конкретный вопрос ответ интересен. Я раньше в основном занимался oracle и сертифицировался по нему поэтому общую теорию знаю, нюансы ms sql не очень
Не ходите больше в эту школу, а сертификат можете выкинуть. Вы попробуйте тоже самое сделать в Оракле, очень удивитесь что индексы работают примерно одинаково во всех РСУБД.

selis76
"Вопрос как заставить MS SQL 2008 R2 использовать индекс если он есть, но без хинтов?"
Это из разряда как заставить собаку есть сено?

selis76
Просто в запросе в условии только конструкция and неужели оптимизатор не может применить два разных индекса последовательно без покрывающего?
И пофигу где там какие поля стоят в индексе, в каком порядке и как в запросе используются. Ну вы условия то попереставляйте еще. Не все варианты небось перебрали, авось научите оптимизатор использовать индексы которые ну совсем не подходят.
21 окт 16, 23:07    [19811384]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
guest45
Guest
Специалистам по 1С нужно учить матчасть
22 окт 16, 09:39    [19811817]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизатор SQL не использует индекс заданный 1С V8  [new]
nicxxx
Member

Откуда: Москва
Сообщений: 58
guest45, причем здесь временные таблицы? надо выбрать из регистра сведений, в котором лежит от 50 до 100 миллионов записей, максимальный номер записи, с отбором по измерению и ресурсу. ты предлагаешь 100 млн во врем таблицу выкидывать? это займет еще больше времени. лучше вообще молчи, если сказать нечего.
25 окт 16, 18:17    [19821236]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить