Всем нужен эксперт

0. Алексей Лустин (lustin) 832 09.01.17 02:40 Сейчас в теме
Приходя на проекты, где необходимы серьезные архитектурные решения, в прошлом году я столкнулся… Скажем, так - с завышенными ожиданиями от компетенций. То есть если вкратце – некоторые вещи, которые для нас представляют собой «базовые», для наших партнеров и друзей оказывались «открытием Америки». Почему так складывается? Лично моё предположение, что за счет огромного количества рутины – у большинства специалистов не хватает времени поднять голову и взглянуть на свою деятельность системно

Перейти к публикации

Комментарии
1. Dorosh Dorosh (Dorosh) 98 09.01.17 10:10 Сейчас в теме
Спасибо за серьезный материал. А по Postgre подобная статья планируется?
turrrrist; Silverbulleters; mrDSide; amon_ra; +4 Ответить
2. Алексей Новиков (Новиков) 288 09.01.17 11:08 Сейчас в теме
Спасибо Алексей! Было бы здорово, если бы Вы что-то также порекомендовали по оборудованию и организации дисковой подсистемы на сервере СУБД. Вот тут был небольшой вброс, но очень жидковато получилось.
3. Татьяна Лустина (Silverbulleters) 125 09.01.17 11:08 Сейчас в теме
Учитывая выход скриптов для Windows от испанских инженеров по PG - да планируется. Это наш "почти технический" долг перед сообществом.
KroVladS; +1 Ответить
4. Sergey Andreev (starik-2005) 1003 09.01.17 12:53 Сейчас в теме
5. Алексей А. (Разумов) 09.01.17 13:10 Сейчас в теме
Еще не вникая, хочется поставить плюс за подход к написанию статьи. Очень "на качество" написана, без торопливости и лени. Даже просто эстетически глаз радует.
kuznecov_egor; fuxic; ZOMI; +3 Ответить
6. Денис Козлов (Akbis) 43 09.01.17 13:21 Сейчас в теме
Если можно получать список с ошибками через скрипт
exec sp_Blitz @IgnorePrioritiesAbove = 50, @EmailRecipients = ‘dba.admins@ВашДомен.ру’

то зачем нужно настраивать job
https://github.com/Microsoft/tigertoolbox/blob/master/MaintenanceSolution/6­_Agent_Alerts.sql

?
7. Сисой Сисой (Сисой) 80 09.01.17 13:55 Сейчас в теме
.УложитьСписокОбъектов(КакойТоСписок) - это о чем?
8. Сисой Сисой (Сисой) 80 09.01.17 14:05 Сейчас в теме
>>Большинство разработчиков не понимают, что соединять нужно таблицы в порядке увеличения записей – то есть вначале с меньшим количество, а уже к ней с большим.

Интересно, а на курсах 1С этому учат? Когда я учился (в нулевые) никто об этом даже и не заикался.
9. Алексей Дубичев (1cWin) 09.01.17 14:26 Сейчас в теме
10. mishgan mishgan (ganshinm@mail.ru) 09.01.17 15:11 Сейчас в теме
>>Большинство разработчиков не понимают, что соединять нужно таблицы в порядке увеличения записей – то есть вначале с меньшим количество,
>> а уже к ней с большим.
Извините, мимо этого пройти не смог. Что автор курит?
ll13; 4rtehouse; Silverbulleters; +3 1 Ответить 2
11. Алексей Лустин (lustin) 832 09.01.17 16:11 Сейчас в теме
(6) это фишка - BrentOzar просто присылает список ошибок, а Microsoft скрипт "валит" оповещение на каждую отдельное письмо. Чтобы не было желания отмахнуться и забить.

