Курс по Оптимизации 1С — Занятие №5
В рамках этого занятия необходимо изучить следующие материалы.
Модуль 3. Причины медленной работы
- Глава 8. Анализ причин медленной работы и оптимизация системы
- Описание операторов плана запроса
- Чтение плана запроса
- Признаки неоптимального плана запроса
К сожалению, у Вас недостаточно прав для дальнейшего просмотра.
Если Вы приобрели курс, но еще не активировали токен — пожалуйста, активируйте доступ по инструкциям, высланным на Ваш email после покупки.
Если Вы не залогинены на сайте — залогиньтесь, вернитесь на эту страницу и обновите ее.
Если Вы залогинены, у Вас активирован токен доступа, но Вы все равно видите эту запись — напишите нам на e-mail поддержки.
Комментарии / обсуждение (217):
Комментарии закрыты
Андрей, Вы в курсе говорите об обязательном выполнении ежедневной обновлении статистики. Если я внимательно читал разделы курса, то основная причина замедления выполнения запросов происходит, когда оптимизатор выбирает оператор Nested Loops при большом количестве строк в таблице. Есть ли еще причины при которых замедляется производительность без статистики? Почему задаю такого рода вопрос, в нашей складской системе обновление статистики не дает очевидного значительного выигрыша в быстродействии ключевых операций.
…
(текст комментария доступен только участникам Мастер-группы)
Допустим, есть такой запрос:
«ВЫБРАТЬ ПЕРВЫЕ 5
| РС_Тест1.Число1,
| РС_Тест1.Число2,
| РС_Тест11.Число1 КАК Число11
|ИЗ
| РегистрСведений.РС_Тест1 КАК РС_Тест1
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РС_Тест1 КАК РС_Тест11
| ПО РС_Тест1.Число1 = РС_Тест11.Число1»
Если судить по плану запроса — то выбираются все записи из первой таблицы, все записи из второй, а потом происходит их соединение, а результат соединения уже «срезается» по первым пяти строкам.
А как в действительности отрабатывает такой запрос на уровне СУБД?
…
(текст комментария доступен только участникам Мастер-группы)
Андрей.
Кластерный индекс есть только в таблицах баз данных 1С, которые работают на MSSQL или он существует и в таблицах других баз данных 1С на других СУБД?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый вечер.
Андрей.
Просматривал видео «33.План запроса. Hash join. Описание» и «34.План запроса. Hash join. Пример».
Решил повторить на практике то, что увидел.
Сформировал запрос как у вас на видео. См файл «Текст_запроса.txt».
В профайлере выполняется план запроса через Nested Loops. Сначала я подумал что первые 500 маловато будет и увеличил сперва 5 000, а затем 50 000. И опять Nested Loops. См файлы «ПланЗапроса_500.txt», «ПланЗапроса_5000.txt» и «ПланЗапроса_50000.txt».
Далее затем я подумал что, у меня не актуальная статистика, и решил обновить также, как это сделали вы видеоуроке
«3. Невыполнение регламентных операций СУБД.Пример». См файл «Обновление статистики запросом.jpg».
Повторил эксперимент — тот же эффект.
Далее т.к. очень хотелось увидеть Hash join пошел несколько иным путем — поместил по 500 000 записей из того регистра во временные таблицы, а затем соединил их выбрав первые 500 записей. При это поля временных таблиц не индексировал. Как ни странно план запросам выполнился через Hash join.
См файлы «Текст_запроса_через_ВТ.txt» и «ПланЗапроса_Hash_Join.txt».
Далее т.к. очень хотелось увидеть Hash join c на том примере, который использовали вы, проиндексировал измерения того регистра — «Число2» и «Число3».
См файл «Индексы измерений регистра.jpg».
Повторил эксперимент когда запрос как из файла «Текст_запроса.txt» — опять Nested Loops.
Андрей, скажите пожалуйста почему так происходит, я что то делаю не так, вы что то сделали за кадром, разное поведение оптимизатора на разных версиях СУБД — у вас кажется MSSQL2008, а у меня MSSQL2014, или вообще это нормальная ситуация?
Особо интересует почему получилось воспроизвести Hash join на запросе с использованием временных таблиц?
На счет нормальной ситуации — не исключаю т.к. во всех случаях запросы выполняются довольно шустро, даже когда 50 000. Единственное что когда 50 000 консоль запросов некоторое время висит, хотя запрос в профайлере уже исполнился, видимо это передача большого объема данных с сервера на
клиент занимает в этом случае побольше времени.
См файлы «Консоль_выполнение_запроса_индексированы_измерения_.jpg» и «Консоль_выполнение_запроса_не_индексированы_измерения_.jpg».
Насчет времени для передачи данных я прав?
В архивном файле запакованы все файлы о которых я говорил выше по тексту.
…
(текст комментария доступен только участникам Мастер-группы)
«При выводе большого числа данных на экран платформа тратит много времени на сам вывод» — этот процесс может как оптимизироваться?
«Видимо у вас есть индекс по полю Число3 поэтому и нет соединения хэшированием, уберите индексирование этого измерения и должен появится хэш джойн» — а индекс каким образом мешает появлению хэш джойна, из-за того что порядковый номер измерения третий и он не может по этой использоваться?
…
(текст комментария доступен только участникам Мастер-группы)
Не совсем понял про table spool в приведенном примере.(Модуль 3, урок 52)
Index Scan выполнялся 6 раз и вернул 7146.
Я так понял, что table spool должен был поместить эти данные в таблицу tempdb.
Только почему же этот оператор выполнился 8 раз и вернул 9528 строк?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
В приложении план запроса, который выполняется медленно. Причем это удалось поймать этот запрос именно в момент медленного исполнения, т.к. в большинстве случаев он выполняется за приемлемое время.
На что я обратил внимание. Это строки 81 и 106, там где TotalSubtreeCost показывает относительно большие значения. Ещё я обратил внимание на строку 137, где большое количество показывает в колонке Rows.
Подскажите, пожалуйста, я в правильном направлении размышляю?
…
(текст комментария доступен только участникам Мастер-группы)
Заархивировал
…
(текст комментария доступен только участникам Мастер-группы)
Спасибо!
Андрей, подскажите, пожалуйста, а чем с точки зрения потенциальной проблемы отличаются строки 81 и 106?
…
(текст комментария доступен только участникам Мастер-группы)
Так и в 81 и в 106 ситуация такая же, как вы описали: сначали идет поиск по индексу, а потом частичное сканирование.
…
(текст комментария доступен только участникам Мастер-группы)
Ясно, спасибо!
ПС. почему-то нету кнопки ответить именно под вашим сообщением.
Доброго дня, Владимир!
Ветка разговора очень длинная, поэтому есть ограничения :))
И позвольте я тут же задам ещё один вопрос. Это тот же запрос, о котором мы начали говорит в этой ветке. Но план немного другой.
Строка 69. Операция Index Spool. TotalSubtreeCost = 1.95.
Вопрос. Т.к. все нижележащие операции в сумме собирают где-то около 0.1, я делаю вывод, что сам Index Spool в данном случае для СУБД получается относительно тяжелый. Что это за операция делает — я прочитал. Но я не понимаю почему она возникает в данном случае и почему создает столько нагрузки. А если ещё конкретнее спросить, то я не понимаю в этой строке:
Index Spool(SEEK:([T25].[_Fld10367RRef]=[qwertyui].[dbo].[_AccumRgT10372].[_Fld10367RRef] as [T25].[_Fld10367RRef] AND [@P65]=[@P65] AND [@P66]=[@P66] AND [@P68]=[@P68] AND [@P67]=[@P67]))
1) что такое [T25] ? Как узнать что это за таблица (или что?)?
2) как читать вот это [T25].[_Fld10367RRef]=[qwertyui].[dbo].[_AccumRgT10372].[_Fld10367RRef] as [T25].[_Fld10367RRef] ?
3) вот это [@P65]=[@P65] AND [@P66]=[@P66] AND [@P68]=[@P68] AND [@P67]=[@P67] я так понимаю СКЛ сервер сам себе что-то придумывает и особо вникать в это смысла нету?
Добавлю, что вот это [_AccumRgT10372].[_Fld10367RRef] — измерение Склад регистра. Если я правильно нашел запрос, к которому оно относится, то запрос имеет такой вид:
ВЫБРАТЬ
ПроданныйТовар.Номенклатура,
0,
-ПроданныйТовар.КоличествоОстаток
ИЗ
РегистрНакопления.ПроданныйТовар.Остатки(
,
Склад = &Склад
И …) КАК ПроданныйТовар
…
(текст комментария доступен только участникам Мастер-группы)
Не могу ответить под вашим сообщением выше, поэтому отвечаю под своим.
В приложении полный текст запроса. Запрос не оптимальный, но просто хочу на его примере разобраться.
1. Т25 это получается временная таблица ТаблицаТоваров? Определить это можно только «интуитивно» и сказать на 100% нельзя?
2. Опять таки, определить что это конкретно за поле из таблицы Т25 можно только интуитивно (посмотрев на текст запроса)?
4. Я его намеренно упростил, думал вам так будет проще.
…
(текст комментария доступен только участникам Мастер-группы)
Да, не внимательно прочитал.
Посмотрел текст запроса в терминах SQL, так таблица T25 — это псевдоним таблицы _AccumRgT10372.
Но условии SEEK получается что «один = один». Т.е. непонятно что с чем сравнивается, т.к. сравнивается поле с собой же.
Если посмотреть в SEEK операции не Index Spool, то там видно какой объект используется и какое условие. А тут непонятно ничего. Прикладываю текст запроса в SQL.
…
(текст комментария доступен только участникам Мастер-группы)
Понятно. Спасибо за ответы!
Урок №46.
Пытаюсь разобраться почему при выполнении оператора Index Scan отсортировано = false и как вообще можно ускорить этот запрос.
Если в запросе из примера ВНУТРЕННЕЕ соединение заменить на ЛЕВОЕ, то все понятно: мы выбираем первые 5 строк, количество предполагаемых строк = фактическому, далее Nested Loops соединяет таблицы между собой.
Также, если при ВНУТРЕННЕМ соединении вместо условия Число2=Число1, поставить условие Число1 = Число1, то при выполнении Index Scan есть сортировка и возвращается всего 5 строк
Но с ВНУТРЕННИМ соединением и условием Число2 = Число 1 не понятно, почему Сортировка = false и почему возвращается 2 млн. строк вместо 5. Пробовал добавлять индекс для измерения Число2, а также менять порядок измерений в регистре, что бы был кластерный индекс Число2+Число3+Число1, все равно сортировки нет и возвращается всегда много строк. Объясните, пожалуйста, почему?
…
(текст комментария доступен только участникам Мастер-группы)
Да, я все понял. Спасибо.
Добрый день.
Урок 40. Вы говорите что оператор Clustered Index Seek выполнился 4 раза и вернул 5 строк. 3 раза вернул по 1 строке и 4 раз — 2 строки. Почему именно так, а не 1 раз вернул 2 строки, а потом по одной?
…
(текст комментария доступен только участникам Мастер-группы)
Так же поэкспериментировав с запросом из 48 видео, где разбирается неоптимальность Scan, понял следующее (просьба подтвердить или опровергну): в скуле в любом случае (указан ли индекс по какому-либо измерению и их нет вовсе) при создании РС создается составной индекс по всем измерениям, причем видимо измерения туда входят в соответствии с порядком их следования в структуре таблицы 1С, т.е. у нас Число1, Число2, Число3 и индекс получился Число1+Число2+Число3, если в структуре 1С был бы порядок Число3, Число1, Число2, то индекс был бы Число3+Число1+Число2, так???
Если брать структуру регистра? как у нас в примере РС_Тест1, то получается:
1) Условие в запросе 1С: Число1 = &П1 => index seek и используется предикат seek(Число1 = &П1)
2) Условие в запросе 1С: Число1 = &П1 И Число2 = &П2 => index seek и используется предикат seek(Число1 = &П1 И Число2 = &П2)
3) Условие в запросе 1С: Число1 = &П1 И Число3 = &П3 => index seek и используется уже 2 предиката seek(Число1 = &П1) И предикат where(Число2 = &П2)
4) Условие в запросе 1С: Число2 = &П2 И Число3 = &П3 => index scan и используется предикат where(Число2 = &П2 И Число3 = &П3)
Получается порядок следования измерений в регистре важно и влияет на оператор в плане запроса, т.е. первым нужно размещать самое «популярное» измерение и его можно не помечать как индексированное, т.к. оно и так будет в индексе первым и в случае отбора по нему, будет выполняться index seek?
Мой пример 3, где используется условие «Число1 = &П1 И Число3 = &П3» и соответственно 2 предиката: seek() и where(), насколько использование двух предикатов увеличивает время работы, оптимально ли это или на это можно закрывать глаза? Я так понял, что where() используется уже после выборки индекса, т.е. он потом своего рода index scan делает?
———
Так же сделал похожий РС из 3х измерений: Число1, Число2, Число3, хотел посомтреть как создадутся индексы, думы про порядок следования полей подтвердилсяЮ однако заметил, что скул в этом случае почему то сделал кластерным индексом не тот, у которого период на первом месте (т.е. как у вашего РС_Тест1), а сделал второй индекс, который начинается с измерений и заканчивается периодом, почему так? Как скуль решает какой индекс делать кластерным в случае 1С?
———
и еще один по 50 видео, скул 2014 все же дает о себе знать, т.к. планы запроса иногда получаются отличными от ваших. запрос, который написан, чтобы показать seek…where, у меня по факту на SQL получился другим:
exec sp_executesql N’SELECT
T1.Fld20RRef,
T1.Fld21Balance_
FROM (SELECT
T2._Fld20RRef AS Fld20RRef,
CAST(SUM(T2._Fld21) AS NUMERIC(30, 8)) AS Fld21Balance_
FROM dbo._AccumRgT22 T2 WITH(NOLOCK)
WHERE T2._Period = @P1 AND ((T2._Fld404RRef = @P2)) AND (T2._Fld21 @P3) AND (T2._Fld21 @P4)
GROUP BY T2._Fld20RRef
HAVING (CAST(SUM(T2._Fld21) AS NUMERIC(30, 8))) @P5) T1′,
N’@P1 datetime,@P2 varbinary(16),@P3 numeric(10),@P4 numeric(10),@P5 numeric(10)’,
‘5999-11-01 00:00:00’,0x00000000000000000000000000000000,0,0,0
План еще более странный, а именно условия в Clustered Index Seek, которые выполняются над ресурсом «количество».
План этого запроса, можете так же прокомментировать? Особенно Clustered Index Seek и условия Where в нем, чем оно может быть объяснено? И я так понял 2014ый теперь для агрегации данных запроса использует конструкцию Hash Match Aggregate…
План: https://www.dropbox.com/s/jwhlj92alfge4wj/Test_SelectPlan_Question_2.SQLPlan?dl=0
…
(текст комментария доступен только участникам Мастер-группы)
…
(текст комментария доступен только участникам Мастер-группы)
Спасибо, уяснил
Добрый вечер, Андрей!
Получается длиннопост, с надеждой на длиноответ. Есть несколько вопросов, в частности по видео 43,44 (вроде бы некоторые аспекты тут уже запрашивались, но я повторюсь),т.к. план запроса у меня получается другим (приложил его), хотелось бы выяснить некоторые непонятные моменты. Вообще как-то странно выглядит план, сначала посмотрел текстовый план и подумал, что все начинается с поиска по Товарам, т.к. этот элемент максимальной вложенности, но логика подсказывает, что выполняется все же так:
1. Выполняется Index Scan по регистру, используется индекс ByDims880 (у которого на пером месте Составной Тип), выбираются поля «[T1].[_Fld777_TYPE], [T1].[_Fld777_RTRef], [T1].[_Fld777_RRRef]».
«Арт подготовка»: у регистра 3 индекса:
ByPeriod
Период + Число1 + Число2 + Число3 + СоставнойТип (Period + Fld751 + Fld752 + Fld753 + Fld777)
ByDims
Число1 + Число2 + Число3 + СоставнойТип + Период (Fld751 + Fld752 + Fld753 + Fld777 + Period)
ByDims880
СоставнойТип + Период + Число1 + Число2 + Число3 (Fld777 + Period + Fld751 + Fld752 + Fld753)
Вопросы:
1.1 Хотя мы пометили, как индексированный только одно измерение «Составной тип». Получается, что вне зависимости от того, делаем мы какое-либо измерение индексированным, скуль для периодических РС все равно делает хотя бы 2 индекса:
— по периоду (плюс все осальные измерения) и делает его кластерным
— по всем измерениям и добавляет период в конце
Я правильно понял?
1.2 Вопрос наверное глупый, но хочется понять правила формирования этих индексов, почему по всем полям (я думал только кластерный индекс содержит все поля таблицы причем физически содержит, а остальные каждый свое поле индексирует, как бы ссылаясь на поля таблицы) и на что влияет порядок, как он используется, я так полагаю на формирования нормализованного дерева? Как он физически вгялдит этои ндекс из нескольких полей кластеризованный и некластеризованный? Как-то доходчиво не могу найти.
1.3 Не могли бы описать выбираемые поля: «[T1].[_Fld777_TYPE], [T1].[_Fld777_RTRef], [T1].[_Fld777_RRRef]»? особенно интересуют последние два, первое понятно содержит информацию о типе, хотя в структуре таблицы таких полей не нашел.
2. Далее Выполняется Clustered Index Seek по Товарам по кластерному индексу PK___Referen__AC8ED0C47D92863E, выбирается КОд, по условию «SEEK:([T2].[_IDRRef]=[Abramenko_Plazma_Test].[dbo].[_InfoRg750].[_Fld777_RRRef] as [T1].[_Fld777_RRRef]». т.е. фактически система использует данные из регистра, причем с учетом 100 записей и, что тип должен быть «из справочников», об этом можно судить по возвращаемому количеству строк = 0, хотя предполагаемое количество = 1 и в регистре есть строка с значением из Товаров, однако, шельмец, понимает, что из регистра она выбрана не будет и откидывает.
Вопросы:
2.1 Почему тут Seek, а не Scan? Как этот оператор без соединения узнает о строках, выбранных из регистра, еще до соединения и использует их в ключе поиска.
2.2 Как в условия Seek используются поля из Регистра? Я считал, что оператор работает с какой-то одной таблицей как бы изолировано, а уже потом их соединяет и в этот момент накладывает условия. А тут получается, что у него есть вариант, когда он до Nested Loops, накладывает условие, которое уже включает поиск, исходя из результата другого оператора (верхнего), т.е. «предчувствуя», что результат будет маленьким и можно использовать Nested Loops. Хотя текстовому плану это сложно сказать. Хотя может я ошибаюсь и это работает как-то не так.
3. Далее на результат Clustered Index Seek накладывается оператор «Фильтр», который пока не описывался в курсах, с непонятным условием исключительно по регистру, а не по Товарам: WHERE:(STARTUP EXPR([Abramenko_Plazma_Test].[dbo].[_InfoRg750].[_Fld777_TYPE] as [T1].[_Fld777_TYPE]=0x08 AND [Abramenko_Plazma_Test].[dbo].[_InfoRg750].[_Fld777_RTRef] as [T1].[_Fld777_RTRef]=0x00000007))
3.1 Что это за «фильтр» и как работает? Вроде бы по стрелке через него проходят данные из выборки Товаров, но условие не содержит полей из Товаров вовсе.
4. остальные конструкции идут так же, как у вас в примере.
—
И несколько рандомных вопросов по теории:
5. Как по бытовому определить смысл фразы «Искать предикаты», я понимаю, что в нем пишется ключ поиска оператора Seek, т.е. то условие по которому будут отобраны записи. Что в терминах скуля означает предикат?
6. При операторе операторы соединения:
6.1 всегда ли верхняя таблица является ведущей таблицей или как определить ведущую в этом случае? 6.2 При соединениях «Левое\Правое» можно сказать, что соблюдается порядок «левая = верхний оператор, входящий в соединение» и «правая = нижний оператор, входящий в соединение»?
Спасибо заранее за ответы
…
(текст комментария доступен только участникам Мастер-группы)
1.2 А если есть Поле1, Поле2, Поле3. Индексировано скажем Поле2, т.е. мы имеем индексы:
— Кластерный: Поле1+Поле2+Поле3
— Составной: Поле2+Поле1+Поле3
Если я задам условие по первому полю отдельно, то будет использоваться кластерный, если по второму отдельно, то составной, а если отдельно по Полю3 какой индекс будет использоваться, кластерный? сканировать или искать?
И вдогонку, в событиях профайлера мы добавили события: SQL Batchcompleted и RPC Completed. Запрос скрывается то в одном, то в другом, от чего это зависит?
…
(текст комментария доступен только участникам Мастер-группы)
Ссылка на архив с базой и планом запроса: https://www.dropbox.com/s/w3tx23ulfcrp6b3/%D0%9C%D0%BE%D0%B4%D1%83%D0%BB%D1%8C3_%D0%92%D0%B8%D0%B4%D0%B5%D0%BE_43_44.rar?dl=0
Запрос в 1С:
ВЫБРАТЬ ПЕРВЫЕ 100
РС_Тест1.СоставнойТип.Код
ИЗ
РегистрСведений.РС_Тест1 КАК РС_Тест1
SQL запрос:
SELECT TOP 100
CASE WHEN T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = 0x00000007
THEN T2._Code WHEN T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = 0x0000000B
THEN T3._Code ELSE CAST(NULL AS NVARCHAR(9)) END
FROM dbo._InfoRg750 T1 WITH(NOLOCK)
LEFT OUTER JOIN dbo._Reference7 T2 WITH(NOLOCK)
ON T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = 0x00000007 AND T1._Fld777_RRRef = T2._IDRRef
LEFT OUTER JOIN dbo._Reference11 T3 WITH(NOLOCK)
ON T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = 0x0000000B AND T1._Fld777_RRRef = T3._IDRRef
*1С 8.3, СКЛ 2014
…
(текст комментария доступен только участникам Мастер-группы)
Как узнать, когда в последний раз в базе обновлялась статистика?
…
(текст комментария доступен только участникам Мастер-группы)
Везде сказано, что большое количество строк (>1) операторе Nested Loops это плохо. Причина накладные расходы на выполнение, но это не запрос в цикле как в 1С, чтобы была передача данных между серверами и т.д. Операторы (ведущий и ведомый) в Nested Loops возращают наборы данных, без повторного выполнения запроса в цикле. Подскажите, пожалуйста, какой именно состав этих накладных расходов?
…
(текст комментария доступен только участникам Мастер-группы)
Вопрос про логическое чтение. В статье https://technet.microsoft.com/ru-ru/library/ms191475(v=sql.105).aspx про логическое чтение говорится следующее: «Логическое чтение выполняется каждый раз, когда компонент Database Engine запрашивает страницу из буферного кэша. Если в этот момент страница не находится в кэше, то сначала при помощи операции физического чтения она копируется в него с диска.»
Правильно ли я понимаю, что по колонке Reads в SQL Profiler мы не можем точно сказать, была ли нагрузка на диск, так как данные могут считаться с кэша, тогда не понятно, почему нет такого показателя как физическое чтение.
…
(текст комментария доступен только участникам Мастер-группы)
Здравствуйте. В уроке 48. Признаки не оптимального запроса. Scan. Пример. Выполняется запрос по регистру сведений «Тест1». Когда условие на измерение «Число2» выполняется Index Scan и в качестве объекта сканирования выбирается InfoRg750_ByDims880_RTNNN. Это индекс (Составной тип+период+Число1+Число2+…).
1. Почему оптимизатор не выбрал другой индекс для сканирования? К примеру ByDims (Число1+Число2+…).
2. Почему в данном случае все же был Index scan с ипользованием WHERE, а скажем не Table Scan с тем же WHERE, если измерение «Число2» все равно не проиндексировано (остальные индексы же бесполезны)?
3. Что означают постфиксы у индексов в плане запросов? К примеру индекс ByDims880, но он указан в виде _ByDims880_RTNNN.
…
(текст комментария доступен только участникам Мастер-группы)
Если не ошибаюсь суффикс RTNNN означает типы данных которые входят в состав индекса, T — время, N — число…
…
(текст комментария доступен только участникам Мастер-группы)
Почему выполнив запрос на получение кода из составного типа составного типа регистра сведений «Тест1» (выбрать первые 100) получается странное количество записей? В плане запроса видно, что из справочника «Склады» получается 400 строк (реальное количество всего 4). А из справочника «Товары» 0 строк, хотя в регистре в последних записях по времени встречается один раз товар и казалось бы должна была быть возвращена одна строка.
…
(текст комментария доступен только участникам Мастер-группы)
43 (44) План запроса. Пример с составными типами.
…
(текст комментария доступен только участникам Мастер-группы)
Здравствуйте. Подскажите, пожалуйста, по операторут «TOP». Во многих видео уроках встречается использование оператора «TOP», по плану запроса видно, что он последний. Ранее него выполняется или «Index Scan» и «Clustered Index Seek», результат соединяется через «Nested Loops». Уже по количеству фактических строк самых первых операторов (к примеру 5) видно, что происходит отсечение строк по значению предиката оператора «TOP» (значение 5). Как тогда работает оператор «TOP»? Он сразу накладывает какие ограничения на другие операторы? Если, нет, то почему не происходит с начала получения миллиона строк, а только потом их отсечение?
В некотороых случаях сначали все отбирается (пример запроса из видео 42. План запроса. Пример соединения нескольких таблиц), а в некоторых почему-то только 5 записей (29. План запроса. Nested Loops).
…
(текст комментария доступен только участникам Мастер-группы)
Здравствуйте.
Разбираю на тестовых данных как 1С (конфигурация УПП) при использовании RLS формирует SQL запрос, а также пытаюсь разобрать план этого запроса (во вложении) — смотрю формы списка в толстом клиенте, обычное приложение. При этом понял то, что при таких конструкциях в запросах RLS как в УПП, 1С формирует запрос SQL при использовании оператора EXISTS (результат подзапроса зависит от каждой строки внешнего запроса).
При этом у меня появились следующие вопросы:
1. Правильно я понимаю, что использование оператора EXISTS в SQL (в том случае, когда результат подзапроса зависит от каждой строки внешнего запроса) — это коррелированные подзапросы, что приводит к запросам в цикле и что соответственно является неоптимальным? Также использование оператора EXISTS в случае, когда результат подзапроса не зависит от каждой строки внешнего запроса не является коррелированным и соответственно скорее всего будет выполнен оптимально?
2. В плане выполнения запроса я увидел логический оператор Lazy Spool, который выполняется оператором Row Count Spool. Что эти операторы означают и для чего используются в СУБД?
3. Если посмотреть план выполнения запроса, то видно что выборка из таблицы документов _Document556 выполняется путем сканирования некластерного индекса _Documen556_ByDocDate_TRL, при этом никаких условий на отбор нет (предикатов нет). Почему в результате первого выполнения этого оператора возвращается всего 217 строк, ведь в таблице намного больше записей? При этом если этот запрос выполнить второй раз и последующий то возвращается еще меньше — 41 запись, почему так? (на общий результат запроса наложен фильтр ТОП 40). Если на общий результат наложить фильтр ТОП 43 и выше — то возвращается вся таблица 1845 строк.
4. Я правильно понимаю, что по данному плану запроса, все что находится ниже оператора Lazy Spool (Row Count Spool) будет выполняться столько раз сколько записей вернет выше стоящий оператор Nested Loops(Left Semi Join, OUTER REFERENCES:([T1].[_Fld15101RRef]))? выполнение всех этих операторов — это как раз и есть запросы в цикле
5. Если прокрутить форму списка в 1С и заново посмотреть запрос SQL то видно что к основному selectу добавляется опция OPTION (FAST 1). Что означает опция FAST 1?
…
(текст комментария доступен только участникам Мастер-группы)
Приложил, отдельно при открытии формы списка и отдельно при прокрутке списка.
…
(текст комментария доступен только участникам Мастер-группы)
Спасибо за ответы.
По ответу на вопрос 3 «…Для ответа нужно провести подробный анализ, а для этого нужна база с четким воспроизведением….» — DT-шник базы и видео с воспроизведением ниже по ссылкам.
http://ifolder.com.ua/gnpl70169tpp.html видео
http://ifolder.com.ua/x9u04tgg78k1.html база
Между тем УПП используется на многих предприятиях и поэтому если RLS в УПП работают неоптимально хотелось бы понять конкретно в чем причина и можно ли как-то оптимизировать переписав запросы в RLS.
…
(текст комментария доступен только участникам Мастер-группы)
https://yadi.sk/d/f7K4bCfoeNTFF
…
(текст комментария доступен только участникам Мастер-группы)
SQL Server 2008
выгрузка базы sql
https://yadi.sk/d/f0cVwJ31ePWQp
…
(текст комментария доступен только участникам Мастер-группы)
Здравствуйте.
Подскажите пожалуйста, почему по запросу
exec sp_executesql N’SELECT TOP 35
T2._IDRRef,
T2._Marked,
T2._IsMetadata,
T2._Description,
T2._Code
FROM dbo._Reference27278 T2 WITH(NOLOCK)
WHERE T2._Description < @P1
ORDER BY (T2._Description) DESC, (T2._IDRRef) DESC
OPTION (FAST 1)',N'@P1 nvarchar(4000)',N'Номер = 105'
срабатывает план с соединением таблиц вложенными циклами
Rows Executes StmtText
—- ——— ———
5 1 Top(TOP EXPRESSION:((35)))
35 1 |—Nested Loops(Inner Join, OUTER REFERENCES:([T2].[_IDRRef]))…
5 1 |—Index Seek(OBJECT:([UPP].[dbo].[_Reference27278].[_Refer2…
35 35 |—Clustered Index Seek(OBJECT:([UPP].[dbo].[_Reference27278]…
Ведь видно что сам запрос строится по одной таблице, для которого на мой взгляд достаточно операторов Index Seek и Top(TOP EXPRESSION:((35))). Как определить для этого запроса зачем нужен оператор Clustered Index Seek в соединении через Nested Loops?
…
(текст комментария доступен только участникам Мастер-группы)
спасибо
Здравствуйте.
1. Где можно более подробно почитать про текстовые планы запросов: про Argument, DefinedValues?
Например, анализирую план и мне не понятно и хотелось бы четко понимать:
— что для оператора Compute Scalar означает аргумент DEFINE:(([Expr1022],[Expr1023],[Expr1021])=GetRangeWithMismatchedTypes([@P2],NULL,(22)))?
— что для оператора Concatenation означает DefinedValues [Expr1027] = ([Expr1022], [Expr1025]), [Expr1028] = ([Expr1023], [Expr1026]), [Expr1029] = ([Expr1021], [Expr1024])?
2. Как оптимизатор определяет в каком случае чтобы по запросу обработать отбор (н-р, WHERE T4._Period >= @P2 AND T4._Period < @P3 AND T4._Active = 0x01) он будет использовать для этого оператор соединения, а в каком случае (н-р, HAVING (CAST(SUM(T2.Fld21Balance_) AS NUMERIC(36, 8))) 0.0)) он будет для этого использовать оператор фильтрации?
…
(текст комментария доступен только участникам Мастер-группы)
спасибо
Почему оптимизатор может ошибиться, выбрав, например, Nested Loops? из-за неверной статистики?
…
(текст комментария доступен только участникам Мастер-группы)
Почему в Nested Loops в ведомой таблице количество обращений меньше, чем в ведущей?
…
(текст комментария доступен только участникам Мастер-группы)
и еще два вопроса:
— почему некоторые тексты запросов выведены в черном цвете, а некоторые в красном?
— «платформа сама определила», что если есть код…Вопрос: на стороне 1с сервера что выполняется?
…
(текст комментария доступен только участникам Мастер-группы)
По первому вопросу: интересует механизм подсветки текста запроса в sql profiller. Почему некоторые конструкции теста запроса высвечены красным цветом, а некоторые черным?
По второму вопросу: на стороне 1с сервера выполняется только компиляция запросов?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день.
Вопрос: графический план запроса мы читаем справа налево и СВЕРХУ вниз. Текстовый: справа налево и СНИЗУ вверх?
…
(текст комментария доступен только участникам Мастер-группы)
Могли бы вы объяснить чем отличаются RPC:Completed и SP:StmtCompleted т.к. запрос SQL показывают оба но вот столбец Duration отличается кардинально
…
(текст комментария доступен только участникам Мастер-группы)
Смотрю сейчас запросы из приложения. И практика не совсем совпадает с теорией. База из курса, все статистика обновлена, индексы перестроены.
Запрос:
ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ГДЕ
НЕ Товары.Ссылка В
(ВЫБРАТЬ
ОстаткиТоваров.Товар
ИЗ
РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров)
Duration = 19
План запроса:
Hash Match(Left Anti Semi Join) rows 0
|—Index Scan rows 104
|—Clustered Index Scan rows 9507
Запрос через объединение, который теоретически должен работать быстрее:
ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров
ПО Товары.Ссылка = ОстаткиТоваров.Товар
ГДЕ
ОстаткиТоваров.Товар Есть Null
Duration = 25
План:
Filter(WHERE:(IS NULL)) rows 0
|—Hash Match(Left Outer Join) rows 9507
|—Index Scan rows 1004
|—Clustered Index Scan rows 9507
В итоге выходит что при НЕ В план запроса более оптимальный и более быстрый.
п.с. Еще возник вопрос по поводу индексации временных таблиц, по опыту в большинстве случаев запрос с индексацией работает дольше, т.к. на саму индексацию тратится не мало времени. Следует делать эту индексацию?
…
(текст комментария доступен только участникам Мастер-группы)
В процессе изучения возник такой вопрос:
При выполнении запроса
ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.ВидТовара = &ВидТовара
Идет поиск по кластерному индексу с доп. предикатом
В запросе же
ВЫБРАТЬ
Товары.Ссылка
ИЗ
Справочник.Товары КАК Товары
ГДЕ
Товары.ВидТовара = &ВидТовара
Идет поиск по индексу. Можете объяснить почему? Ведь реквизит ВидТовара проиндексирован, но поиск идет по кластерному индексу в первом варианте
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день! Анализирую графический план (на скриншоте), вижу, что предполагаемое количество строк больше, фактического. В уроках было озвучено, что при проблемной статистике должна быть обратная ситуация! Скажите, такой разлет показателей, это на сколько плохо? (В запросе получаю остатки из РН.ТоварыНаСкладах древней УПП). Спасибо!
…
(текст комментария доступен только участникам Мастер-группы)
Спасибо! Тут, как я понимаю, частичное сканирование индекса. Увы, есть зазор в индексе, использую параметр, наврядли решусь что-либо изменять, так как база ~500 ГБ, чревато! :|
…
(текст комментария доступен только участникам Мастер-группы)
В видео Вы рассказываете все очень доступно.
Но на реальных данных все несколько отличается. Подскажите как правильно анализировать медленный такой запрос:
1. Контекст:
{Отчет.ОборотноСальдоваяВедомостьПоСчету.Форма.ФормаОтчета}/{КоманднаяПанель : ДействияФормыОсновные}/{ДействиеСформировать}
Отчет.ОборотноСальдоваяВедомостьПоСчету.Форма.ФормаОтчета.Форма : 97 : ОбновитьОтчет();
Отчет.ОборотноСальдоваяВедомостьПоСчету.Форма.ФормаОтчета.Форма : 31 : СформироватьОтчет(ЭтаФорма.ЭлементыФормы.Результат, ДанныеРасшифровки, Ложь, , ВыводитьПолностью);
Отчет.ОборотноСальдоваяВедомостьПоСчету.МодульОбъекта : 202 : СтандартныеОтчеты.ВывестиОтчет(ЭтотОбъект, Результат, ДанныеРасшифровки, ВыводВФормуОтчета, ВнешниеНаборыДанных, Истина, НастройкаКомпоновкиДанных);
ОбщийМодуль.СтандартныеОтчеты.Модуль : 2772 : ПроцессорВывода.Вывести(ПроцессорКомпоновки, Истина);
{Отчет.ОборотноСальдоваяВедомостьПоСчету.Форма.ФормаОтчета}/{КоманднаяПанель : ДействияФормыОсновные}/{ДействиеСформировать}
Отчет.ОборотноСальдоваяВедомостьПоСчету.Форма.ФормаОтчета.Форма : 97 : ОбновитьОтчет();
Отчет.ОборотноСальдоваяВедомостьПоСчету.Форма.ФормаОтчета.Форма : 31 : СформироватьОтчет(ЭтаФорма.ЭлементыФормы.Результат, ДанныеРасшифровки, Ложь, , ВыводитьПолностью);
Отчет.ОборотноСальдоваяВедомостьПоСчету.МодульОбъекта : 202 : СтандартныеОтчеты.ВывестиОтчет(ЭтотОбъект, Результат, ДанныеРасшифровки, ВыводВФормуОтчета, ВнешниеНаборыДанных, Истина, НастройкаКомпоновкиДанных);
ОбщийМодуль.СтандартныеОтчеты.Модуль : 2772 : ПроцессорВывода.Вывести(ПроцессорКомпоновки, Истина);
2. Текст запроса:
‘SELECT
T1.AccountRRef,
T1.Value1_TYPE,
T1.Value1_RTRef,
T1.Value1_RRRef,
T1.Value2_TYPE,
T1.Value2_RTRef,
T1.Value2_RRRef,
T1.Fld13171RRef,
T1.Fld13995RRef,
T1.Fld13173InitialBalanceDt_,
T1.Fld13173InitialBalanceCt_,
T1.Fld13173TurnoverDt_,
T1.Fld13173TurnoverCt_,
T1.Fld13173FinalBalanceDt_,
T1.Fld13173FinalBalanceCt_,
T1.Fld13175InitialBalanceDt_,
T1.Fld13175InitialBalanceCt_,
T1.Fld13175TurnoverDt_,
T1.Fld13175TurnoverCt_,
T1.Fld13175FinalBalanceDt_,
T1.Fld13175FinalBalanceCt_,
CASE WHEN (CASE WHEN T1.Fld13995RRef IS NOT NULL THEN 0x0000004F END = 0x0000004F) THEN T1.Fld13995RRef ELSE T1.Fld13995RRef END,
CASE WHEN (CASE WHEN T1.Fld13995RRef IS NOT NULL THEN 0x0000004F END = 0x0000004F) THEN T26._Description ELSE CAST(NULL AS NVARCHAR(50)) END,
CASE WHEN (CASE WHEN T1.Fld13995RRef IS NOT NULL THEN 0x0000004F END = 0x0000004F) THEN 0.0 ELSE -1.0 END,
T1.AccountRRef,
T27._Code,
T27._Kind,
T27._Fld13994,
T27._OrderField
FROM (SELECT
T2.Fld13995RRef AS Fld13995RRef,
T2.Fld13171RRef AS Fld13171RRef,
T2.Value1_TYPE AS Value1_TYPE,
T2.Value1_RTRef AS Value1_RTRef,
T2.Value1_RRRef AS Value1_RRRef,
T2.AccountRRef AS AccountRRef,
T2.Value2_TYPE AS Value2_TYPE,
T2.Value2_RTRef AS Value2_RTRef,
T2.Value2_RRRef AS Value2_RRRef,
CASE WHEN CAST(SUM(T2.Fld13173TurnoverDt_) AS NUMERIC(27, 8)) IS NULL THEN 0.0 ELSE CAST(SUM(T2.Fld13173TurnoverDt_) AS NUMERIC(27, 8)) END AS Fld13173TurnoverDt_,
CASE WHEN CAST(SUM(T2.Fld13173TurnoverCt_) AS NUMERIC(27, 8)) IS NULL THEN 0.0 ELSE CAST(SUM(T2.Fld13173TurnoverCt_) AS NUMERIC(27, 8)) END AS Fld13173TurnoverCt_,
CASE WHEN CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) IS NULL THEN 0.0 WHEN MAX(T25._Kind) = 0.0 OR MAX(T25._Kind) = 2.0 AND CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) > 0.0 THEN CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) ELSE 0.0 END AS Fld13173InitialBalanceDt_,
CASE WHEN CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) IS NULL THEN 0.0 WHEN MAX(T25._Kind) = 1.0 OR MAX(T25._Kind) = 2.0 AND CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) 0.0 THEN CAST(SUM(T2.Fld13173Balance_ + T2.Fld13173FinalTurnover_) AS NUMERIC(34, 8)) ELSE 0.0 END AS Fld13173FinalBalanceDt_,
CASE WHEN CAST(SUM(T2.Fld13173Balance_ + T2.Fld13173FinalTurnover_) AS NUMERIC(34, 8)) IS NULL THEN 0.0 WHEN MAX(T25._Kind) = 1.0 OR MAX(T25._Kind) = 2.0 AND CAST(SUM(T2.Fld13173Balance_ + T2.Fld13173FinalTurnover_) AS NUMERIC(34, 8)) 0.0 THEN CAST(SUM(T2.Fld13175Balance_) AS NUMERIC(32, 8)) ELSE 0.0 END AS Fld13175InitialBalanceDt_,
CASE WHEN CAST(SUM(T2.Fld13175Balance_) AS NUMERIC(32, 8)) IS NULL THEN 0.0 WHEN MAX(T25._Kind) = 1.0 OR MAX(T25._Kind) = 2.0 AND CAST(SUM(T2.Fld13175Balance_) AS NUMERIC(32, 8)) 0.0 THEN CAST(SUM(T2.Fld13175Balance_ + T2.Fld13175FinalTurnover_) AS NUMERIC(33, 8)) ELSE 0.0 END AS Fld13175FinalBalanceDt_,
CASE WHEN CAST(SUM(T2.Fld13175Balance_ + T2.Fld13175FinalTurnover_) AS NUMERIC(33, 8)) IS NULL THEN 0.0 WHEN MAX(T25._Kind) = 1.0 OR MAX(T25._Kind) = 2.0 AND CAST(SUM(T2.Fld13175Balance_ + T2.Fld13175FinalTurnover_) AS NUMERIC(33, 8)) < 0.0 THEN -CAST(SUM(T2.Fld13175Balance_ + T2.Fld13175FinalTurnover_) AS NUMERIC(33, 8)) ELSE 0.0 END AS Fld13175FinalBalanceCt_,
MAX(T25._Kind) AS AccKind_
FROM (SELECT
T3._Fld13995RRef AS Fld13995RRef,
T3._Fld13171RRef AS Fld13171RRef,
T3._Value1_TYPE AS Value1_TYPE,
T3._Value1_RTRef AS Value1_RTRef,
T3._Value1_RRRef AS Value1_RRRef,
T3._AccountRRef AS AccountRRef,
T3._Value2_TYPE AS Value2_TYPE,
T3._Value2_RTRef AS Value2_RTRef,
T3._Value2_RRRef AS Value2_RRRef,
T3._Fld13173 AS Fld13173Balance_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld13173FinalTurnover_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld13173TurnoverDt_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld13173TurnoverCt_,
T3._Fld13175 AS Fld13175Balance_,
CAST(0.0 AS NUMERIC(15, 3)) AS Fld13175FinalTurnover_,
CAST(0.0 AS NUMERIC(15, 3)) AS Fld13175TurnoverDt_,
CAST(0.0 AS NUMERIC(15, 3)) AS Fld13175TurnoverCt_
FROM dbo._AccRgAT213193 T3 WITH(NOLOCK)
INNER JOIN #tt3 T4 WITH(NOLOCK)
ON T4._IDRRef = T3._AccountRRef AND T4._Cnt = ?
WHERE ((T3._Fld13171RRef = ?)) AND T3._Period = ? AND (T3._Fld13173 ? OR T3._Fld13175 ?)
UNION ALL SELECT
T5._Fld13995DtRRef AS Fld13995RRef,
T5._Fld13171RRef AS Fld13171RRef,
T7._Value_TYPE AS Value1_TYPE,
T7._Value_RTRef AS Value1_RTRef,
T7._Value_RRRef AS Value1_RRRef,
T5._AccountDtRRef AS AccountRRef,
T9._Value_TYPE AS Value2_TYPE,
T9._Value_RTRef AS Value2_RTRef,
T9._Value_RRRef AS Value2_RRRef,
CAST(-T5._Fld13173 AS NUMERIC(21, 2)) AS Fld13173Balance_,
T5._Fld13173 AS Fld13173FinalTurnover_,
T5._Fld13173 AS Fld13173TurnoverDt_,
CAST(0.0 AS NUMERIC(15, 2)) AS Fld13173TurnoverCt_,
CAST(-T5._Fld13175Dt AS NUMERIC(21, 3)) AS Fld13175Balance_,
T5._Fld13175Dt AS Fld13175FinalTurnover_,
T5._Fld13175Dt AS Fld13175TurnoverDt_,
CAST(0.0 AS NUMERIC(15, 3)) AS Fld13175TurnoverCt_
FROM dbo._AccRg13170 T5 WITH(NOLOCK)
LEFT OUTER JOIN dbo._Acc12_ExtDim13138 T6 WITH(NOLOCK)
ON T6._Acc12_IDRRef = T5._AccountDtRRef AND T6._LineNo = ?
LEFT OUTER JOIN dbo._AccRgED13197 T7 WITH(NOLOCK)
ON T7._RecorderTRef = T5._RecorderTRef AND T7._RecorderRRef = T5._RecorderRRef AND T7._LineNo = T5._LineNo AND T7._Period = T5._Period AND T7._Correspond = ? AND T7._KindRRef = T6._DimKindRRef AND T7._Period >= ? AND T7._Period = ? AND T9._Period = ? AND T5._Period = ? AND T12._Period = ? AND T14._Period = ? AND T10._Period ? AND T17._Period ? AND T19._Period ? AND T15._Period ? AND T22._Period ? AND T24._Period ? AND T20._Period < ?) T2
INNER JOIN dbo._Acc12 T25 WITH(NOLOCK)
ON T25._IDRRef = T2.AccountRRef
GROUP BY T2.Fld13995RRef,
T2.Fld13171RRef,
T2.Value1_TYPE,
T2.Value1_RTRef,
T2.Value1_RRRef,
T2.AccountRRef,
T2.Value2_TYPE,
T2.Value2_RTRef,
T2.Value2_RRRef
HAVING (CASE WHEN CAST(SUM(T2.Fld13173TurnoverDt_) AS NUMERIC(27, 8)) IS NULL THEN ? ELSE CAST(SUM(T2.Fld13173TurnoverDt_) AS NUMERIC(27, 8)) END) ? OR (CASE WHEN CAST(SUM(T2.Fld13173TurnoverCt_) AS NUMERIC(27, 8)) IS NULL THEN ? ELSE CAST(SUM(T2.Fld13173TurnoverCt_) AS NUMERIC(27, 8)) END) ? OR (CASE WHEN CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) IS NULL THEN ? WHEN MAX(T25._Kind) = ? OR MAX(T25._Kind) = ? AND CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) > ? THEN CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) ELSE ? END) ? OR (CASE WHEN CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) IS NULL THEN ? WHEN MAX(T25._Kind) = ? OR MAX(T25._Kind) = ? AND CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) < ? THEN -CAST(SUM(T2.Fld13173Balance_) AS NUMERIC(33, 8)) ELSE ? END) ? OR (CASE WHEN CAST(SUM(T2.Fld13173Balance_ + T2.Fld13173FinalTurnover_) AS NUMERIC(34, 8)) IS NULL THEN ? WHEN MAX(T25._Kind) = ? OR MAX(T25._Kind) = ? AND CAST(SUM(T2.Fld13173Balance_ + T2.Fld13173FinalTurnover_) AS NUMERIC(34, 8)) > ? THEN CAST(SUM(T2.Fld13173Balance_ + T2.Fld13173FinalTurnover_) AS NUMERIC(34, 8)) ELSE ? END) ? OR (CASE WHEN CAST(SUM(T2.Fld13173Balance_ + T2.Fld13173FinalTurnover_) AS NUMERIC(34, 8)) IS NULL THEN ? WHEN MAX(T25._Kind) = ? OR MAX(T25._Kind) = ? AND CAST(SUM(T2.Fld13173Balance_ + T2.Fld13173FinalTurnover_) AS NUMERIC(34, 8)) < ? THEN -CAST(SUM(T2.Fld13173Balance_ + T2.Fld13173FinalTurnover_) AS NUMERIC(34, 8)) ELSE ? END) ? OR (CASE WHEN CAST(SUM(T2.Fld13175TurnoverDt_) AS NUMERIC(26, 8)) IS NULL THEN ? ELSE CAST(SUM(T2.Fld13175TurnoverDt_) AS NUMERIC(26, 8)) END) ? OR (CASE WHEN CAST(SUM(T2.Fld13175TurnoverCt_) AS NUMERIC(26, 8)) IS NULL THEN ? ELSE CAST(SUM(T2.Fld13175TurnoverCt_) AS NUMERIC(26, 8)) END) ? OR (CASE WHEN CAST(SUM(T2.Fld13175Balance_) AS NUMERIC(32, 8)) IS NULL THEN ? WHEN MAX(T25._Kind) = ? OR MAX(T25._Kind) = ? AND CAST(SUM(T2.Fld13175Balance_) AS NUMERIC(32, 8)) > ? THEN CAST(SUM(T2.Fld13175Balance_) AS NUMERIC(32, 8)) ELSE ? END) ? OR (CASE WHEN CAST(SUM(T2.Fld13175Balance_) AS NUMERIC(32, 8)) IS NULL THEN ? WHEN MAX(T25._Kind) = ? OR MAX(T25._Kind) = ? AND CAST(SUM(T2.Fld13175Balance_) AS NUMERIC(32, 8)) < ? THEN -CAST(SUM(T2.Fld13175Balance_) AS NUMERIC(32, 8)) ELSE ? END) ? OR (CASE WHEN CAST(SUM(T2.Fld13175Balance_ + T2.Fld13175FinalTurnover_) AS NUMERIC(33, 8)) IS NULL THEN ? WHEN MAX(T25._Kind) = ? OR MAX(T25._Kind) = ? AND CAST(SUM(T2.Fld13175Balance_ + T2.Fld13175FinalTurnover_) AS NUMERIC(33, 8)) > ? THEN CAST(SUM(T2.Fld13175Balance_ + T2.Fld13175FinalTurnover_) AS NUMERIC(33, 8)) ELSE ? END) ? OR (CASE WHEN CAST(SUM(T2.Fld13175Balance_ + T2.Fld13175FinalTurnover_) AS NUMERIC(33, 8)) IS NULL THEN ? WHEN MAX(T25._Kind) = ? OR MAX(T25._Kind) = ? AND CAST(SUM(T2.Fld13175Balance_ + T2.Fld13175FinalTurnover_) AS NUMERIC(33, 8)) < ? THEN -CAST(SUM(T2.Fld13175Balance_ + T2.Fld13175FinalTurnover_) AS NUMERIC(33, 8)) ELSE ? END) ?) T1
LEFT OUTER JOIN dbo._Reference79 T26 WITH(NOLOCK)
ON T1.Fld13995RRef = T26._IDRRef
LEFT OUTER JOIN dbo._Acc12 T27 WITH(NOLOCK)
ON T1.AccountRRef = T27._IDRRef
WHERE (T1.Fld13171RRef = ?)
3. план во вложении
Было бы очень интересно получить детальный видео ответ. Думаю интересно будет и другим участникам.
…
(текст комментария доступен только участникам Мастер-группы)
Спасибо. Это правильно.
…
(текст комментария доступен только участникам Мастер-группы)
Подскажите как в таком случае анализировать запрос 1С? По контексту я вижу, что это типовой отчет ОСВ, но не известно какой именно запрос был сформирован.
2. В запросе sql я вижу временные таблицы. Как понять, что в них?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
Почему у меня вместо Index Scan вместо Index Seek?
Хочу вывести номер документа, дату и наименование контрагента для документов поступления. Написал запрос:
ВЫБРАТЬ Номер,Дата,Контрагент.Наименование
ИЗ Документ.ПоступлениеТоваровУслуг
Получаю такой план запроса:
Rows Executes StmtText
—- ——— ———
12910 1 Hash Match(Left Outer Join, HASH:([_Document121_Q_000_T_001]
12910 1 |—Clustered Index Scan(OBJECT:([TestSql].[dbo].[_Document121].[PK___Document121__21C0F255]
11406 1 |—Index Scan(OBJECT:([TestSql].[dbo].[_Reference30].[_Referenc30_Descr_SR]
Почему для поиска имени контрагента выполняется Index Scan, а не Index Seek ?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый вечер! Скажите, пожалуйста, в уроках озвучено 3 оператора соединения Nasted Loops, Merge Join и Hash Join. Правильно ли утверждать, что все эти операторы есть логическое умножение? В уроке 44, модуль 3, когда выбираем код из реквизита составного типа, возникает Nasted Loops, левое внешнее соединение, выбираем те строки, которых нет в наборе Hash Join. Так верно утверждать? И еще, не понятно, в данном случае, по какому полю происходит поиск строки ведущей таблицы в ведомой таблице? Как я понял тут идет соединение по Истине. Спасибо!
…
(текст комментария доступен только участникам Мастер-группы)
Снимаю вопрос, посмотрел графический план запроса, все в всплывающих подсказках написано. Просто нужно правильно определить поля, которые скрыты за всякими *Fld! :)
В текстовом плане запроса rows — это общее количество строк за Executes или количество строк, вернувшееся при одном выполнении?
Т.е. общее количество обработанных строк = Executes*rows или нет?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
В видео про признаки неоптимального плана Nested Loops
ошибку мы увидели, а в чем причина выбора неоптимального плана?
Попробовал потестить, получается что оптимизатора вводит в заблуждение конструкция ПЕРВЫЕ 5
Если результат соединения вначале поместить во временную таблицу, а уже из нее выбрать первые 5 то время выполнения снижается на 35% (duration 4300 в примере против 2800 с ВТ)
…
(текст комментария доступен только участникам Мастер-группы)
Это я понял, вопрос в том, что оптимизатор обманули конструкцией ПЕРВЫЕ 5?
Есть ли какие-либо правила связанные с этой конструкцией?
…
(текст комментария доступен только участникам Мастер-группы)
Вопрос по условию «НЕ В».
В видео Вы говорите, что запрос
«ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ГДЕ
НЕ Товары.Ссылка В
(ВЫБРАТЬ
ОстаткиТоваров.Товар
ИЗ
РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров)»
в 1000 раз медленнее запроса
«ВЫБРАТЬ
Товары.Наименование
ИЗ
Справочник.Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ОстаткиТоваров КАК ОстаткиТоваров
ПО ОстаткиТоваров.Товар = Товары.Ссылка
ГДЕ
ОстаткиТоваров.Товар ЕСТЬ NULL»
но согласно плану стоимость (TotalSubtreeCost) первого 0.22, второго 0.24. Поясните этот момент,пожалуйста.
запросы сиквел и планы
…
(текст комментария доступен только участникам Мастер-группы)
Андрей, совсем не понял Вашего ответа :(.
В видео Вы говорите о том, что конструкцию «НЕ В» лучше заменять на левое соединение.
В плане запроса видно, что стоимость запроса «НЕ В» меньше чем аналогичного запроса с левым соединением.
…
(текст комментария доступен только участникам Мастер-группы)
Время выполнения запроса «НЕ В» 9 мс, запроса с левым соединением — 7мс. Второй замер показал запрос «НЕ В» — 7мс и запрос с левым соединением — 8мс
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
Правильно ли я понимаю, что операторы Table Scan, Clustered Index Scan и Index Scan в любом случае перебирают все строки таблицы, но в 2-х последних случаях идет перебор в порядке возрастания индекса и получаются упорядоченные таблицы?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день.
В уроках говорилось, что оператор Concatenation (Объединить все) выполняется всегда очень быстро. Но я на практике уже встретил несколько запросов, у которых в плане запроса этот оператор имеет очень большой вес (даже максимальный бывает). Чем это можно объяснить? или это просто какое-то неверное определение весов операторов?
…
(текст комментария доступен только участникам Мастер-группы)
В файле запрос и план запроса. Получилось только в виде текста план выгрузить. Здесь у оператора Concatenation вес — 33. Максимальный во всем плане
…
(текст комментария доступен только участникам Мастер-группы)
В данном случае запрос такой, исходя из задачи. И в общем-то он выполняется не так уж долго, учитывая, сколько данных выбирается (их действительно надо выбирать много). И что интересно, пробы переписать его (например вынести запросы к виртуальным таблицам в отдельные пакеты) приводили к тому, что запрос начинал выполняться дольше, чем в текущем виде…
…
(текст комментария доступен только участникам Мастер-группы)
Беда в том, что я не имею в данный момент доступа к серверу субд и его настройкам. Спасибо за совет, попробую двигаться в этом направлении
В видео где Вы демонстрируете работу оператора seek … where. Если убрать условие по Характеристики будет ли использован оператор index seek?
2. На платформе 8.3 этот же запрос (но без фильтра по характеристике), на этой же базе выглядит иначе:
SELECT
T1.Fld20RRef,
T1.Fld21Balance_
FROM (SELECT
T2._Fld20RRef AS Fld20RRef,
CAST(SUM(T2._Fld21) AS NUMERIC(30, 8)) AS Fld21Balance_
FROM dbo._AccumRgT22 T2 WITH(NOLOCK)
WHERE T2._Period = @P1 AND (T2._Fld21 @P2) AND (T2._Fld21 @P3)
GROUP BY T2._Fld20RRef
HAVING (CAST(SUM(T2._Fld21) AS NUMERIC(30, 8))) @P4) T1
соответственно даже без условия по характеристике в плане есть оператор seek…where. Это ошибка разработчиков платформы или ошибка проектирования структуры регистра?
…
(текст комментария доступен только участникам Мастер-группы)
А как видно, что запрос скатывается в скан? По оператору seek…where?
Тоже подумал про партнерский форум. Задам его там. Ответ потом помещу здесь.
план запроса
…
(текст комментария доступен только участникам Мастер-группы)
В каких еще таблицах кроме регистров сведений включена сортировка и по каким полям? Где почерпнуть эту информацию?
…
(текст комментария доступен только участникам Мастер-группы)
Здравствуйте! В видео про признаки неоптимального запроса Nested Loops мы видели, что при внутреннем соединении регистров примера ведущей таблицой является таблица с 2млн записей, но при этом оператор Index Seek выполнился немного меньше, чем 2 млн раз. С чем связана разница?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
Скажите, в плане понятие Стоимость оператора(поддерева, операции в/в) это в какой единице измерения?
Можно ли сравнивать эти абсолютные значения в разных запросах? То есть оператор со стоимостью 5 в двух запросах сопоставимо нагружают сервер или эти стоимости в абсолютных величинах имеют смысл только для конкретного запроса?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
При записи в таблицу SQL кроме записи в основную таблицу также вносятся изменения в индексы этой таблицы. А как определить какую долю времени записи занимает обновление индексов?
Просто индекс ускоряет чтение, но замедляет запись. И кроме добавления индексов интересна также задача по удалению индексов.
Ага, наверно по sys.dm_db_missing_index_group_stats создаем индексы, которые SQL рекомендует, а затем с помощью dm_db_index_usage_stats мониторим время на обновление и время на пользование. И уже дальше от контекста использования смотрим, что важнее выигрыш от чтения или выигрыш от записи.
А не знаете как сохранять статистику по индексам при перезапуске SQL? Может есть готовый скрипт?
К сожалению в dm_db_index_usage_stats не длительности использования и обновления, а время последнего использования или обновления.
Есть какой-то способ все же узнать временные затраты на обновление индекса при изменении основной таблицы?
…
(текст комментария доступен только участникам Мастер-группы)
…
(текст комментария доступен только участникам Мастер-группы)
…
(текст комментария доступен только участникам Мастер-группы)
Начал еще раз разбираться с медленным восстановлением последовательности. Все упирается в использовании виртуальной таблицы регистра накопления Остатки.
Выполняю запрос получение на нужную дату в середине месяца старого года по одному измерению и одному ресурсу. Запрос во вложении.
Смотрю во что превращается запрос в Sql. Во вложении.
Там соединяется таблица итогов и часть основной таблицы. Все логично. Но если посмотреть на запрос из основной таблицы, то там есть избыточное условие:
(_Period = @P6) AND (_Period < @P8)
где @P6 выбранная мною дата, а @P8 это начало следующего месяца. Из-за этой избыточности и происходит торможение.
Если убрать (_Period < @P8), то запрос существенно ускоряется.
Можете помочь донести до разработчиков 1с эту проблему?
…
(текст комментария доступен только участникам Мастер-группы)
Андрей, добрый день. Можете прокомментировать следующую ситуацию которую я обнаружил? у меня периодический регистр сведений с 2 измерениями (Магазин, КатегорияТовара) и вот его запрос:
ВЫБРАТЬ
ИсторияСмены.КатегорияТовара КАК КатегорияТовара,
ИсторияСмены.КатегорияССМ КАК МагазинОтправитель
ИЗ
РегистрСведений.ИсторияСменыКатегорий.СрезПоследних(
&Дата,
Магазин = &Магазин
И КатегорияТовара В (&СписокКатегорийТоваров)) КАК ИсторияСмены
и оптимизатор построил следующий план. Вопрос: почему сначала оптимизатор решил что лучше пойти в таблицу индекса Измерение1 + [Измерение2 +…] + Период, а потом искать записи в индексе Период + [Измерение1 + …] ? неужели это быстрее чем сразу пойти в кластерный индекс и найти записи там ?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
В занятии указано, что графический план запросов нужно читать справа налево и сверху вниз.
В начале курса мы установили параллелизм в значение 1. Правильно ли я понимаю, что если бы там был например 3, то в сложном дереве плана запроса сразу по 3 оператора выполнялось (разумеется при условии их независимости) или это имеется в виду, что один оператор может выполняться на 3-х процессорах?
…
(текст комментария доступен только участникам Мастер-группы)
Здравствуйте! В разборе примера работы оператора Table Scan мы видели инструкцию SQL Truncate Table #tt1, и вы сказали, что это удаление временной таблицы. Тогда для чего используется уничтожение временных таблиц в самом запросе? (синтаксис УНИЧТОЖИТЬ ВТ)
…
(текст комментария доступен только участникам Мастер-группы)
Но даже если не писать Уничтожить ВТ, то инструкция Truncate все равно появляется. То есть можно не добавлять дополнительно в самом запросе уничтожение?
…
(текст комментария доступен только участникам Мастер-группы)
И еще один вопрос. В видео говорится, что Compute scalar — это обычно маленькая стоимость, и можно на них особо внимания не обращать. При подготовке к экзамену я смотрела видео от разработчика SQL про планы запроса и как читать планы запроса. В этом видео обращали внимания на оператор Compute scalar, что хотя он в плане запроса 0%, а на самом деле он может оказывать достаточно сильное влияние на скорость выполнения запроса, потому что оптимизатор не может сделать оценку стоимости этот оператора, поэтому он всегда дает оценку Compute scalar 0%. Но если сравнить, как будет выполняться запрос с вычисляемыми полями и без в Management Studio со с включенными счетчиками (set statistic time on), то запрос с вычисляемыми полями будет выполняться раза в 4 медленнее, чем без вычисляемых полей.
Как вы можете это прокомментировать?
…
(текст комментария доступен только участникам Мастер-группы)
https://www.youtube.com/watch?v=CXtj0lwA5Ko
Не помню точно, но вроде это то видео. Мне он понравился — хорошо объясняет)
…
(текст комментария доступен только участникам Мастер-группы)
Здравствуйте.
Сделала эксперимент, как будет вести себя запрос, если использовать функцию представления и если не использовать.
Детальное описание того, что получилось — во вложении. Как вы можете прокомментировать, в каких случаях нужно использовать функцию представление?
…
(текст комментария доступен только участникам Мастер-группы)
Андрей, добрый день!
Посмотрев видео, захотелось воспользоваться программой SQL Sentry Plan Explorer, мне кажется там должно быть удобнее анализировать более-менее крупные планы запросов.
Но не могу в SQL Profiler отловить свой запрос целиком.
Запускаю трассировку, формирую отчет (в запросе используются временные таблицы в том числе), останавливаю трассировку и получаю такую картину как на приложенном рисунке.
Похоже все выполняется частями, просмотрел все строки, нашел самый крупный кусок текстового плана, но соответствующего ему графического плана не нашел.
Может есть какое-то еще событие в котором можно увидеть итоговый получившийся план запроса?
…
(текст комментария доступен только участникам Мастер-группы)
Спасибо!
Таким способом удалось посмотреть графическое представление.
Андрей, приложил кусок графического представления плана запроса.
Подскажите, как может получаться так, что работа с сотнями тысяч строк имеет нулевую стоимость, а сортировка 23 и 73 строк «стоит» 53% и 33% соответственно.
Ведь по стрелкам видно, что у сортировке на входе всего 23 строки, неужели отчет, который формируется несколько минут, большую часть времени тратит на сортировку этих строк?
…
(текст комментария доступен только участникам Мастер-группы)
А есть ли где-то информация о стоимости по факту?
…
(текст комментария доступен только участникам Мастер-группы)
возникли еще вопросы: если реквизит проиндексирован, то приемущественно должен использоваться оператор index seek, а какой оператор оптимизатор должен выбрать в качестве оптимального если в секции ГДЕ 2 и более условий, по индексируемому полю и по не индексируемому? оказывает ли влияние порядок условий в тексте запроса? и если указано 1 условие по не индексируемому полю?
возможно вопрос не по этому занятию, но боюсь могу забыть задать его позже: в рамках задачи таблицы соединяются внутренним соединением и присутствует секция ГДЕ. будет ли выигрыш по производительности если условия из секции ГДЕ перенести в условие соединения и почему ?
…
(текст комментария доступен только участникам Мастер-группы)
оказывают ли влияние добавление индексов на скорость работы оператора Sort ? каким образом можно уменьшить время работы этого оператора когда отсортировать нужно по 2-3 полям ?
…
(текст комментария доступен только участникам Мастер-группы)
Добрый день!
Правильно ли я понимаю что запись временной таблицы в TempDB происходит если есть оператор Тable Spoon. В противном случае временная таблица остается в оперативной памяти?
Есть ли рекомендации когда использовать временные таблицы, а когда лучше все таки использовать вложенные запросы. Например когда нет соединений, но результат временной таблицы/вложенного запроса необходимо сгруппировать?
Можно ли по количеству строк во временной таблицы предсказать будет ли она в оперативной памяти или будет записана на диск?
…
(текст комментария доступен только участникам Мастер-группы)
Временные таблицы это не универсальное решение всех проблем. Чем больше количество строк вы будете помещать во временную таблицу, тем больше вероятность что это не поможет :)
Когда у вас много строк в основной таблице, то старайтесь актуализировать статистику и только потом делать оптимизацию в других направлениях.
…
(текст комментария доступен только участникам Мастер-группы)