Оптимізація запитів в Підприємство 8

Spilka-IT Статті експертів 26 червня 2018 Перегляди: 7

Оптимізація запитів в Підприємство 8

Зміст:

Одним з найважливіших питань підвищення продуктивності прикладного рішення є оптимізація роботи запитів 1С:Підприємство. Неефективний запит в 1С:Підприємство може призводити до істотної втрати продуктивності рішення, а в окремих випадках - до збою системи.

У клієнт-серверній архітектурі платформа 1С:Підприємство транслює запит, створений розробником в запит до бази даних на мові запитів SQL. Оптимізатор запитів СКБД (а мова запитів 1С:Підприємство підтримує роботу з наступними СКБД: MSSQLServer, PostgreSQL, IBMDB2, OracleDatabase) будує план запиту. План запиту можна подивитися засобами самої СКБД.

1. Загальні рекомендації для запитів СКБД

У запиті потрібно прагнути до вибору тільки необхідних полів.

Пакетування запитів (використання тимчасових таблиць) підвищує їх читаність. Об'ємний запит може призвести до вибору неоптимального плану оптимізатором запитів (на стороні програми СКБД).

У поєднанні (двох таблиць) не слід використовувати підзапити.

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

Ніколи не використовувати запити в циклі.

Відсутність значень параметрів у віртуальній таблиці або використання замість них умови «ДЕ».

Відсутність перевірки на NULL

Не отримувати дані через точку від полів посилального типу.

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

2. Рекомендації для роботи з запитами в 1С:Підприємство

Ось деякі рекомендації, необхідні для оптимізації запитів:

Коли запит стає великим, використовуються тимчасові таблиці.

Якщо в умові є індексований стовпець, то краще комбінувати рівності й нерівності. Наприклад, краще писати значення_колонки> = 8, ніж значення_колонки> 7. Так як в останньому випадку спочатку буде знайдено число 7, а потім буде проводитися пошук таких значень. У першому випадку пошук відразу починається з 8.

При зверненні до індексу за кількома стовпцями необов'язково вказувати в запиті кожен стовпець. Але починати треба з першого стовпця і продовжувати в порядку індексації. Використання індексу припиняється при першому пропуску стовпця.

Часто об'єднання запитів буває краще, ніж один запит, оскільки окремі запити виходять простіше і краще піддаються оптимізації. До того ж в деяких СКБД окремі запити виконуються паралельно.

Використання індексів має деякі недоліки. На них витрачаються такі ресурси:

Індекс сам по собі займає місце (додатково до того, що займає таблиця).

При вилученні з таблиці великого числа рядків використання індексу - тільки втрата часу. Це трохи сповільнить виконання.

Розробнику необхідно вирішити, чи використовувати індекс в залежності від наступних моментів.

Як часто буде виконуватися запит до таблиці, і як часто вона буде оновлюватися.

Наскільки повільно виконується запит.

Що в даному випадку важливо: час виконання або місце на диску. Зазвичай час цінніше.

Як часто поля, які будуть індексуватися, будуть використовуватися в умовах запиту.

Як часто поля будуть використовуватися в умовах з'єднання. З'єднання в основному виконуються повільніше, ніж інші види запитів, і виграш у часі виходить більше.

3. Приклади оптимізації запитів.

Замість нерівності в умовах запиту краще використовувати рівність, оскільки більшість ПП проігнорують індекси, так як у них передбачається, що нерівність буде виконана для більшості значень.

Неоптимальний запит

Оптимальний запит

ВИБРАТИ

Заявки. Номер Покупки ЯК Номер Покупки,

Заявки. Дата Покупки ЯК Дата Покупки,

Заявки. Кількість ЯК Кількість

Довідник. Заявки ЯК Заявки,

Довідник. Покупці ЯК Покупці

Де

НЕ Заявки. Кількість<1000

І Заявки. Номер Покупця =

Покупці. Номер Покупця

ВИБРАТИ

Заявки. Номер Покупки ЯК

Номер Покупки,

Заявки. Дата Покупки ЯК Дата Покупки,

Заявки. Кількість ЯК Кількість

Довідник. Заявки ЯК Заявки,