(7) да действительно - моя проффесиональная деформация. Я привык называть метод УстановитьПараметр(_коллекция) по старому (по 1С++'ному) как УложитьСписокОбъектов() - это позволяет мне не забыть что чудес не бывает, и коллекции в качестве параметров в реальности превращаются в подготовку служебной таблицы для организации фильтра

(10) видимо я недостаточно понятно объяснил - имелось ввиду, что фильтры должны назначаться как можно раньше - чтобы не получились ситуации, когда для выборки "одной строки", "вы читаете половину базы"
starik-2005; +1 Ответить 1
12. Сергей Рудаков (fishca) 1062 09.01.17 16:27 Сейчас в теме
Кратко и по делу, спасибо!
13. Антон Стеклов (asved.ru) 33 09.01.17 16:44 Сейчас в теме
(10)
В общем-то мы имеем право рассчитывать, что оптимизатор определит оптимальный порядок и методы соединений.

Однако не следует забывать о пороге достаточности в поиске оптимального плана. В условиях высоких нагрузок и сложного набора соединений SQL может найти относительно приличный с его точки зрения вариант и на дальнейшую оптимизацию плана забить. Или вообще не успеть найти достаточно оптимального плана. Или иметь кривые статистики.

Указывая оптимальный с нашей точки зрения порядок соединений, мы провоцируем оптимизатор рассматривать план с совпадающим порядком соединений в первую очередь, тем самым повышая вероятность нахождения желаемого плана за ограниченное время и снижая затраты на оптимизацию.

Впрочем, материя это достаточно тонкая, работает через два раза на третий, и лучше не делать сложных соединений вообще.

Для простых же соединений рекомендация, действительно, смысла не имеет. Лично мне случаи, чтобы оптимизатор терялся в двух-трех таблицах, не встречались.

14. mishgan mishgan (ganshinm@mail.ru) 09.01.17 17:47 Сейчас в теме
(11)
>>видимо я недостаточно понятно объяснил - имелось ввиду, что фильтры должны назначаться как можно раньше - чтобы не получились ситуации,
>>когда для выборки "одной строки", "вы читаете половину базы"

Да, такая формулировка более корректна, спасибо
15. Алексей Лустин (lustin) 832 09.01.17 17:53 Сейчас в теме
(13) Вот это вот и классическая проблема - якобы какой-то оптимизатор, какой то СУБД что-то сделает за Вас. И вы осознанно на это забиваете - а потом внезапно оказывается, что оптимизатор не такой уж оптимальный, да и СУБД бывают разные. И в итоге мы все возвращаемся к истокам. А истоки нам показывают, что надежда на физический оптимизатор и есть тонкий тюнинг, а вначале следует логика запроса, которая никогда не должна быть "оптимизаторо-зависимой".

То есть повторюсь - я прекрасно знаю что оптимизатор MSSQL и две настройки

* optimize for ad hoc workloads = 1
* traceflag on 4199

Дают для 1С вауэффект, но это уже тюнинг на уровне СУБД, а причина то кроется совершенно в другом.

очень много запросов в рамках аудита у нас выглядит примерно так (псевдокод):

ВЫБРАТЬ * 
ПОМЕСТИТЬ втНоменклатуры
ИЗ Справочник.Номенклатура;
ВЫБРАТЬ ПЕРВЫЕ 1 
ИЗ РегистрСведений.Цены как ТипаЦены
ЛЕВОЕ СОЕДИНЕНИЕ втНоменклатуры ПО ТипаЦены.Товар = втНоменклатуры.Ссылка
ГДЕ ТипаЦены.Товар = &ФильтрПоТовару
...Показать Скрыть


Почему очень многие назначают фильтры "в конце" запроса - для нас остается загадкой. Именно поэтому я очень сильно рефлексирую на выражение "Нас спасет оптимизатор, а мы пока запросы конструктором погенерируем".

16. mishgan mishgan (ganshinm@mail.ru) 09.01.17 17:53 Сейчас в теме
(13)
порядок соединения таблиц, указываемый разработчиком не влияет ни на что (если разумеется других отличий нет).
ни на решение, принимаемое оптимизатором.
ни на поиск оптимального плана.
проверить несложно

для того, чтобы СУБД использовала ваш порядок соединений - используются хинты, например option (force order) для MSSQL, /*+ ORDERED */ для Oracle.
но в 1С штатными средствами их использовать нельзя.
ll13; fuxic; 4rtehouse; Andreynikus; lustin; +5 Ответить 1
17. mishgan mishgan (ganshinm@mail.ru) 09.01.17 17:56 Сейчас в теме
(15)
>>очень много запросов в рамках аудита у нас выглядит примерно так (псевдокод)
тут конечно, никакой оптимизатор не спасет.
но это фильтрация, а не порядок соединений
18. Сисой Сисой (Сисой) 80 09.01.17 18:27 Сейчас в теме
Я думаю, автору нужно поправить рекомендацию про соединение. Потому что 99% разработчиков читают эту рекомендацию следующим образом:
Для любой конструкции
Select ... From Table 1 Left Join Table 2 On ...
строго-настрого нужно следить, чтобы количество записей в Table1 было меньше, чем в Table2.

А это совсем не то, что хотел донести автор.
Видимо, имелось в виду следующее: при использовании соединений условия выборки должны максимально отрабатываться условием соединения и (автор об этом не написал) параметрами виртуальных таблиц 1С (если они используются в запросе).
shalimski; lustin; +2 Ответить
19. Антон Стеклов (asved.ru) 33 09.01.17 18:52 Сейчас в теме
(16) Вам знакомо понятие устойчивости плана запроса? Оно очень близко к тому, о чем я писал.

Повторюсь, чтобы увидеть эффект от изменения порядка таблиц в соединении, нужно:
1) Предельно высокие нагрузки
2) Сложное соединение с большим количеством таблиц
Также неплохо будет, если в запросе участвуют временные таблицы, а потом откатыватся транзакция. В таком случае кэширование планов не работает.

Что само по себе является проблемой, решать которую нужно отнюдь не изменением порядка соединения. Однако знать о такой особенности бывает полезно.

Еще можно, кстати, упомянуть о привычке MSSQL не обращать внимания на статистику по второму полю кластерного индекса, если первое поле высокоселективно. Для разделенных баз это выглядит достаточно печально.
20. Антон Стеклов (asved.ru) 33 09.01.17 19:02 Сейчас в теме
(15) От приведенного примера оптимизатор не спасет. Здесь результативна только эвтаназия.

Речь о ситуации, когда алгоритм исполнения запроса определяется исключительно на стороне СУБД.
21. mishgan mishgan (ganshinm@mail.ru) 09.01.17 19:05 Сейчас в теме
(19)
понятие устойчивости плана запроса знакомо.
применительно к данной теме, Вы видимо имели ввиду следующее:
1) есть запрос sel ect * from t1 inner join t2 ...., он выполняется много-много раз, его план в кеше
2) есть другой запрос select * fr om t2 inner join t1 ...., т.е. запрос точно такой же как и первый, но порядок таблиц другой (все остальное в точности совпадает, выборки одинаковые)

в это случае план второго запроса будет взят не из кеша, а построен заново и мы потеряем время построение плана.

так я Вам больше скажу - чтобы вытащить план из кеша, для СУБД имеет значение каждый пробел в тексте запроса, регистр всех символов и т.п., т.е. должно быть 100% совпадение текста запроса (хотя справедливости ради нужно отметить, что на этапе трансляции SDBL->SQL кое что "сглаживается").
22. mishgan mishgan (ganshinm@mail.ru) 09.01.17 20:33 Сейчас в теме
Господи, какой бред

- "Временная таблица должна индексировать всегда, если не доказано обратное, а не наоборот. Поля индексации временной таблицы выбираются так, чтобы СОЕДИНЕНИЕ строилось только по индексированным полям"
В большинстве случаев, временные таблицы совершенно не требуют индексирования

- "Выборка данных НИКОГДА не должна делаться через 2 точки, а явно подразумевает необходимые внутренние соединения внутри запроса."
Соединение через точку не несет ничего плохого. Какая разница, кто напишет left join - сам разработчик 1С, либо платформа.
Запрос на SQL будет одинаковый.
За исключением одного случая - обращение через точку к составному типу, например, к полю типа Справочник ссылка произвольного типа, либо документ ссылка произвольного типа.
В этом случае запрос на SQL будет содержать несколько десятков лишних джойнов и таких обращений через точку, конечно, следует избегать.
Но не нужно формулировать, что следует избегать вообще всех обращений через точку.

