Сразу к делу.
Я надеюсь, к концу чтения этой публикации у Вас появятся некоторые представления, а если у Вас они есть, то Вы можете не читать дальше, т.к. это весьма субъективный вопрос.
Держите в своей голове несколько принципов написания запросов:
— В маленьких табличках нечего оптимизировать, т.к. SQL сервер в любом случае будет просматривать всю табличку, а 1С не забывает делать один кластеризованный индекс.
— Если вы намерены получить ограниченное количество строк (одну или две), то не забывайте сообщить об этом заранее, добавляя «первые K» и сортировку вдоль отсортированного поля.
— Поиск в отсортированном поле выполняется быстрее, чем в любом другом. Вам нужно соединять таблицы или выбирать из них строки по отсортированным полям (в 1С есть свойство индексирования у реквизитов).
— Старайтесь обходиться левыми соединениями и поднимать вверх таблички с наименьшим количеством строк (оптимизатор SQL сервера будет стараться делать это за Вас, опираясь на статистику, и, конечно, может ухудшить ожидаемое время, если соединений слишком много, ну, скажем, 7 и более).
— Если условий много, то складывайте во временную таблицу небольшой объем нужных строк, отбирая их по индексу. А потом используйте этот небольшой набор, чтобы добавить прочие условия или левые соединения. (Оптимизатор SQL вполне может «напутать» с порядком формирования таких начальных наборов, и ждать придется долго).
— Если есть вложенные запросы и много вычисляемых полей, то старайтесь отложить вычисления на позднее время, когда количество отобранных строк максимально сократится.
— Добавляйте требуемые индексы к реквизитам, значения которых редко повторяются. Поиск хорошо «разгоняется» по таким полям.
Пути оптимизации запросов:
— Добавление и удаление индексов. Это позволит ускорить поиск и отбор нужных строк.
— Изменение текста запросов. Это позволит изменить стратегию отбора нужных строк из таблиц.
Модель поведения программиста при оптимизации запросов.
Обычно проблема появляется резко и без подсказок. А нам нужно быстро собрать сведения.
1) Работа не клеится, когда кто-то стоит над душой. Поэтому открываем Management Studio, подключаемся к нашей базе и нажимаем волшебную комбинацию <Ctrl>+<Alt>+A. В результате откроется окно, где мы для виду будем тыкать на кнопочки с умным видом, пока нас не оставят в покое, решив, что мы уже работаем. Это лучше потренировать заранее.
2) Проверим для начала, не ждут ли пользователи друг друга:
/*Кто кого блокирует */
SELECT
pr1.status,
pr1.waittime as [Сколько ждем?],
pr1.waitresource as [Что ждем?],
pr1.waittype as [Тип ожидания],
DB_NAME(pr1.dbid) AS [DB],
pr1.spid AS [spID ждущего],
RTRIM(pr1.loginame) AS [Login ждущего],
RTRIM(pr1.hostname) AS [Компьютер ждущего],
pr1.program_name AS [программа ждущего],
pr2.spid AS [spID виновника],
RTRIM(pr2.loginame) AS [Login виновника],
RTRIM(pr2.hostname) AS [Компьютер виновника],
pr2.program_name AS [программа виновника],
txt.[text] AS [Запрос виновника],
pr1.*
FROM MASTER.dbo.sysprocesses as pr1(NOLOCK)
left JOIN MASTER.dbo.sysprocesses as pr2(NOLOCK) ON (pr2.spid = pr1.blocked)
OUTER APPLY MASTER.sys.dm_exec_sql_text(pr2.[sql_handle]) AS txt
WHERE pr1.blocked <> 0
--or pr1.hostname='ws-msk-a1573'
--or pr1.spid in (87)
Скажу честно, я давно не сталкиваюсь с блокировками, поэтому не буду касаться здесь деталей.
3) Проверим, не ждут ли пользователи долгие запросы:
/* Список тяжелых запросов */
SELECT TOP 555
execution_count,
total_worker_time/(execution_count*1000) AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text,
pt.query_plan
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS pt
--where st.text like '%dnprefix%Docno>%'
ORDER BY 2 DESC
Этот запрос (в отличие от предыдущего) всегда что-то возвращает. В первой колонке количество запросов, а во второй длительность выполнения (смело округляйте до тысяч, и это будут секунды). Найдите строки с большим количеством в первой колонке и подумайте, могут ли на это жаловаться пользователи, взглянув на время выполнения. Имеет смысл анализировать запросы длительностью большей и близкой к 1 секунде. Третья колонка содержит текст запроса (и его придется искать в 1С, связав имена таблиц в запросе с привычными именами 1С). Четвертая колонка содержит план выполнения запроса, и если Вы его откроете, то узнаете, из каких источников и в каком порядке выбираются данные (есть даже совет, какого индекса не хватает).
3) Разработчиком был программист (хочется об этом мечтать), а пользоваться готовым функционалом дали пользователям, которые преследуют какие-то свои цели. Поэтому часть имеющихся индексов может просто тормозить базу. Это, в первую очередь, индексы на полях, значения которых часто повторяются, или не используемые в запросах индексы:
/* Index Read/Write stats (all tables in current DB) */
SELECT
OBJECT_NAME(s.[object_id]) AS [ObjectName],
i.name AS [IndexName],
i.index_id,
user_seeks + user_scans + user_lookups AS [Reads],
user_updates AS [Writes],
i.type_desc AS [IndexType],
i.fill_factor AS [FillFactor]
FROM
sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE
OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
--Укажите анализируемую таблицу (Комментарий в SQL –это «--«)
--and OBJECT_NAME(s.[object_id]) like '_Reference44'
ORDER BY
OBJECT_NAME(s.[object_id]),
writes DESC,
reads DESC;
Если в поле Read стоит ноль или число значительно меньшее числа в поле Write, то индекс скорее мешает и его лучше убрать. Если Вы сами не можете принять такое решение, то спросите:
/*Возможно не нужные индексы*/
SELECT
OBJECT_NAME(s.[object_id]) AS [Table Name] ,
i.name AS [Index Name] ,
i.index_id ,
user_updates AS [Total Writes] ,
user_seeks + user_scans + user_lookups AS [Total Reads] ,
user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id
WHERE
OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > ( user_seeks + user_scans + user_lookups )
AND i.index_id > 1
ORDER BY
[Difference] DESC ,
[Total Writes] DESC ,
[Total Reads] ASC ;
А если не верите мне, то сходите https://technet.microsoft.com/ru-ru/library/jj128029.aspx
Очевидные вопросы:
1) Кластеризованный индекс – это отсортированная по каким-то полям сама таблица (а таблица может быть только одна).
2) Дополнительный индекс содержит индексируемые поля и поле кластеризованного индекса. Поиск в таком индексе сводится к отбору значений, по которым будет выполняться поиск в кластеризованном индексе, если требуется информация из других полей (реквизитов).
3) У регистров кластеризованный индекс содержит все измерения в порядке, указанном в конфигурации. А обычный индекс включает реквизит (с сортировкой) и все измерения, т.е. измерения всегда будут задвоены (что увеличивает объем).
4) Поиск в индексе возможен, если указаны все входящие в него поля (и желательно без условий, т.к. SQL допускает только простейшие сравнения в индексах).
5) В плане запроса указано относительное время выполнения операции и сравнительный объем отбираемых данных из источника.
6) Т.к. все отсортировано, то частые изменения предполагают периодическую дефрагментацию. Иначе статистика, накапливаемая сервером, начнет жить своей жизнью, меняя стратегию выборки данных по запросам.
Примечание:
Несмотря на кажущуюся простоту, скоро Вы поймете, что можно оптимизировать бесконечно и бесконечно зарабатывать большие деньги только этим. Удачи!
Related Posts
Получение логина и пароля техподдержки 1С из базы
Класс для вывода отчета в Excel
Счет-фактура для УПП
Библиотека классов для создания внешней компоненты 1С на C#- Акт об оказании услуг (со скидками) — внешняя печатная форма для Управление торговлей 11.1.10.86
Прайс-лист с артикулом в отдельной колонке
«Поиск в индексе возможен, если указаны все входящие в него поля».
Точнее все таки будет «Поиск в индексе возможен по набору полей, если они расположены в начале структуры индекса».
(0) а можно просто запустить консоль которая сделает весь этот анализ более точно и глубоко
не поверил, сходил по ссылке, не поленился, а там написано:
Не забудьте об периодическом создании отчетов — эта операция может отсутствовать в ежедневной нагрузке. Хотя используемы для отчетности индексы и используются нечасто, их наличие может быть критически необходимым
привет блокировки, сегодня вы все будете ждать маленькую табличку, потому что там скан и нам посоветовали ее не трогать
отличный доклад был на инфостарте про проблемы с временными таблицами на пострессе, можно переосмыслить подход к написанию запросов )
не проходит и недели как на инфостарте люди пытаются расковырять тайные техники оптимизации, наполнить себя эзотерическими сведениями
а проблема банальна — люди при написании кода не получают денег за то чтобы он работал быстро, только за то чтобы функционал успеть к дедлайну
напишите статью чтобы платить шеф начал за оптимизацию и оно «как попрет»… )
(5) Gilev.Vyacheslav, на «пост стрессе» или «на постгресе»? Как бы вроде одна буква, но столько смыслов разных…
На курсах подготовки к эксперту помимо многого всего (обычно нехорошего) о гилевах, рассказывают еще о том, почему 1С в плане оптимизации запросов учит выносить соединения с подзапросами во временные таблицы — типа 1С-неги тупые и иначе им сложно что-то объяснить. Но 1С-неги помимо соединений с подзапросами как-то автоматически выносят во временные таблицы и все остальное — т.е. даже такую примитивную вещь не в силах освоить. В итоге у чуть более продвинутых товарищей так много интересной работы.
(7) starik-2005,
тем не менее факты:
фирма 1С купила разработку дизайна 8ки у Егора Гилева
Евгений Гилев известен обучением
фирма 1С не раз обращалась ко мне (Вячеславу Гилеву) за услугами ЗА ДЕНЬГИ
по производительности у моей команды отзывов по производительности больше чем у подразделения 1С:ЦКТП со всеми франчайзами вместе взятыми
что там себе позволяют отдельные представители 1С на тренингах пусть останется на их совести
если бы это было так, 1С:Предприятие не вытеснило другие другие учетные системы
я знаю очень много талантливых программистов 1С
это скорее больше говорит о том, кто и как обучает
если «зубриловку» 1С:Профессионал заменить практическим экзаменом, проверяющим практические навыки, то гляди и доверия больше было бы )
сами же породили лозунг «Доступно и всерьез»…
(8) да мне, собственно, и так ясно, что кто-то что-то у кого-то покупает. Отрицательно высказывался некий Бурмистров — может ему завидно — не знаю.
По поводу того, что 1С-неги — тупые (разумеется, не все — просто порог входа вроде бы как весьма маленький) — это вполне обоснованное мнение, с другой стороны кодеры на JS не менее тупые (опять же не все, но их в принципе больше на порядок), но они хотя бы ООП осваивают.
Ну и основной месседж, который Вы так и не уловили (видимо, Бурмистров в чем-то тут прав) — это то, что проблема оптимизации запросов решается 1С-негами в большинстве случаев просто через вынесение всего и вся во временные таблицы без какой-либо задней мысли на тему, зачем это вообще надо и какой эффект это дает. Один товарищ — эксперт по скулу — на какой-то международной конфе по скулу сказал, что есть в России программа такая — 1С. Так вот она такое (Т-А-А-А-КОЕ!!!) количество временных таблиц генерит в своих запросах, что он такого нигде и никогда не видел. И что, это программа плохая или 1С-неги? Или вендор из-за тупизны 1С-негов кроме как научить их оптимизировать запросы через временные таблицы ничему не могут? Тогда и вендор — тупой, ибо не может научить, т.е. качественно оказать образовательную услугу?
Ну а экзамены — это в принципе отстой. Всегда можно за сто попыток примелькаться экзаменаторам, и они пожалеют потраченных тобой денег и дадут заветную корочку.
(9) starik-2005,
очень сильно сомневаюсь —
он не работает в 1С, не ведет
наша консоль запросов при вставке во временную таблицу оценивает разумность объема строк и рациональность такого приема и любой программист может проверить свой код
все кто проходит курсы на сайте изучают нашу консоль и учатся ее использовать
что за касается мотивации писать хорошо я уже в (6) ответил, так что это Вы не уловили
видать эксперт не до конца эксперт )
Платформа тоже генерирует временные таблицы не спрашивая программиста 1С. И тут скорее можно говорить что надо те кто судят — делают это поверхностно. Не стоит обсуждать эту тему абстрактно без конкретных примеров.
Даже ms sql server может в плане запроса сделать Table Spool, который просматривает входную таблицу и помещает копию каждой строки в скрытую буферную таблицу, которая находится в базе данных tempdb и существует только в течение времени жизни запроса. Скуль у Вас тоже плохой получается!
Если нужно, создайте отдельную ветку форуме. Не надо автору публикации забивать ветку совсем сторонним.
Давайте ближе к теме публикации. Сколько можно себя нахваливать? и подкладывать ссылки на бесполезные поделки?
Мне было бы приятно обойтись без уличного базара. Я внимательно прочитал Ваши комментарии, но не нашел, что из этого достойно добавления к статье.
В этой публикации я себя не рекламирую. Идея написать статью у меня возникла после ознакомления с рядом подобных статей. Я захотел, с одной сторон, изложить все максимально сжатно и приближено к 1с-практике, а, с другой, оставить поле деятельности, для самостоятельного размышления. Ряд приведенных запросов вполне рабочий и используется мной на предприятии (где обслуживает нагруженную систему). Перечисленные мной принципы оптимизации тоже рабочие (ими я руководствуюсь, можно считать из заметками на полях).
Прошу в комментариях придерживаться теории СУБД, чтоб была общая платформа, для рассуждений или спора.
фирма 1С купила разработку дизайна 8ки у Егора Гилева
уж лучше бы и не покупала
(10) Gilev.Vyacheslav, как ты меня уже достал своей рекламой во многих комментах на разных публикациях, угомонись уже наконец-то
Гыгы, Славу стало плющить)))
Был месяц назад на курсах подготовки к проф. и эксперту по тех. вопросам — вели Бурмистров и Филиппов (который книжку написал).
Да и вообще сообщения должны смысл нести,но у некотопых только реклама. Может не взлетел продукт и от такой вот невеселой жизни хочется рвать и метать икру? Бывает. И это пройдет.
Статья хорошая.
А меня вот интересует вопрос. В запросах 1С, к сожалению, нельзя писать скульные хинты. Может есть какие-то ловкие ходы, чтобы заставить запрос выполниться с нужным мне хинтом? Например, отсортировав две таблицы и джойня их между собой в 1С — хочу чтобы вместо нестед лупс использовался мердж. Можно как-то схитрить и заставить план построиться именно так?
Кстати, вот еще один пример весьма очевидной оптимизации, о которой, к сожалению не только лишь все знают, точнее мало кто вообще )))
Если параметры в запрос передавать не в явном виде, а динамически формируя текст запроса — то вы заставляете оптимизатор запросов СУБД каждый раз строить новый план.
N-е количество запросов вида, текст которых собирается динамически:
выполнится в разы медленнее, чем если написать
А по поводу В (&СписокОтбора) — тоже еще один пласт для ускорения. Порой, значительного прироста можно добиться, передав отбор в запрос, поместив его во временную таблицу и отсортировав, а также ЯВНО написать вместо «В (&СписокОтбора)» — «ВНУТРЕННЕЕ СОЕДИНЕНИЕ».
(6) Gilev.Vyacheslav,
Ну собственно в итоге платить приходиться (проходит оптимизация) или мириться с медленной работой.
Большинство в мире работает по PainDD (пока не заболит — фиг с ним, заболело — лечим)
(8) Gilev.Vyacheslav, В конце прошлого года проходил курс подготовки к эксперту, ни одного плохого слова в ваш адрес не было. Уточню, в рамках официально части курса о ваших инструментах вообще молчали (что понятно, им нужно ЦУП по 80к продавать), а в неофициальной части отзывались нейтрально, как об еще одном инструменте, который не лучше и не хуже, все больше зависит от головы того, кто инструмент применяет.
(17) Silenser, да, у меня тоже возникли сомнения в достоверности утверждений товарища Sergey Andreev
(11) ture, посты (3) и (4) не по теме? а ну тогда конечно
(15) Dach, да ладно. Это что, получается, что интерпретатор 1С вообще ни при чём, и собирая запрос конкатенацией с заменами (я уж молчу про новомодную объектную технику) я имею более медленное исполнение итогового запроса? Чё-то или я не понял, или вы странные вещи пишете. Вы о «динамическом формировании текста запроса» — где и в какой момент?
(15) Dach, неоднократно обсуждалось на партнерском, фирма 1С считает что это «усложнение» может принести больше зла в неумелых руках
(18) Gilev.Vyacheslav, указанные посты не грамотные. Я не могу закрыть Вам рот, но рекомендую воздержаться от дальнейших высказываний, т.к. они не делают Вам чести как специалисту.
(19) Yashazz, если на вход интерпретатора СУБД подать различные запросы, где отборы «написаны руками», вместо параметров запроса — для запроса, где отбор написан «вручную» — СУБД будет каждый раз анализировать и заново строить план, вместо того, чтобы использовать кэш. Я не про динамическую сборку целиком, а про параметры.
(22) Gilev.Vyacheslav, Если я Вас задел, прошу прощение. Давайте больше не будем так рассуждать.
(17) времена меняются, да и профессионала по тех.вопросам в прошлом году не было — сдавали сразу эксперта. Теперь все иначе:
А по поводу продуктов от Гилева, то мне лично вполне по душе тес, остальными не пользуюсь — необходимости нет. Простота теста Гилева и возможность посмотреть табличку результатов, сравнив ее с другими тестируемыми конфигурациями, вполне помогает оценить уровень в цепочке производительности одного потока (сервер 1С — сервер СУБД). Но вот какие-то выводы сделать, куда копать — увы и ах.
(25) starik-2005, я вот сейчас вникаю в межпроцессорное взаимодействие (Windows API), пытаясь разобраться на основе аналогий с Posix. И вдруг такой «трёп» не простительный. Если б все программисты вели себя так словно ЧСВ важнее всего остального, то мы не увидели бы ни одной операционной системы. Нет, я знаю, что программист 1С — это халява при больших деньгах, по все же я не предполагал, что кто-то всерьёз считает, что общается с отбросами.
Мне его консоль бесполезна во всех смыслах. Объясню почему в двух словах:
1) найдя запрос в статистике SQL, я долго соображаю откуда он мог прилететь (родной 1с-ный или кто-то умный, что-то прикинул в студии)
2) видя запрос 1С, мне нужно переписать его «голыми руками» на t-sql, чтоб увидеть план (здесь меня спасают только view-хи и предположения о том, как это сделает 1С)
3) в реали, я даже не имею права включать трассировки на SQL или как-то еще тормозить продакшен
4) конечно мне не нужны советы о том, что я сделал не так в запросе, т.к. мне нужен план запроса вместо них
Мне, разумеется, приятно читать замечания, т.к. каждое из них раскрывает моё невежество или напоминает, что не бывает мелочей (хоть и хочется говорить об обратном с замыленными-то глазами). Особенно ценно, когда человек разобрался в причинах и упоминает их.
В конечном счете сообщество программистов 1С смогло организоваться и собирать знания по крупицам. Уже за это нельзя обидно обзываться. Политика 1С носит защитный характер (разработчики прямо-таки охраняют свою платформу от расширений и контролируют способы интеграции). В результате программист 1С оказывается замкнут в рамках дозволенного 1С, но это дозволенное такой программист изучает весьма усердно.
Когда 1С искажает понятия и ограничивает интерфейс, вдруг находится кто-то, кто выделяется своим знанием и волшебным умением. Он просто знает детали об изначальном API, которые собрал своим трудом. И лучшее — это поделиться этим, а не набивать себе цену и баловать своё ЧСВ. Любой серьёзный проект — это дело рук не одного человека, и когда находится один крутой и замкнутый на себе, то это просто лошадь, для тяжелой работы, а не игрок для сложного проекта.
(5) Gilev.Vyacheslav,
. О каком докладе речь?
(28) BorovikSV, 03 МАЛЫЙ ЗАЛ МАСТЕР КЛАСС РЕШЕНИЕ ТИПОВЫХ ПРОБЛЕМ ПРИ ПЕРЕХОДЕ НА POSTGRESQL СМОЛКИН ГРИГОРИЙ
(29) в памятку:
Есть такая кнопка CapsLock. Если она нажата, то все буквы в верхнем регистре вводятся. Слова написанные таким образом означают крик и воспринимаются в цивилизованном обществе как невежество. На профессиональных форумах не принято писать в верхнем регистре.
увидел знакомое «`ws-msk-a1573«` и думаю где то я уже этот формат имени компьютера видел 😉
(0) Жень — ссылка
такие вещи как «Кто кого блокирует», спрашивают у Брента
(30)
копипаст рУками перебивать никто не заморачивается.
Вопросы к составителю расписания мероприятий, походу.
(32) можно объяснить всё, но не всем )
когда чел создает свою «виртуальную реальность» и она ему «удобна», то она его защищает его психику от фрустраций, но она же его и «держит»
не надо его «шевелить палкой» — захочет, сам вылезет из «домика»
(27) а к чему столь много строк-то (пафосу)? К тому, что я сказал о том, что 1С-неги (как и прочие) могут оказаться не на столько умными, чтобы понять, что все во временные таблицы тащить не стоит? И всего-то?
(33)
ну вот и я о том же. Токмо я к тому, что если важен результат, то некоторые (1С, например) понимая, что не всем можно что-то объяснить достоверно, объясняет так, чтобы дошло до большинства. А до большинства может дойти только что-то простое, например вынести все подзапросы во временные таблицы.
(34) не знаю, сам подумай, а я пока скорректирую своё восприятие.
(36) ну довай, корректируй. Токмо поменьше химии )))
(10) интересно, как Ваша консоль предугадает план запроса без статистики и решит, оптимальный запрос или нет.
(36)
(37)
Бокс по переписке?
Мальчики выросли, и пиписки нет? Что за клоунада в комментах.