Довідник. Покупці ЯК

Покупці

Де

Заявки. Кількість> =1000

І Заявки. Номер Покупця =

Покупці. Номер Покупця

Не слід використовувати АБО в секції ДЕ запиту.

Це може призвести до того, що СКБД не зможе використовувати індекси таблиць і буде виконувати сканування, що збільшить час роботи запиту і ймовірність виникнення блокувань. Замість цього слід розбити один запит на кілька і об'єднати результати.

Неоптимальний запит

Оптимальний запит

ВИБРАТИ

Довідник.Покупці ЯК Покупці

ДЕ

(Покупці.НомерПокупця> 2002

АБО Покупці.Місто = "London")

ВИБРАТИ

Довідник.Покупці ЯК Покупці

ДЕ

Покупці.НомерПокупця>= 2003

ОБ’ЄДИНИТИ

ВИБРАТИ

Довідник.Покупці ЯК Покупці

ДЕ

Покупці.Місто = "London"

Відсутність значень параметрів у віртуальній таблиці або використання замість них умови «ДЕ».

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

Важливо ще розуміти, що в разі використання, наприклад, віртуальних таблиць Зріз Перших і Зріз Останніх (регістр відомостей) обидва варіанти дадуть різний результат. Зріз Перших і Зріз Останніх відбирають не всі записи регістру відомостей, а тільки перші або останні за часом.

Неоптимальний запит

Оптимальний запит

ВИБРАТИ

ВТ.Номенклатура,

ВТ.Кількість Залишок,

ВТ.Склад

РегістрНакопичення.ТовариНаСкладах.Залишки

ЯК ВТ

ДЕ

ВТ.Номенклатура В ІЄРАРХІЇ(&Номенклатура)

І ВТ.Склад = &Склад"

ВИБРАТИ

ВТ.Номенклатура,

ВТ.Кількість Залишок,

ВТ.Склад

РегістрНакопичення.ТовариНаСкладах.Залишки(

Номенклатура В ІЄРАРХІЇ (&Номенклатура)

І Склад = &Склад) ЯК ВТ

Відсутність перевірки на NULL. Перевірка значення на NULL допоможе запобігти помилок як під час виконання, так і під час налагодження.

Неоптимальный запрос

Оптимальный запрос

ВИБРАТИ ТовариНаСкладахЗалишки.КількістьЗалишокЯК Залишок

РегістрНакопичення. ТовариНаСкладахЗалишки.КількістьЗалишок

ВИБРАТИ ЄNULL(ТовариНаСкладахЗалишки.КількістьЗалишок, 0) ЯК Залишок

РегістрНакопичення. ТовариНаСкладахЗалишки.КількістьЗалишок

Виняток отримання поля Посилання через точку.

Наприклад, потрібно відібрати певну номенклатуру з табличної частини документа Видаткова Накладна. Реквізит табличної частини Номенклатура має посилальний тип на довідник Номенклатура. Тому отримання поля Посилання через точку для поля посилального типу є зайвим. В цьому випадку відбудеться з'єднання з усією таблицею довідника Номенклатура.

Неоптимальний запит

Оптимальний запит

ВИБРАТИ ВидатковаНакладнаСписокНоменклатури.Номенклатура ЯК Номенклатура

Документ.ВидатковаНакладна.СписокНоменклатури ЯК ВидатковаНакладнаСписокНоменклатури

ДЕ

ВидатковаНакладнаСписокНоменклатури.Номенклатура.Посилання = &Номенклатура

ВИБРАТИ ВидатковаНакладнаСписокНоменклатури.Номенклатура ЯК Номенклатура

Документ.ВидатковаНакладна.СписокНоменклатури ЯК ВидатковаНакладнаСписокНоменклатури

ДЕ

ВидатковаНакладнаСписокНоменклатури.Номенклатура = &Номенклатура