Нужно понимать, что плохо, а что нет. Автор нахватался всяких советов, не очень осознал их, такими статьями можно окончательно запудрить неокрепшие умы 1с-ников.
ipoloskov; Andrsan; 4rtehouse; +3 Ответить 3
23. Alexander Speshilov (speshuric) 930 09.01.17 23:06 Сейчас в теме
(21)
чтобы вытащить план из кеша, для СУБД имеет значение каждый пробел в тексте запроса, регистр всех символов и т.п., т.е. должно быть 100% совпадение текста запроса

Что правда что ли? А почему MS пишет что это не так?
Легко же проверить:
Код

Обратите внимание, что на первые 3 селекта к ttt один query_hash и на все четыре селекта к ttt один query_plan_hash
Berckk; ganshinm@mail.ru; +2 Ответить 3
24. Alexander Speshilov (speshuric) 930 09.01.17 23:27 Сейчас в теме
(22)
В современных версиях 1С (т.е. 8.3), если СУБД MS SQL (мы же про нее сейчас?), то платформа при создании индексированной ВТ достаточно разумно сначала создаёт кластеризованный индекс, а потом наполняет её данными. Если отсечь пограничные глупые случаи (типа "положи мне в ВТ всю базу" и "2 ГБ памяти на сервере и tempdb на самом медленном диске"), то заметить затраты на один единственный кластеризованный индекс вы не сможете.
Если у вас нет ни одного индекса на ВТ, а записей больше 2000, то мало того, что у вас единственная операция "просканируй меня", так еще и статистика начинает уплывать. Если сразу указать удачный индекс на ВТ, то SQL Server может его применить в соединениях, например. Так что рекомендация достаточно разумная: хуже чем куча вряд ли будет, а лучше - при минимальном здравом смысле - будет.
25. Петр Базелюк (pbazeliuk) 1284 09.01.17 23:31 Сейчас в теме
(22)
В большинстве случаев, временные таблицы совершенно не требуют индексирования

Индексирование уменьшит деградацию запроса при росте количества данных.
P.S. Из опыта одного проекта: при матричном управлении товарами с учетом характеристик и других показателей, временную таблицу необходимо несколько раз переиндексировать на разных уровнях иерархии. Без индексов расчет ассортиментной матрицы (25 складов) занимает больше часа, с индексами 30 секунд.
26. Alexander Speshilov (speshuric) 930 09.01.17 23:49 Сейчас в теме
(0) Кроме скриптов Брента Озара, я бы рекомендовал еще посмотреть на скрипты Гленна Берри. Пока Брент жмотил свои скрипты и давал ссылку только после регистрации - у Гленна уже они были доступны "по клику". Сейчас-то Брент выложил на github, но у меня осадочек остался.
Плюс у Гленна не создаётся ничего, голые селекты, а у Брента всё в ХП.
Ну и, конечно, у каждого набора скриптов есть свои нюансы, так что, по-хорошему, у DBA оба должны быть под рукой.
gadjik; JohnyDeath; +2 Ответить
27. Алексей Лустин (lustin) 832 09.01.17 23:57 Сейчас в теме
(22) началось... пошли слова "бред", "селективность", "нахватался".

специально для Вас в статье секция
Вы с чем то в статье не согласны?


(23) Саш - ты чего, это же классический "микровебинар" Брента Самый показательный в мире вебинар про сохраненный план запроса

shalimski; FirePyres; JohnyDeath; ganshinm@mail.ru; +4 Ответить 2
28. mishgan mishgan (ganshinm@mail.ru) 10.01.17 00:23 Сейчас в теме
(24)
Если я использую временную таблицу, то с большой вероятностью индекс будет не нужен.
Потому что
1) Во временную таблицу кладется относительно небольшая порция данных
2) Как правило эта временная таблица и так будет полностью прочитана. Т.е. full scan будет в любом случае, независимо от того, есть там индекс или нет.

Можно придумать, случай когда индексы на временную таблицу нужны. Например, мы кладет порцию данных во временную таблицу, а потом к ней делает 100 запросов, выбирая каждый раз по одной записи. В таком случае, конечно, индекс пригодится, но это неправильный сценарий использования временной таблицы.
29. mishgan mishgan (ganshinm@mail.ru) 10.01.17 00:29 Сейчас в теме
(23)
Спасибо. Кажется я где-то что-то пропустил.
Разберусь, возможно отпишусь.
30. mishgan mishgan (ganshinm@mail.ru) 10.01.17 00:32 Сейчас в теме
(24),(25),(0)
Приведите, пожалуйста, пример, достаточно часто встречающийся на практике, когда на ваш взгляд, использование индексов на временную таблицу чем то улучшило ситуацию
31. Alexander Speshilov (speshuric) 930 10.01.17 00:38 Сейчас в теме
(27) А я, кстати, не видел. Интересно, как он этого добился? У меня на 2016 на моём примере выдаёт один query_hash. Хотя с другой стороны - я помню как где-то "select ... from config where ..." с кучей разных гуидов в dm_exec_query_stats были (прямо по количетву объектов).
32. mishgan mishgan (ganshinm@mail.ru) 10.01.17 00:40 Сейчас в теме
(27)
Извините.
Если Вы не согласны с моими замечаниями, приведите пожалуйста примеры
- с индексированными временными таблицами (когда это хорошо)
- обращение через точку (когда это плохо)

Спасибо за ответ на (23)
33. Alexander Speshilov (speshuric) 930 10.01.17 01:07 Сейчас в теме
(32) Дискуссия комментарии примерно 70-80. Да, в 8.1 и 8.2 индексирование было сделано неэффективно. В 8.3 сделали разумно. Если смотреть планы и трассу, то на самом деле ускорение достаточно частое даже на банальном "В (Выбрать)". Особенно, если эта ВТ используется в нескольких фильтрах в запросе.

