Нотатки з фронту оптимізації

Добрий день друзі! Сьогодні я вам розповім історію про оптимізацію типового запиту УПП при відновленні послідовності розрахунків. Буде багато букв, але цікаво. Оптимізувати будемо трьома шляхами: Beginner, medium, high. Поїхали!

1. Beginner level

Надійшла скарга, на обробку одного документа при відновленні послідовності розрахунків йде до 10 секунд. Простим виміром був знайдений запит, який з’їдає 98% часу. Я його трохи спростив для зручності. Ось наш винуватець:

Перша ВТ в оригіналі інша, але там, як правило, 1 рядок. У регістрі РасчетиПоРеалізацііВУсловнихЕдініцахОрганізаціі 3.5 млн рядків. Запит відпрацьовує за 7-10 секунд.

Хто читав мої попередні замітки, міг звернути увагу на упорядкування

Оскільки «Документ» складеного типу, то сервера доведеться підтягувати всі типи документів.

Але в даному випадку це не сильно уповільнює запит – вдало використовується пошук по кластерному індексу таблиць документів.

У профайлером SQL запит видав такі значення:

У плані запиту є цікава рядок

Що ми тут бачимо? А бачимо ми 967126 читань нашої тимчасової таблиці. Це відбувається ось тут:

Коли ми робимо таку умову, SQL сервер при перевірки умови кожен раз звертається до тимчасової таблиці. Тут ми і чекаємо 10 секунд. Що ми можемо зробити?

А ось зараз ми згадаємо, що тимчасові таблиці можна індексувати! І, звичайно, всі ми це робимо! Беремо і індексуємо ВТ по полях, використовуваним в відборі:

… і виконуємо наш запит …

Запит тепер стабільно виконується за 2-2.5 секунди, проти 6-10. Відмінний результат з мінімумом зусиль. Тому, намагайтеся індексувати тимчасові таблиці по полях з’єднань, умов.

Beginner level пройдено!

2. Medium level

Але в нашій ситуації 2-2.5 секунди теж багато. До того ж очевидна неоптимальність запиту. Немає сенсу нам читати ВТ майже мільйон разів.

І зараз ми зробимо цікавий поворот. Нам багато разів говорили, що треба максимально використовувати параметри віртуальної таблиці. Тут так і зроблено. А ще деякі з вас знають, що з’єднання з віртуальними таблицями – це погано, тому що віртуальна таблиця, по суті, це вкладений запит, а з’єднання з вкладеними запитами нестабільні по продуктивності.

І зараз ми ігноруємо ці 2 правила :). І пишемо наступне:

Раптово, запит відпрацьовує за 0.1 – 0.6 секунди. Давайте розберемося, чому так. Ось дані профайлера:

Подивіться, наскільки менше стало читань. Звідси і час в 123 мс і слабке навантаження на CPU. А ось і розгадка:

Ми 1 раз читаємо нашу ВТ при внутрішньому сполученні. Тому і така висока швидкість. Але треба сказати про 2 нюансу:

  1. Внутрішнє з’єднання і конструкція «В» це не одне і те ж. Не забуваємо, що конструкція «В» шукає тільки рядки з 1-ої таблиці. А з’єднання перемножує таблиці. І якщо за умовою в 2-ій таблиці знайдеться більше одного рядка, то в результат також піде більше 1 рядка. Цей момент треба врахувати обов’язково.
  2. Конструкція, задовольняє наших умов повністю, але тільки з невеликою кількістю рядків у ВТ, тому що вся конструкція нестабільна. А нестабільна вона через з’єднання віртуальної таблиці залишків і тимчасової таблиці. При такій ситуації оптимізатор СУБД може вибрати невірний план запиту і ми можемо піти в ті ж 10 секунд, а то і набагато більше. Це може відбутися не відразу, а при зростанні розмірів таблиць, наприклад. Ось такий випадок був при виконанні цього ж запиту, але з іншим періодом:
    76 секунд … Це говорить від тому, що оптимізатор в цей раз вибрав невірний план виконання запиту. Далі ми підкажемо йому, як завжди вибирати вірний план! Але це вже буде high level!

Medium level пройдено!

3. High level

Ну що, друзі, далі ми будемо використовувати зовсім чорну магію. Особливо вразливим читати не рекомендую ?.

Зараз ми зробимо наш запит стабільним при будь-якій кількості даних, а також заглянемо всередину віртуальних таблиць.

Всі ми знаємо, що віртуальні таблиці залишків, оборотів і т.д. формуються під час запиту і не зберігаються в БД. Однак, це не зовсім так. В БД зберігаються розраховані підсумки на кожен місяць. Виглядають вони приблизно так:

І коли ми хочемо отримати залишки, скажімо, на 14 липня, 1С отримує з таблиці підсумків залишки на 01.08 і забирає дані за дні з 14.07 по 31.07. Або може отримати дані на 01.07 і додати дані за 14 днів.

Так ось, ми зараз спробуємо зробити свою таблицю залишків з блекджек і грамотними сполуками. Ось перша частина запиту:

Цей запит виглядає точно як минулий, але зверніть увагу на параметр Початок місяця. Тобто ми беремо дані не на шукану дату, а на початок місяця шуканої дати. А на цю дату як раз є розраховані підсумки.

У підсумку в даному запиті йде з’єднання нашої проіндексованою тимчасової таблиці і проіндексованою реальної таблиці підсумків на початок місяця. Час виконання 10-20 мс.

Далі ми підтягнемо об’єднанням з реальної таблиці дані за відсутні дні:

У цьому запиті ми з’єднуємо реальну таблицю регістра з нашої ВТ, що теж добре і зрозуміло для оптимізатора. Далі об’єднуємо, групуємо і т.д.

Тут треба бути уважним і використовувати кордону, щоб не втратити секунду, або нехтувати секунду двічі. А також не забуваємо про «активність», тому що використовуємо реальну таблицю.

У підсумку ми отримали запит, який стабільно виконується за 100-300 мс при нашому кількості даних. А також, передбачувано поводиться при різних умовах і зростанні таблиць БД.

Ссылка на основную публикацию