За необхідності жертвуйте компактністю й універсальністю коду заради продуктивності. Як правило, для виконання конкретного запиту в даних умовах не потрібні всі можливі типи даного посилання. В цьому випадку, слід обмежити кількість можливих типів за допомогою функції ВИРАЗИТИ. Якщо даний запит є універсальним і використовується в декількох різних ситуаціях (де типи посилання можуть бути різними), то можна формувати запит динамічно, підставляючи в функцію ВИРАЗИТИ той тип, який необхідний за даних умов. Це збільшить обсяг вихідного коду і, можливо, зробить його менш універсальним, але може істотно підвищити продуктивність і стабільність роботи запиту.

Якщо Реєстратор - це поле складеного типу, яке може набувати значень посилання на 20 видів документів.

Неоптимальний запит

Оптимальний запит

ВИБРАТИ

Залишки.Реєстратор.Номер,

Залишки.Реєстратор.Дата,

Залишки.Контрагент

РегістрНакопичення.Залишки ЯК Залишки

ВИБРАТИ

ВИБІР

КОЛИ Залишки.Реєстратор ПОСИЛАННЯ Документ.ВидатковаНакладна

ТОДІ ВИРАЗИТИ (Залишки.Реєстратор ЯК Документ.ВидатковаНакладна).Номер

КОЛИЗалишки.Реєстратор ПОСИЛАННЯ Документ.ЗамовленняПокупця

ТОДІ ВИРАЗИТИ (Залишки.Реєстратор ЯК Документ.ЗамовленняПокупця).Номер

КІНЕЦЬ ЯК Номер,

ВИБІР

КОЛИ Залишки.Реєстратор ПОСИЛАННЯ Документ.ВидатковаНакладна

ТОДІ ВИРАЗИТИ (Залишки.Реєстратор ЯК Документ.ВидатковаНакладна).Дата

КОЛИ Залишки.Реєстратор ПОСИЛАННЯ Документ.ЗамовленняПокупця

ТОДІ ВИРАЗИТИ (Залишки.Реєстратор ЯК Документ.ЗамовленняПокупця).Дата

КІНЕЦЬ ЯК Дата,

Залишки.Контрагент

РегістрНакопичення.Залишки ЯК Залишки

ДЕ

Залишки.Реєстратор ПОСИЛАННЯ Документ.ВидатковаНакладна АБО

Залишки.Реєстратор ПОСИЛАННЯ Документ.ЗамовленняПокупця

Якщо запит відбирає тільки один вид контакту, то немає необхідності передавати вид контакту в якості параметра в запит. Як аргумент функції значення може виступати зумовлене значення, тобто те значення, до якого можна звернутися з коду безпосередньо (зумовлені значення, значення перерахувань).

При цьому скорочується обсяг коду і звернення відбувається швидше.

Неоптимальний запит

Оптимальний запит

ВИБРАТИ

КонтактЗКлієнтом.ВидКонтакту

Документ.КонтактЗКлієнтомЯККонтактЗКлієнтом

ДЕ

КонтактЗКлієнтом.ВидКонтакту=&ВидКонтакту

ВИБРАТИ

КонтактЗКлієнтом.ВидКонтакту

Документ.КонтактЗКлієнтомЯККонтактЗКлієнтом

ДЕ

КонтактЗКлиентом.ВидКонтакту=ЗНАЧЕННЯ(Перерахування.ВидиКонтактів.Дзвінок)

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

Неоптимальний запит

Оптимальний запит

ВИБРАТИ

ВидатковаНакладнаСписокНоменклатури.

Номенклатура ЯК Номенклатура,

ВидатковаНакладнаяСписокНоменклатури.Кількість ЯК Кількість,

ВидатковаНакладнаСписокНоменклатури.Сума ЯК СумаПродажіНоменклатури,

ЗалишкиНоменклатури Залишки.Партія ЯК Партія,

ЄNULL(ЗалишкиНоменклатуриЗалишки.КількістьЗалишок, 0) ЯК КількістьЗалишок,

ЄNULL(ЗалишкиНоменклатуриЗалишки.СумаЗалишок, 0) ЯК СумаЗалишок,

ЗалишкиНоменклатуриЗалишки.Партія.Дата ЯК ПартіяДата

Документ.ВидатковаНакладна.СписокНоменклатури ЯК ВидатковаНакладнаСписокНоменклатури