Тут еще 3 момента важны
1. Это часто проявляется не на простых запросах, а когда уже 1С-ных объектов в запросе давно считается десятками, а план запросов уже и в студии смотреть некомфортно. Тогда оптимизатор уже не строит из себя умника и его приходится водить за ручку.
2. Замерять время запроса в тестовой базе 1С - это хреновая метрика. Когда всё прокешировано - ограничением будет CPU, а когда эти запросы в бою потребуют IO, то CPU станет несущественным.
3. Какой попало индекс, конечно, не поможет.
34. mishgan mishgan (ganshinm@mail.ru) 10.01.17 01:32 Сейчас в теме
(33)
"Особенно, если эта ВТ используется в нескольких фильтрах в запросе."
В это случае индексирование ВТ может быть оправдано. Да и то не всегда.

Крайний случай я привел в (28) "Например, мы кладет порцию данных во временную таблицу, а потом к ней делает 100 запросов, выбирая каждый раз по одной записи"

Автор же предлагает "Временная таблица должна индексировать всегда, если не доказано обратное, а не наоборот".
Я как раз считаю обратное.
35. Никита Грызлов (nixel) 135 10.01.17 02:22 Сейчас в теме
(34)
> Автор же предлагает "Временная таблица должна индексировать всегда, если не доказано обратное, а не наоборот".
Я как раз считаю обратное.

У Вас с доказательством как-то тухло. Единственное, что я уловил из Ваших слов - там все равно будет table scan, какая разница.

ВТшки не так уж и редко бывают средними или даже большими по размеру. Table scan на таблицах со строками даже больше 100 уже может быть медленнее, чем индекс с хорошей селективностью.

Если в ВТ три строки или из этой ВТ нужно просто выбирать большую часть данных, то да, индекс может и не помочь. Но это все же не основной сценарий. А в общем случае рекомендовать индексы стоит.
36. Никита Грызлов (nixel) 135 10.01.17 02:25 Сейчас в теме
(35) из личного опыта - одно только грамотное расставление и попадание в имеющиеся индексы позволило сократить время выполнения ряда довольно тяжёлых запросов суммарно на 30-40 миллионов строк выборки с четырёх часов до 3 минут.
37. mishgan mishgan (ganshinm@mail.ru) 10.01.17 02:33 Сейчас в теме
(35)
>>У Вас с доказательством как-то тухло
У Вас тоже, коллега
Пример давайте
38. mishgan mishgan (ganshinm@mail.ru) 10.01.17 02:34 Сейчас в теме
(36)
Речь идет про временные таблицы. 30-40 млн строк во временные таблицы не кладут
39. mishgan mishgan (ganshinm@mail.ru) 10.01.17 02:39 Сейчас в теме
(35)
>>Table scan на таблицах со строками даже больше 100 уже может быть медленнее, чем индекс с хорошей селективностью.
Ответьте на вопрос - зачем во временную таблицу класть больше, чем планируете использовать?

40. Алексей Лустин (lustin) 832 10.01.17 02:57 Сейчас в теме
(35) Никит - я напомню. Мы пишем код на 1С.

Поэтому:

вот эта конструкция багом не считается - что там этих пользователей то ? 100-200 строк.

CRE ATE   TABLE #Users
    (
        ID          INT IDENTITY(1,1),
        UserID      INT,
        UserName    VARCHAR(50)
    )
    
    INS ERT IN TO #Users
    (
        UserID,
        UserName
    )   
    SEL ECT 
         UserID     = u.UserID
        ,UserName   = u.UserName
    FR OM dbo.Users u
...Показать Скрыть


а вот это считается

ВЫБРАТЬ
  Пользователи.Ссылка
ПОМЕСТИТЬ втПользователи
ИЗ Справочник.Пользователи как Пользователи
...Показать Скрыть


И я опять же - достаточно прозрачно добавил.
Если не доказано обратное
.
Если у Вас достаточно знаний и компетенций, чтобы это обосновать - Вам почет и уважуха. Если нет - будьте любезны индексировать временную таблицу в 1С.

Все что происходит сейчас в нашей дискуссии является классической ошибкой грамотного DBA. Он забывает что 1С:

* это некий такой ORM
* сегодня это MSSQL, а завтра что-то другое
* DBA всегда кажется что его знания про физику MSSQL достаточно для построения рекомендаций в части проектирования метаданных и выработки стандарта кодирования

несколько лет назад был такой же "холивар" про отсутствующие индексы.

Если подытожить, то получается - всем понравилась секция где я описал, на какие классические косяки 1С-ников мы нарвались в прошлом году. Если я правильно понял - я наступил на больную мозоль и основные участники считают:

* временные таблицы в 1С индексировать не надо
* контролировать количество соединений в 1С запросе не надо
* всё отдаем на откуп оптимизатора MSSQL - он магичен, он дальше разберется.

Как я указал выше - если где-то мы с Вами пересечемся: придется "похоливарить". Но извините за деньги вашего заказчика. Бесплатно мне что-то холиварить не хочется - в прошлом году мы и так задолбались объяснять прописные истины.
41. Алексей Лустин (lustin) 832 10.01.17 03:10 Сейчас в теме
(38) еще как кладут... да так кладут, что могут за сутки "полтеррабайта" через temdb прогнать.

Вообще - я на вебинаре покажу 2 метрики которую я всегда смотрю в части временных таблиц:

* объем данных проведенных через tempdb деленный на объем данных базы данных - если за сутки этот параметр составляет более 20%: ищи в коде помещение во временные таблицы больших объемов данных: косяк там.

если

exec sp_BlitzCache @ExpertMode = 1, @SortOrder = 'reads'


в качестве участника запроса выводит хоть что-то связанное с
#tt
- ищи временные таблицы в 1С без индексов.

В любом случае - это всегда проблема логики запросов, а не индексов на временной таблицы. Но как показывает практики - эффект достигается простым индексированием как заметили коллеги
42. mishgan mishgan (ganshinm@mail.ru) 10.01.17 03:33 Сейчас в теме
(40)
Ничего по делу нет. Я попросил пример ВЫБОРКИ, которая показывает, что индекс оправдывает себя.
Вы привели пример заполнения на TSQL и на 1C. При чем тут индексы?
43. mishgan mishgan (ganshinm@mail.ru) 10.01.17 03:40 Сейчас в теме
(41)
>>еще как кладут... да так кладут, что могут за сутки "полтеррабайта" через temdb прогнать.
Если кладут - это проблемы с головой того, кто кладет.
Видел я такие случаи. Выборка во временную таблицу - помещается, скажем, 10000 записей. Потом идет выборка из этой временной таблицы и всего выбирается, скажем, 100 записей.
И вот местный умник мне тоже рассказывает, что он добавил индекс и стало все круто и вообще индексы на временные таблицы рулят.
Все что надо в этом случае - наложить доп. ограничение и заполнять временную таблицу ровно тем количеством строк, которое реально используется. Тогда в большинстве случаев индекс не требуется.

