Comments 23
Что-то мне кажется, разница во времени объясняется банальным кэшированием - после первого запроса создания нового индекса его данные остались в оперативке. Число строк, выбираемых из обоих индексов одинаковое, а сами индексы по объёму почти не отличаются - чудес быть не должно. Можно дополнительно проверить, включив set statistics io on - число чтений скорее всего в обоих прогонах будет одинаковым.
Но вопрос, конечно, был в другом. И выбор второго индекса для меня, если честно, неожиданный - по всем формальным признакам он должен постоять "в стороне".
Подробнее про оптимизатор можно почитать у Дмитрия Пилюгина http://www.queryprocessor.ru/optimizer_unleashed_1/ . Он очень глубоко разбирается в механике работы QP. Какие-то знания, наверняка устарели, но в статье по ссылке - подробный разбор этапов формирования плана. Если включить специальные флаги трассировки (у него всё указано), можно посмотреть, какие варианты оцениваются и понять, почему "побеждает" итоговый план.
Я перед каждым прогоном делал
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
DBCC FREEPROCCACHE ;
DBCC DROPCLEANBUFFERS
За ссылку спасибо почитаю
В целом как я понял оптимизатор ориентируется на цену плана, и при сравнении важно не столько время исполнения запроса (оно может отличаться на разных машинах) , а показатели IO в плане и CPU . Из плана видно что "нетрадиционный" индекс побеждает по цене плана . Могу приложить текстовые файлы из профайлера если хочется оценить подробнее.
Возможно просто MS SQL использует особые алгоритмы обхода дерева индекса
The Balanced Search Tree (B-Tree) in SQL Databases (use-the-index-luke.com)
Покажите, пожалуйста, вывод set statistics io on для обоих запросов.
Кажется, что убрав одно поле из индекса, вы выиграли мегабайт 350 на 70 млн записей - этим не может объясняться разница в скорости выполнения
В первой картинке обычный индекс 1С с Fld_628 и полем номеров
Во второй картике индекс без Fld_628 разница в количестве чтений
А каков процент фрагментации "родного" 1Совского индекса и средняя заполненность страницы? Не могу понять чем может быть вызвана разница в 2 миллиона чтений для индексов, единственное различие между которыми - это 5 байтовая константа в ключе.
В вашем предыдущем посте данные из родного индекса возвращались также быстро, как из нового индекса в этом, даже когда на выходе были все 210 миллионов записей.
На Duration нет смысла смотреть, так как файловые группы на тестовой системе в HDD и там могут другие пользователи работать, как следствие на таком запросе время может плавать. Фрагментация родного индекса прилагается.
Предполагаю, что если вы сделаете ребилд индекса, количество чтений практически сравняется. Разница должна быть около 150 тысяч - 5 байт * 210 млн строк / 8192 (размер страницы) - столько сэкономлено за счёт описанных изменений ключа индекса.
Попробую, но это не объяснит - почему оптимизатор выбирает нетрадиционный индекс. Врядли он смотрит на фрагментацию это не быстро
Новый индекс уже существующего, читать меньше. Условие на равенство бессмысленно, он это знает из статистики. Поле есть в индексе - видимо оно в составе ключа кластерного индекса (или вы включили его в included).
Фрагментация может влиять на план запроса - у Пола Вайта был пример, где при сильно фрагментировааном индексе выбирался параллельный план. Но я и правда не уверен, что она может влиять на выбор индекса.
Проверьте, кстати, fill factor у обоих индексов, если он ниже у родного индекса - это тоже будет влияние на разницу в чтении.
Да Ваше предположение верно, переиндексация "_InfoR18860_ByDims18897_STRRRR" вернула предпочтения MS SQL обратно на селективный индекс. Количество чтений в нем гораздо меньше стало
Но если сделать эмуляцию запроса 1С и принудительно задать хинт
План исполнения нетрадиционного индекса всеравно лучше чем у традиционного 5972 против 6710
SELECT
T1._Period,
T1._Fld18861RRef,
T1._Fld18865,
T1._Fld18863RRef,
T1._Fld19363RRef
INTO #tt_RESULT
FROM dbo._InfoRg18860 T1 WITH(NOLOCK,INDEX=_InfoR18860_MySuperWithout_Fld628_ByDims18897_STRRRR)
INNER JOIN #tt_alternative T2 WITH(NOLOCK)
ON (T1._Fld628 =0 ) AND (T1._Fld18865 = T2._Q_000_F_000)
WHERE T1._Period>= DATEADD(month, -3,@BeginPer);
Estimate'ы - это ни о чём. Вы же видите, что количество чтений из родного индекса стало даже меньше, чем из ручного на прошлом скрине (ему, видимо, тоже нужен ребилд, т.к. из него читать всё равно должно быть меньше).
Плюс, вы в запросе используете локальную переменную, что тоже может оказывать влияние на estimate, попробуйте этот запрос выполнить через sp_executesql
Предполагаю, что _Fld628 входит в ключ кластерного индекса, поэтому этот индекс содержит все нужные данные, но чуть меньше в размере, вот оптимизатор его и выбрал.
Разница в скорости выполнения тоже кажется вызванной банальным кешированием.
В частности было отмечено, что без дополнительных условий в Index seek, в поток для Megre join попадают все записи индекса и приходится указывать дополнительные фильтры для ограничения. Вопрос: почему так происходит? - остался открытым.
Пропустил тот пост, сейчас прочитал - и так происходит потому что так работает merge join. Помогите ему, вместо фильтра по дате фильтр по ИдИсхСистемы >= (выбрать мин(СвязаннаяОпИдИсхСистемы) из Врем_...), если 1С так может (не помню уже). С вашим новым индексом, да и со старым тоже - это должно работать не хуже фильтра по дате
Я так пробовал, с фильтром ограничения по номеру он работает но примерно также как и ограничением по дате, план могу приложить. Я использовал фильтр по дате чтобы продемонстрировать что MS SQL склонен лезть в нижние ветки индекса. Казалось бы при Join у него уже должна быть информация о макс мин значениях которые он соединяет, и он может взять это и из статистики и из индекса. Но на практике пока не укажешь ограничения с низу - будет сканироваться весь индекс
Что вы имеете в виду под "нижние ветки индекса"? В любом случае он будет опускаться на листовой уровень, т.к. только оттуда можно получить данные, которые нужно вывести.
Я имею ввиду b-tree traversal как описано тут The Balanced Search Tree (B-Tree) in SQL Databases (use-the-index-luke.com)
Вроде МС ушли из РФ, проблематично купить. Может стоит рассматривать альтернативный продукты?
В реляционных СУБД везде все похоже, поскольку у них одинаковая база computer science (реляционная алгебра). В начале 2000х я сертифицировался как Oracle dba, мне этих знаний до сих пор хватает чтобы понимать MS SQL . Когда идешь в более "маленькие" СУБД типа MySQL понимаешь что там все похоже. Просто задаю себе вопрос - а вот у СУБД Х есть такое как у Oracle? И уже знаешь, где искать в документации СУБД Х ответ. Хочется наоборот не грузится как работает оптимизатор у конкретной СУБД, использовать общие практики (типа селективного индекса) но не получается :)
Во 2 варианте SQL читает меньше данных.
Я вот смотрю на все эти издострадания автора в течении последних 5 серий - это путь благородный, еще Конфуций его одобрял, но может rtfm по sqlю лучше.
Для полноты картины осталось сделать мегатест с полным убиранием разделителя из всех индексов, включая кластерный. Есть подозрение, что не стоило создавать регистр с разделителем. И ссылки на то, что в других объектах конфигурации разделитель останется и нужно будет делать соединения - несущественны, а вот то, что запросы в 1с можно будет писать проще не думая о разделителе - бесценно!
Для полноты картины осталось сделать мегатест с полным убиранием разделителя из всех индексов, включая кластерный. Есть подозрение, что не стоило создавать регистр с разделителем. И ссылки на то, что в других объектах конфигурации разделитель останется и нужно будет делать соединения - несущественны
Если в объекте присутствует разделитель 1С будет автоматом подставлять Fldxxx=0 , и просто из индекса его исключать нельзя. Проще
1) выгрузить данные через bcp без поля Fldxxx разделителя ,
2) сделать truncate ,
3) реструктуризацию штатным образом на отключение разделителя в конкретном объекте.
4) загрузить данные через bcp обратно
Примерно как описано тут 1С БодиПозитив / Хабр (habr.com)
простите, а сейчас вы часто "думаете о разделителе", когда пишете запросы в 1С?
Проблема ведь ровно в обратном: программист часто вообще не задумывается, что разделитель есть. Потому что в дереве конкретного объекта метаданных (документа, справочника) его не видно. В условие запроса он добавляется платформой автоматически.
Если сделать как вы предлагаете и при этом не выполнять реструктуризацию, уверен, вас ждёт неприятный сюрприз: платформа добавит разделитель в текст запроса, а т.к. вы предлагаете убрать его и из ключа кластерного индекса, СУБД придётся делать key lookup, чтобы достать нужное значение
Селективный индекс от 1С — что выберет MS SQL?