ЛІВЕ З’ЄДНАННЯ РегістрНакопичення.ЗалишкиНоменклатури.Залишки(

&Момент,

) ЯК ЗалишкиНоменклатуриЗалишки

З (ВидатковаНакладнаСписокНоменклатури.Номенклатура = ЗалишкиНоменклатуриЗалишки.Номенклатура)

ДЕ

ВидатковаНакладнаСписокНоменклатури.Посилання = &Посилання

І ВидатковаНакладнаСписокНоменклатури.Номенклатура.ВидНоменклатури <> ЗНАЧЕННЯ(Перерахування.ВидиНоменклатури.Послуга)

ВПОРЯДКУВАТИ ЗА

Номенклатура,

ПартіяДата

ПІДСУМКИ

МАКСИМУМ(Кількість),

МАКСИМУМ(СумаПродажіНоменклатури),

СУМА(КількістьЗалишок),

СУМА(СумаЗалишок)

Номенклатура

ВИБРАТИ

| ВидатковаНакладнаСписокНоменклатури.Номенклатура ЯК Номенклатура,

| СУМА(ВидатковаНакладнаСписокНоменклатури.Кількість) ЯК Кількість,

| СУМА(ВидатковаНакладнаСписокНоменклатури.Сума) ЯК Сума

|ПОМІСТИТИ ТабДок

|З

| Документ.ВидатковаНакладна.СписокНоменклатури ЯК ВидатковаНакладнаСписокНоменклатури

|ДЕ

| ВидатковаНакладнаСписокНоменклатури.Посилання = &Посилання

| І ВидатковаНакладнаСписокНоменклатури.Номенклатура.ВидНоменклатури <> ЗНАЧЕННЯ(Перерахування.ВидиНоменклатури.Послуга)

|ЗГРУПУВАТИ ЗА | ВидатковаНакладнаСписокНоменклатури.Номенклатура

|ІНДЕКСУВАТИ ЗА

| Номенклатура

|////////////////////////////////////////////////////////////////////////////////

|ВИБРАТИ

| ТабДок.Номенклатура ЯК Номенклатура,

| ТабДок.Кількість ЯК Колікість,

| ТабДок.Сума ЯК СумаПродажіНоменклатури,

| ЗалишкиНоменклатуриЗалишки.Партія ЯК Партія,

| ЄNULL(ЗалишкиНоменклатуриЗалишки.КількістьЗалишок, 0) ЯК КількістьЗалишок,

| ЄNULL(ЗалишкиНоменклатуриЗалишки.СумаЗалишок, 0) ЯК СумаЗалишок,

| ЗалишкиНоменклатуриЗалишки.Партія.Дата ЯК ПартіяДата

|З

| ТабДок ЯК ТабДок

| ЛІВЕ З’ЄДНАННЯ РегістрНакопичення.ЗалишкиНоменклатури.Залишки(

| &Момент,

| Номенклатура В

| (ВИБРАТИ

| ТабДок.Номенклатура

| З

| ТабДок ЯК ТабДок)) ЯК ЗалишиНоменклатуриЗалишки

| ЗА ТабДок.Номенклатура = ЗалишкиНоменклатуриЗалишки.Номенклатура

| ВПОРЯДКУВАТИ ЗА

| Номенклатура,

| ПартіяДата "+ ПорядокПартій+"

|ПІДСУМКИ

| МАКСИМУМ(Кількість),

| МАКСИМУМ(СумаПродажіНоменклатури),

| СУМА(КількістьЗалишок),

| СУМА(СумаЗалишок)

|З

| Номенклатура

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

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

У разі роботи в клієнт-серверній архітектурі, наприклад, з СУБД MSSQL можна налаштувати технологічний журнал з певними фільтрами, які виявляють найбільш тривалі запити.

Також рекомендується налаштувати очистку процедурного КЕШУ і процедуру дефрагментації індексу.

У процесі роботи з базою даних відбувається ефект фрагментації індексів.

Рекомендується виконувати дефрагментацію індексів не рідше одного разу на тиждень.

Фахівець компанії Ільдар Мінгалєєв.

Статті

Пошук

Теги

Оптимізація запитів в Підприємство 8

Контакти

Меню

ПРО НАС