ПС. Я реально видел случаи, когда во временную таблицу клали все проводки в базе (вообще все и это не шутка). Что ж теперь, считать это нормой?
44. Антон Стеклов (asved.ru) 33 10.01.17 05:54 Сейчас в теме
(21) До оптимизатора текст запроса не доходит вообще. Оптимизатор работает с результатом его декомпозиции и интерпретации. Поэтому у разных по тексту запросов могут быть одинаковые планы.
Изменение в тексте запроса, не меняющее его формальную логику, тем не менее, может влиять на результат интерпретации текста, делая неоптимизированный план ближе или дальше к считаемому оптимальным. Что влияет на скорость нахождения достаточно оптимального плана и на сам критерий достаточной оптимальности.

В общем, читайте ЕМНИП главы 10 и 11 Microsoft SQL Server internals, где-то там про это рассказывается. Но повторюсь, материя достаточно тонкая и большинству 1С-ников совершенно бесполезная.

Валидность кэшированного плана, опять же, определяется не текстом запроса, а на более глубоком уровне, в том числе и по состоянию таблиц. Если ВТ была удалена, например, откатом транзакции, а потом создана идентичная ВТ - план в кэше будет уже инвалид.
45. Антон Стеклов (asved.ru) 33 10.01.17 06:02 Сейчас в теме
(40) По индексации временных таблиц - сделайте простой нагрузочный тест: селект в индексированную и неиндексированную ВТ. Смотрите на IO по tempdb.
Речь о том, что при написании запроса необходимо оценивать объемы и сортировку выбираемых данных и принимать решение исходя из этого. Однако на практике это мало кто делает и рекомендацию индексировать ВТ в целом можно считать корректной - для большинства разработчиков.
brr; JohnyDeath; +2 Ответить 1
46. mishgan mishgan (ganshinm@mail.ru) 10.01.17 09:30 Сейчас в теме
(45)
Приведите хоть один пример, когда использование индекса во временной таблице оправдано.

>>Однако на практике это мало кто делает и рекомендацию индексировать ВТ в целом можно считать корректной - для большинства разработчиков.
Бредовая рекомендация, которая, конечно, устроит 95% 1с-ников, которые все равно не понимают ни назначения индексов, ни уж тем более смысла индексирования временных таблиц
47. Антон Стеклов (asved.ru) 33 10.01.17 09:38 Сейчас в теме
(46)
Любой случай, когда ВТ, достаточно большая для того, чтобы nested loops был невыгоден, джойнится с достаточно большим индексом. Потому, что merge join будет выгоднее hash join, а с неиндексированной - и следовательно не имеющей гарантии упорядоченности ВТ merge join невозможен.

Другой вопрос, каковы будут затраты на индексацию. Это зависит от сложности индекса и производительности tempdb.
ganshinm@mail.ru; +1 Ответить 2
48. Алексей Лустин (lustin) 832 10.01.17 09:52 Сейчас в теме
(42) уважаемый. Я сознательно Вам ничего не представляю... Когда закончите использовать формулировки типа "Ничего по делу нет". Вы умный, мы глупые - Вам удачи.
Dem1urg; Artem.Po; JohnyDeath; +3 Ответить 2
49. mishgan mishgan (ganshinm@mail.ru) 10.01.17 10:10 Сейчас в теме
(47)
Да, согласен, пожалуй единственный случай, когда использование индексов На временные таблицы оправдано.
50. mishgan mishgan (ganshinm@mail.ru) 10.01.17 10:17 Сейчас в теме
(48)

После того бреда который Вы написали в статье, а именно:

"
Неумением писать запросы на языке 1С с применением ключевого слова СОЕДИНЕНИЕ

Большинство разработчиков не понимают, что соединять нужно таблицы в порядке увеличения записей – то есть вначале с меньшим количество, а уже к ней с большим.

НЕ индексирование временных таблиц

Временная таблица должна индексировать всегда, если не доказано обратное, а не наоборот. Поля индексации временной таблицы выбираются так, чтобы СОЕДИНЕНИЕ строилось только по индексированным полям

Работа «с двумя точками»

Выборка данных НИКОГДА не должна делаться через 2 точки, а явно подразумевает необходимые внутренние соединения внутри запроса.
"

я бы вообще порекомендовал Вам завязать с практикой писать статьи и не проводить никаких вебинаров. Уровень глубины не тот.
Понимаю, конечно, что пипл хавает. Но если уж Вы путаете соединение с фильрацией....
51. Roman Seagal (4rtehouse) 10.01.17 11:09 Сейчас в теме
(48) Алексей, 3 пункта из статьи, описанные в (50), очень и очень спорные, может и не полный бред, но как минимум дезинформация.
По крайней мере в такой формулировке.
Видимо у вас накипело, и хочется дать универсальный совет для начинающих, как не накосячить по-крупному, но для специалиста с опытом видеть в такой статье такие советы... просто сразу возникают сомнения в компетенции автора...
ganshinm@mail.ru; +1 Ответить 1
52. Максим Кузнецов (Makushimo) 150 10.01.17 12:40 Сейчас в теме
Спасибо.
Как чек лист и шпаргалка идеальная статья.
в суперизбранное.
53. Иван Петров (dgolovanov) 10.01.17 14:47 Сейчас в теме
(51) Аргументировать можете?
54. Roman Seagal (4rtehouse) 10.01.17 15:46 Сейчас в теме
(53) по поводу СОЕДИНЕНИЙ сам автор в (15) прокомментировал, что имел ввиду - добавлять условия во временные (и, наверно, виртуальные) таблицы, а не в итоговую выборку из них. Т.е. не выбирать из БД то, что не пригодится.

