Складові типи 1с, стань експертом в 1с
З огляду на поточний плачевний стан наших програм, можна сказати, що програмування виразно все ще чорна магія і, поки, ми не можемо називати його технічної дисципліною.
Приводом для написання статті став черговий розбір такої помилки:
Server: Msg 8632, Level 17, State 2, Line 1
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
Як зберігаються дані складеного типу в базі даних
На перший погляд все досить просто. Поле складеного типу в базі даних представляється двома, трьома або більшою кількістю окремих полів:
Радує хоча б те, що в цей бардак запхати рядки необмеженої довжини і сховище значень не вийде 🙂 На цьому етапі зауважимо, що навіть незаповнені поля складеного значення (крім строкового типу змінної довжини) зберігають значення максимального з точки зору зберігання розміру, а саме:
Це призводить до помітних зайвих витрат на дисковий простір і пам'ять сервера СУБД навіть не зважаючи на те, що дані зберігаються вирівняними по 8-кібібайтним сторінок.
Слідство 1: складовою тип призводить до помітного збільшення розміру поля в запису.
Ну ладно, нехай на диску база займає не 30 гинув, а 35 гинув - хто ж ці копійки вважає і помітить? Помітить як мінімум MS SQL Server, коли буде будувати індекси. Максимальна довжина індексу 16 полів або 900 байт. Ці кордони найчастіше порушуються саме на регістрах відомостей з складовими полями в вимірах. До речі, про індекси, а що взагалі буде, якщо ми індексуємо складене поле? У найпростішому випадку, якщо, наприклад, ми індексуємо складене поле довідника (без доп. Упорядкування), буде побудовано кілька індексів на кожен базовий тип:
До п'яти індексів. На щастя, в самому частому випадку, коли в складеному типі можливо кілька довідкових полів, і немає інших типів, індекс всього один. Але індекси по одному полю - це ще не біда. Біда настає, коли складові поля з декількома індексами стають вимірами в регістрах. Адже платформа спробує створити індекс на кожну комбінацію базових типів (в регістрах відомостей на основну таблицю регістра, в регістрах накопичення і бухгалтерії - на таблицях підсумків). Всього 3 «універсальних» виміру і у вас 126 індексів (для регістра відомостей 5 * 5 * 5 по складовим полях + 1 за спеціальним внутрішньому полю). Мабуть через те, що в MS SQL Server більше 254 індексів побудувати не можна, четверте і наступні поля в індексах просто не беруть участь, але і до помилки не приведуть.
Наслідок 2. Для складових полів може створюватися багато індексів. Для регістрів відомостей з складовими вимірами ДУЖЕ БАГАТО індексів.
Індекси ці займають місце на диску, займають час на вставці / зміну / видалення записів, вимагають регулярного обслуговування. Хтось скаже: «Та й добре! Нехай займають, зате вибірки прискорюють! ». Чи так це? Для операцій пошуку по одному значенню - так. У багатьох інших випадках - ніяк немає.
Приклад. Нехай у нас є Справочнік1 з складовим полем СоставноеПоле (може містити число або посилання на Документ1) і Справочнік2 з таким же складовим полем СоставноеПоле (може містити число або посилання на Документ1). СоставноеПоле індексовано в обох довідниках. Є запит виду:
Індекси? Забудьте. Якщо ви все ще пишете подібні запити, то можете відразу відкласти 120 000 рублів і написати заповіт заяву про звільнення.
Наступний виток жаху починається при зверненні до реквізитів реквізитів. Нерідко ледачі програмісти пишуть щось типу Регістратор.Основаніе.Контрагент або Субконто1.Владелец.Код.
Приклад. Нехай у наших документів з попереднього прикладу є ще поле Підстава, причому воно може бути складовим (РасходнаяНакладная і ПриходнаяНакладная) і є поле Контрагент. Подивимося, як буде виконуватися запит:
А виконуватися він буде ось так (в квадратних дужках вказані поля СУБД, що є «частинами» складеного поля):
У нашому гранично простому прикладі цей запит призводить до 6 з'єднанням 7 таблиць, причому всі 7 таблиць мають великі шанси потрапити на повне сканування кластерізованного індексу (тобто по суті сканування таблиці) замість використання відповідних індексів, навіть якщо такі є. Не відмахується «у мене база маленька і так зійде»! Продуктивність такого запиту в деяких випадках може падати як ступінь від кількості таблиць. Що це означає на практиці? На практиці це означає, що навіть в нашому примітивному випадку запит може сповільнитися в 128 разів при збільшенні кількості документів вдвічі або сповільнитися вдвічі при збільшенні кількості документів на 10%. У типових конфігураціях, де в складеному типі можуть бути десятки і сотні простих, використання таких запитів майже завжди приведе з суттєвих проблем продуктивності. Ще одним негативним моментом можуть стати надлишкові блокування при виконанні таких запитів в транзакціях (наприклад, при проведенні документів): мало того, що запит виконується все довше і довше, так ще й всі інші користувачі постійно відвалюються з таймаут блокування.
І вже зовсім сумною стає робота цього запиту, коли складовою тип може містити не тільки посилання: умови і з'єднання стають ще більш громіздкими і швидко деградуючими.
Чи не обговорюючи коректність або некоректність вихідної задачі, спробуємо зрозуміти, чи можна виправити такий запит «малою кров'ю» без зміни архітектури рішення? Цілком можливо, але ціною цього стане розмір і Новомосковскемость запиту:
У цьому варіанті стало трохи більше з'єднань, але всі вони будуть гарантовано мати належні індекси. На додаток до цього планувальником запитів MS SQL Server дана трохи більша свобода у виборі планів за рахунок застосування у внутрішньому сполученні - воно симетрично щодо таблиць і в даному випадку точніше відображає суть.
У цього варіанту, взагалі кажучи, плани виконання можуть виявитися менш стабільними і сильно залежати від статистики даних. Зате в цьому варіанті менше з'єднань і при наявності відповідних індексів (і вдалого збігу зірок над планувальником) він може виявитися швидше.
Тепер ви розумієте, чому скромне вираз Субконто1.Владелец в запиті по залишками регістра бухгалтерії, і тим більше в частині ДЕ цього запиту, на мене наганяє паніку?
Слідство 8. Для отримання реквізиту значення складеного типу відбувається кілька лівих з'єднань, їх може бути досить багато і це може погіршити роботу запиту. Для отримання реквізиту від реквізиту значення складеного типу (Поле1.Реквізіт1.Реквізіт2, де хоча б Поле1 складене) використовуються вкрай громіздкі і неефективні конструкції.
Не завжди при використанні складеного типу є «хороше» рішення. Наприклад, якщо значення складеного типу виводиться в звіті, то постає дилема:
Слідство 9. Якщо отримувати уявлення складеного типу в запиті, то це призводить до великої кількості з'єднань. Якщо отримувати потім при виведенні, наприклад, звіту, то це призведе до великої кількості запитів. Хороших новин немає.
Використання значень складових типів в складних виразах в запитах
Окремо хотілося б показати, як реалізована робота з складовими типами в деяких висловах мови запиту:
- Агрегатні функції (мінімум, максимум, кількість)
- Використання в виразах ВИБІР ... КОЛИ ... КІНЕЦЬ
Буду демонструвати приклади на такий часовий таблиці: