Некоторые принципы оптимизации запросов 1С (+SQL)
Разработка - Математика и алгоритмы
Сразу к делу.
Я надеюсь, к концу чтения этой публикации у Вас появятся некоторые представления, а если у Вас они есть, то Вы можете не читать дальше, т.к. это весьма субъективный вопрос.
Держите в своей голове несколько принципов написания запросов:
- В маленьких табличках нечего оптимизировать, т.к. 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) Т.к. все отсортировано, то частые изменения предполагают периодическую дефрагментацию. Иначе статистика, накапливаемая сервером, начнет жить своей жизнью, меняя стратегию выборки данных по запросам.
Примечание:
Несмотря на кажущуюся простоту, скоро Вы поймете, что можно оптимизировать бесконечно и бесконечно зарабатывать большие деньги только этим. Удачи!
Специальные предложения
См. также
Регистры бухгалтерии. Общая информация 111
05.09.2019 6735 YPermitin 22
"Хочу универсально!" [Часть 1] 65
02.09.2019 4914 SeiOkami 35
Иерархия без "В ИЕРАРХИИ" 117
22.08.2019 4921 ildarovich 16
EnterpriseData – часть 3. Загрузка данных, идентификация объектов 62
22.08.2019 4242 ids79 7
Обработчики событий при записи объектов. Зачем и что за чем? 202
25.07.2019 12815 4 AlbinaAAA 23
Как проводятся документы в типовых конфигурациях от 1С 137
24.07.2019 16090 skv_79 32
FizzBuzz на 1С. Чем короче, тем веселее. Варианты принимаются... 8
24.07.2019 2869 vandalsvq 16
Управление качеством кода 136
22.07.2019 8263 Stepa86 29
Что делает "В ИЕРАРХИИ" в запросе? 94
16.07.2019 8212 YPermitin 34
Создание отчетов с помощью СКД - основные понятия и элементы 208
25.06.2019 21060 ids79 17
Реализуем Стек, Очередь и Приоритетную очередь в 1С 52
24.06.2019 7814 RonX01 63
Организация хранения промежуточных данных 3
29.05.2019 1957 scientes 1
Вычисление 200 тысяч знаков числа pi 73
28.05.2019 3993 Oleg_nsk 93
Регистры накопления. Виртуальные таблицы. Часть №1: Обороты 84
20.05.2019 11118 YPermitin 5
Даем названия переменным: как префиксы экономят наше время 10
06.05.2019 3216 Designer1C 69
Заметки по SQL: Срез последних - аналог запроса 15
15.01.2019 6323 IVC_goal 5
Разработка и сценарное тестирование с Vanessa-ADD. Концепция, теория и сквозной пример создания сценария 222
09.01.2019 27596 Vladimir Litvinenko 69
Многопоточное восстановление последовательностей 41
05.12.2018 7255 _ASZ_ 29
Автоматические и управляемые блокировки применительно к типовым конфигурациям 1С 127
10.11.2018 22288 ids79 40
Основные понятия и механизмы оптимизации клиент-серверного взаимодействия в 1C 147
23.08.2018 22917 Rain88 42
Теорема номер тринадцать 15
15.03.2018 9362 vasilev2015 24
Введение в CI для 1С 87
21.11.2017 19335 real_MaxA 22
Как работает серверный вызов в 1С 459
18.11.2017 44258 pahich 77
#Область ВНЕШНИЕ_ВЫЗОВЫ или MVC в 1С, библиотечность и упрощение интеграции кода 43
12.10.2017 14813 for_sale 58
Групповая разработка конфигураций в крупном холдинге 68
15.08.2017 17551 stas_ganiev 15
Автоматизация процесса 1С-разработки 91
07.06.2017 23032 ekaruk 9
Пишем игру Минер. Обработка событий ActiveX в 1С 29
29.05.2017 12740 user621724_Dimav1979 11
Как я доступ на kb.1c.ru получал 91
01.05.2017 22539 ikekoval 33
Улучшение стандарта "Структура модуля" 6
26.03.2017 12331 o.nikolaev 23
"Распределение в запросе" или "избавляемся от перебора" 185
16.12.2016 28640 alexandersh 48
Планы обмена. Квитировать или гарантировать? 24
12.12.2016 14565 zhichkin 9
Использование git для доработки типовых конфигураций 1С 230
11.10.2016 188496 pumbaE 31
Оптимизация запросов 1С:Предприятие – от теории к практике 116
07.10.2016 32089 bpc222 20
Регистры сведений 1С. Как это устроено. 729
05.08.2016 151074 Sergey.Noskov 155
Переводим расширения на 8.3.8. Памятка. 79
29.07.2016 39730 mrXoxot 12
Подобие Объектно-ориентированного программирования в 1С (ПООПс) 12
24.07.2016 10925 adam26 54
Опыт практического применения методики BDD на 1С. Написание сценариев 121
03.07.2016 20336 oleynik.dv 132
Заметки про запросы. Последовательность. 110
27.05.2016 29635 vasilev2015 31
Оптимизация планирования доставки грузов. Алгоритм кластеризации k-means (метод K-средних). 26
10 стартмани
09.02.2016 26531 mi1man 4
Контур.EDI изнутри, или история командной разработки тиражного продукта на 1С 174
17.11.2015 36057 skif47 88
Порядок записи движений регистров при проведении документа 95
13.11.2015 80536 triton_tver 8
Три способа получить дерево элементов иерархического справочника 52
11.11.2015 63135 32ops 9
Распределение суммы по базе 48
08.11.2015 27743 starik-2005 19
Мультиинструментальный Brute Force 4
30.10.2015 10445 scientes 4
1С с "плюсами" 74
14.10.2015 19975 IntelInside 47
Знакомство с технологией Automation-сервер на примерах 33
28.09.2015 26197 niko11s 10
Критерии отбора 83
24.09.2015 49493 niko11s 13
По ссылке или по значению? Ключевое слово Знач и с чем его едят 196
12.08.2015 37194 Evil Beaver 239
Приемы обработки больших данных в 1С 258
07.08.2015 60333 tormozit 27