А в статье написано это: "Большинство разработчиков не понимают, что соединять нужно таблицы в порядке увеличения записей – то есть вначале с меньшим количество, а уже к ней с большим."

т.е. если количество записей в Т1 больше, чем в Т2, то в запросе следует писать:
Т2 <ЛЕВОЕ|ПРАВОЕ?> СОЕДИНЕНИЕ Т1

я так понял...
55. Roman Seagal (4rtehouse) 10.01.17 15:55 Сейчас в теме
(53) Работа "с двумя точками"

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

Смущает безапелляционное "НИКОГДА".

56. Roman Seagal (4rtehouse) 10.01.17 16:32 Сейчас в теме
(53) Про индексы сказали уже достаточно, и MERGE JOIN даже упомянули (47) (очень важная вещь кстати).
Участвовать в этом холиваре не буду, лишь приведу пример, когда индексы во временных таблицах не нужны абсолютно:

ВЫБРАТЬ ...
ПОМЕСТИТЬ Т1
;
ВЫБРАТЬ ...
ПОМЕСТИТЬ Т2
;
ВЫБРАТЬ * ИЗ Т1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ * ИЗ Т2
57. Антон Стеклов (asved.ru) 33 10.01.17 19:48 Сейчас в теме
(56) Если забить на определение, какие поля следует индексировать, то я еще проще могу :D

ВЫБРАТЬ ...
ПОМЕСТИТЬ Т1
;
УНИЧТОЖИТЬ Т1
speshuric; JohnyDeath; +2 Ответить
58. Евгений Мартыненков (JohnyDeath) 290 10.01.17 19:50 Сейчас в теме
(56) вам не кажется, что в данном случае создавать Т1 и Т2 не нужно абсолютно?
59. Alexander Speshilov (speshuric) 930 10.01.17 19:56 Сейчас в теме
(56) Необходимость индексов в этом запросе сильно зависит от того, как используется Т1 во втором запросе ("ВЫБРАТЬ ... ПОМЕСТИТЬ Т2" - нигде не сказано, что Т1 в нем не используется :) )
60. Roman Seagal (4rtehouse) 11.01.17 09:40 Сейчас в теме
(58)Я просто имел ввиду - когда из временной таблицы выбираются все данные индексы зачем нужны?
Автор пишет ВСЕГДА нужны.

И даже если можно временную таблицу не использовать а выбирать данные прямо в основном запросе, я имею право её всё-таки использовать, хотя бы с целью упрощения всего запроса и последующей легкой поддержкой.
В примере запрос из двух таблиц, а если их будет порядка 50-ти?
61. Roman Seagal (4rtehouse) 11.01.17 09:42 Сейчас в теме
(59) Нет, с этим все понятно, когда есть какие-то отборы или соединения, индексы нужны, согласен. Здесь имелась ввиду просто выборка всех таблиц полностью.
62. Alexey (AlexeyFreeLife) 11.01.17 09:44 Сейчас в теме
По-моему, на автора статьи сильно наехали НЕ заслуженно - человек старался, писал - вещи умные. Выдал в свободный доступ для использования и размышлений. Большинству разработчиков это точно не помешает - а кто обладает более глубокими знаниями просто может ими поделиться, а не пытаться уменьшить значимость данного материала.
fuxic; shalimski; headMade; vovan_victory; Artem.Po; +5 Ответить 1
63. Евгений Мартыненков (JohnyDeath) 290 11.01.17 10:05 Сейчас в теме
(60)
я имею право её всё-таки использовать, хотя бы с целью упрощения всего запроса и последующей легкой поддержкой.

Вот из-за такого подхода и появляются такие споры и статьи.
Вам лишь бы проще поддерживать, а кому-то очень критична скорость работы.
Тоже самое и с "пятью точками": ведь проще написать и "поддерживать" пять точек, чем 5 джойнов (или вызов какой-то серверной ф-ии, если речь идет о разыменовании в реквизитов объекта в коде, а не запросе)
64. Евгений Мартыненков (JohnyDeath) 290 11.01.17 10:07 Сейчас в теме
(62) Автор уже выразил свое отношение ко всем "протестующим" строками прямо в статье: "Есть про что рассказать - велкам! Почитаем вашу статью"

Помимо очень полезной информации в статье, из нее еще и неплохой вброс получился ;)
65. Roman Seagal (4rtehouse) 11.01.17 10:25 Сейчас в теме
(63) Какого "такого подхода"?
Ситуации разные бывают, здесь мне так захотелось, а там по-другому. Мне опыт позволяет решения принимать, т.к. знаю как это работает на более низком уровне.
Раньше тоже стремился оптимизировать всё и вся, сейчас пришел к тому, что главное - быстро сделать то что, нужно бизнесу, далее легкая поддержка.
Рефакторинг только для себя.
Оптимизация только того, на что жалуются, т.к. это самая неблагодарная работа.

А в статье меня не суть этих советов смущает, а их формулировка.
Если бы автор так написал, вопросов бы вообще никаких не было:

"... Рефакторинг 1С решений – это также тема отдельной статьи, но как показывает наша практика на 2016 год основными проблемами являются:

Избыточная выборка данных из БД
(Отсутствие условий в больших временных и виртуальных таблицах)
Отсутствие индексов у больших временных таблиц
Обращение к полям составного типа «через две точками»
Неоптимальное использование конструкции В(&СписокИлиТаблица)
Неверное использование конструкции ПОДОБНО


подробности в отдельной статье ;)
..."
66. Евгений Мартыненков (JohnyDeath) 290 11.01.17 11:15 Сейчас в теме
(65)
Какого "такого подхода"?

подхода "лишь бы быстрей и проще для разработки"

Ведь если вы ВТ будете всегда добавлять индексы, то как минимум хуже от этого не станет. Верно?
67. Олег Веселов (sml) 36 11.01.17 11:26 Сейчас в теме
(50) согласен с этим.
Единственный случай, когда пришлось индексировать ВТ:
Выбрать Ссылка, ТипНоменклатуры
ПОМЕСТИТЬ ВТ1
ИЗ Справочник.Номенклатура
ГДЕ
Родитель В &СпГрупп

;
...Показать Скрыть

В дальнейшем ВТ1 соединяется с таблицей регистра и по Номенклатуре, и по Типу с другим Регистром

Индекс строил по Типу

В остальных случаях выборка в ВТ должна быть минимально достаточной для дальнейшей работы и не требует индексов
68. Иван Петров (dgolovanov) 11.01.17 11:35 Сейчас в теме
(66)
Ведь если вы ВТ будете всегда добавлять индексы, то как минимум хуже от этого не станет. Верно?

"Как минимум" на создание индекса тратится время. Запрос с индексами может работать дольше, чем запрос без индексов.
69. Иван Петров (dgolovanov) 11.01.17 11:39 Сейчас в теме
Коллеги, а вы на курсы по вождению ходили? Экзамен в ГАИ сдавали? Тоже на каждый совет инструктора слюной брызгали "да я так делал и нормально", "адекватному водителю можно"? Есть правила - подходят 90%, есть некие отклонения, когда ты отдаешь себе отчет - это остаток, малая часть. А вы всю статью на какие-то частности растащили.
JohnyDeath; +1 Ответить
70. Roman Seagal (4rtehouse) 11.01.17 12:04 Сейчас в теме
(66) Понимаете в чем дело, я ведь раньше с таким лозунгом и жил. И всем джуниорам говорил - "Когда создаете ВТ обязательно индексируйте все поля, которые потом используете в условиях соединений!"
И вот, однажды, один товарищ сидит, отчет оптимизирует, и спрашивает меня: "Вот ты говорил всё индексировать, а я сюда индекс добавляю и запрос на несколько секунд дольше работает. Как же так, Рома?"
Можно объяснять про затраты на создание индекса, про то что объем данных потом вырастет - индекс пригодится...
Тут вопрос в доверии.
Т.е. человеку профессионал с многолетним стажем дает рекомендацию, а потом он на личном опыте видит, что рекомендация даёт обратный результат. И доверие к этому "профессионалу" уже не то...
71. Alexander Speshilov (speshuric) 930 11.01.17 12:52 Сейчас в теме
(68) А можно конкретный пример? Сколько тратится времени? Как я уже писал выше - в 8.3 в ВТ создаётся кластеризованный индекс до вставки строк. При этом на самом деле разницу между вставкой в кучу и в КИ не так-то просто заметить.
Заметная разница в пользу кучи может быть если:

1. Один план параллельный, другой нет. Но если у вас параллельные планы при заполнении ВТ, то у меня для вас плохие новости: либо у вас ВТ огромная , либо вам надо что-то делать с запросом или структурой БД вне зависимости от ВТ.
2. Одна операция минимально протоколируемая, другая - нет. Да, вставка в пустую кучу будет "чаще" минимально протоколируемой. Только если у вас узкое место - журнал транзакций на заполнении ВТ, то опять же - это значит, что она огромная и у вас проблема гораздо глубже.
3. Вы выбрали очень неудачный индекс и для сортировки по кластеризованному индексу сгенерирован "плохой" план. Ну так возьмите более удачный индекс.

Поэтому, те кто утверждает, что на индексирование тратится время: пруф в студию. Только а) на свежих 8.3 и MS SQL 2012-2016 (лучше на 2016 с новым cardinality estimator), б) воспроизводимый, в) без параллельных планов, г) с планом запросов и с заметной (т.е. более 20%) разницей на writes в tempdb и duration д) временная таблица, конечно, не с миллионами-миллиардами строк. Эта задача выполнима, но те кто её могут выполнить, уже точно не нуждаются в рекомендациях, как писать запросы.
Bronislav; Berckk; Sergey.Noskov; JohnyDeath; nixel; +5 Ответить 2
72. Alexander Speshilov (speshuric) 930 11.01.17 12:57 Сейчас в теме
(71) Забыл еще случай, когда в ВТ куча дублирующихся строк, но это частный случай п.3. И так тоже не надо делать (или взять другие поля для кластеризованного индекса)
73. Денис Козлов (Akbis) 43 11.01.17 13:26 Сейчас в теме
(71) Ну понятно что какое-то время тратится, а также надо учитывать текущую нагрузку на базу.
Рекомендация 1С "для всех" - индексировать все ВТ. Так стабильнее если разрастется база, сменится СУБД, разброс данных поменяется.
Дальше уже кто шарит, понимает что далеко не всегда индексирование дает пользу.
А иногда даже забирает лишние милисекунды.
74. Alexander Speshilov (speshuric) 930 11.01.17 13:31 Сейчас в теме
(73)
понятно что какое-то время тратится
Непонятно. Пруф с предложенными ограничениями есть? Сколько именно потрачено лишнего внемени и IO?
75. Денис Козлов (Akbis) 43 11.01.17 13:34 Сейчас в теме
(74) А по вашему время не тратится?
Индексировать можно все подряд на здоровье?)
76. Евгений Мартыненков (JohnyDeath) 290 11.01.17 13:37 Сейчас в теме
(73) вот вас и просят показать эти "иногда даже забирает лишние милисекунды".
77. Денис Козлов (Akbis) 43 11.01.17 13:39 Сейчас в теме
(76) Именно на 8.3 и 2016 сервере?
78. Alexander Speshilov (speshuric) 930 11.01.17 14:01 Сейчас в теме
(75) А что такое "тратится"? На ВТ возможен ровно один индекс, причем кластеризированный (КИ). Причем ВТ всегда на начало заполнения - пустая (это, кстати - предпосылка к неполному протоколированию вставки). Если параллельных планов нет, и планы выполнения "похожи", то скорее всего у вас будет одно из двух ограничений - либо получение данных запросом (но у нас же планы похожи), либо скорость вставки. Скорость вставки в кучу "типа" выше, пока мы не вспомнили про RID (которых может не быть в КИ, если значения уникальны). Так, если речь идет о ВТ со списком уникальных ссылок на один объект метаданных, разница в размере может быть почти в 1,5 раза в пользу кластеризованного индекса.

Если планы похожи, но в случае КИ есть сортировка, а в случае кучи - нет и на этом разница производительности больше 20% для запроса заполнения ВТ, то у вас очень большая ВТ. Не забываем, что это обычно лишь маленький этап большого пакета. Насколько оправданно создание такой ВТ в принципе? Насколько она полезна без индекса?

Если планы разные, то сложно вообще говорить в терминах "тратится". Просто один запрос с эффективным планом, а второй - нет. Я представляю, как заставить SQL свалиться в эту ситуацию, но а) я знаю как с этим бороться б) мне сложно придумать практическое применение результатам такого запроса. Именно поэтому я и прошу воспроизводимую ситуацию.
79. Alexander Speshilov (speshuric) 930 11.01.17 14:04 Сейчас в теме
(77) 8.3 - обязательно. В (33) ссылка почему 8.2 и 8.1 не подойдут. SQL можно и 2008, но начиная с 2014 появился новый cardinality estimator и может случиться так, что вы просто докажете, что пора переходить на свежий SQL.
dgolovanov; +1 Ответить 1
80. Антон Стеклов (asved.ru) 33 11.01.17 18:08 Сейчас в теме
(79)
начиная с 2014 появился новый cardinality estimator

А вы знаете, почему платформа сейчас требует полномочия sa на MSSQL2014?
roma_semenov79; +1 Ответить 1
81. Евгений Алексеев (BaldRzn) 11.01.17 18:38 Сейчас в теме
Какой-то странный холивар организовали.
Естественно не стоит воспринимать слова в статье как прямое руководство к действию. Естественно нужно их "переварить" и применять обдуманно.
Все претензии - к немного "обобщенным" формулировкам на мой взгляд.
Алексей - спасибо за статью.
83. Антон Стеклов (asved.ru) 33 12.01.17 13:52 Сейчас в теме
(82) Ну, еще один шаг. Что делает T4199.
84. ash (ashvik) 12.01.17 14:45 Сейчас в теме
(83) Включает исправление, которое по умолчанию отключено https://support.microsoft.com/en-us/kb/974006
85. Марат Хафизов (Painted) 16 11.05.17 12:00 Сейчас в теме
В некоторых версиях MS SQL затираются индексные DVM-ки при ребилде индексов. Поэтому довольно опасно доверять "sp_BlitzIndex", который собирает информацию с этих DVM. Там не представительная статистика при частом ребилде.
86. friend0 11.05.17 18:03 Сейчас в теме
Решил снова проверить, вдруг действительно в 8.3 чудо-чудное с индексированием временных таблиц сделали. Но чуда не произошло. Может конечно дело в том что скуль 2012, но другого у меня нет.

В обработке запускал 4 запроса (бухия, 94 тыщи строк) на основе:
	ЗапросН.Текст = "ВЫБРАТЬ 
	                |	ДоговорыКонтрагентов.Ссылка,
	                |	ДоговорыКонтрагентов.владелец,
	                |	ДоговорыКонтрагентов.СрокДействия
	                |ПОМЕСТИТЬ ттт
	                |ИЗ
	                |	Справочник.ДоговорыКонтрагентов КАК ДоговорыКонтрагентов
	                |;
	                |
	                |////////////////////////////////////////////////////////////­////////////////////
	                |УНИЧТОЖИТЬ ттт";
...Показать Скрыть

1. Исходный
2. Индекс: Ссылка
3. Индексы: владелец, СрокДействия
4. Индексы: Ссылка, владелец, СрокДействия

Если не брать во внимание первоначальное создание таблиц, то результаты на картинке
Прикрепленные файлы:
87. Sergey Andreev (starik-2005) 1003 11.05.17 21:08 Сейчас в теме
(86) а что за циферки-то? Создал и удалил? И все? Полагаю, что если потом эти ВТзаюзать длячего-либо, может возникнуть профит.
88. friend0 12.05.17 01:55 Сейчас в теме
(87)Может возникнуть, а может не возникнуть. Вопрос стоял в том, что лучше: бездумно создавать или бездумно не создавать индексы для временных таблиц. Я всегда был за второй вариант (создание индексов полюбому требует ресурсов, а будут ли они использоваться - это бабушка надвое сказала). Некоторые товарищи в теме утверждали, что индексы волшебным образом не требуют доп. ресурсов - вот я и решил проверить.

П.С. Циферки стандартные профайлеровские: CPU, Read, Write, Duration. Большие циферки - собственно чтение-вставка, маленькие - уничтожение.
89. Alexander Speshilov (speshuric) 930 12.05.17 02:42 Сейчас в теме
(88) Ну и посмотрите внимательно на свои результаты.
1. Варианты 3 и 4 по факту меняют план запроса, остаётся сравнивать однократный запуск 1 и 2.
2. Writes поровну. Чтений почти поровну. Duration отличается на 22% ( (135-104)/135 ). Отсутствие индексов, если эта ВТ используется почти гарантированно будет дороже.
3. Формулировка в 3 и 4 неправильная: не "Индексы", а "Индекс" - на ВТ он в 1С всегда один. Это важно.
JohnyDeath; +1 Ответить
90. Sergey Andreev (starik-2005) 1003 12.05.17 10:20 Сейчас в теме
(88)
Может возникнуть, а может не возникнуть.
Нужно сделать так, чтобы возник. А еще нужно мозг напрячь и подумать, а возникнет ли вообще профит от создания ВТ или соединения достаточно (многие тут мозг в принципе включать не умеют - учиться надо!).

С другой стороны, создавать индексы в ВТ нужно тогда и только тогда, когда с этой ВТ в последствии идет соединение. И поля для индекса нужно выбирать с учетом селективности и последующего использования в соединении. Если это организация, коих три штуки, то смысла в индексе нет никакого, ибо логарифм по основанию два из трех - это где-то почти 2 (для тех, кто вообще понимает, как все ищется при нестед лупсах). Если же соединение идет по низкоселективным полям, то, возможно, вообще не стоит по ним соединять, а вынести это в условие.

В общем нужен мозг. И не нужно всю базу в ВТ засовывать, чтобы потом три записи получить - это все азбука. А если нужно всю базу в отчет засунуть, то тут ВТ вообще не нужны - просто селекти все и не парь мозг никому.
Оставьте свое сообщение