Sql server: пошук або сканування. N верхніх позицій для групи. Продовження

Модель оптимізації використовує вихідне припущення, відоме як включення (containment) і включення (inclusion). Включення (containment) передбачає, що якщо ви щось шукаєте, то шукане існує. У разі еквівалентного з`єднання (equijoin) вихідне положення таке, що різні значення в стовпцях з`єднання, існуючі на одній стороні, існують і на інший. Початкове положення про включення (inclusion) аналогічно, але для предиката фільтра рівності з константою, тобто допущення таке, що фильтруемое значення дійсно існує. Подивимося на залишковий предикат оператора Clustered Index Scan: O.empid = E.empid. З огляду на вихідні допущення моделі, якщо ID кожного співробітника компанії, що є об`єктом сканування, дійсно існує, то за законами статистики з кожної сотні рядків один рядок буде шуканим збігом (щільність Orders.empid - 0.01). Таким чином, знову ж за законами статистики, для виходу на шуканий рядок знадобиться читання всього пари сторінок. Якщо вихідні допущення моделі коректні, то ця стратегія краще, ніж подвійний пошук. Однак в нашому випадку вихідні допущення моделі не виконуються, оскільки у нас в таблиці Employees 200 співробітників компанії, з яких 100 не мають шуканих замовлень. Тому в 100 випадках цикл сканування буде виконуватися від початку до кінця. Отже, продуктивність такого рішення дуже низька.



Виправити ситуацію можна кількома способами. Якщо є можливість розширити індекс включенням відсутніх стовпців (в нашому випадку filler), то це найкраще. В результаті отримуємо план, аналогічний другому варіанту на скріншоті вище. Якщо такої можливості немає, то можна вказати підказку індексу до таблиці Orders: WITH (INDEX (idx_eid_oid_i_od_cid)). В результаті отримуємо стратегію подвійного пошуку. Якщо ви вважаєте за краще не користуватися підказками, то можна застосувати метод, який я називаю проміжним CROSS APPLY. Цей метод передбачає використання двох операторів CROSS APPLY. Проміжний оператор обчислює відповідний ID замовлення для даного співробітника компанії за допомогою запиту ТОР (1), але не повертає ніяких елементів зовнішнім запитом. Оператор активує внутрішній оператор CROSS APPLY і передає внутрішньому запитом відповідний ID замовлення, після чого внутрішній запит повертає відповідний рядок з таблиці Orders. Проміжний запит CROSS APPLY повертає рядок, що повертається внутрішнім запитом CROSS APPLY, зовнішнім запитом, який, в свою чергу, повертає шукані елементи замовлення. Якщо сенс не цілком ясний, погляньте на запит, пропонований в якості рішення, і знову прочитайте пояснення (див. Код вище). План запиту показаний на скріншоті нижче.








Як ми бачимо, на цей раз план передбачає застосування стратегії подвійного пошуку замість сканування. Запит задіє всього кілька сотень операцій читання і виконується за 90 мілісекунд.


На закінчення для очищення виконайте код, представлений вище.

Пошук або сканування - питання непросте. У цій статті показано, що в деяких випадках оптимізатор самостійно робить вибір, наприклад при використанні предиката EXISTS для обробки напівз`єднання або антіполусоедіненій. Однак в деяких випадках, наприклад якщо потрібно вивести значення min або шах або п верхніх позицій для групи, доводиться вручну розробляти рішення, яке дає оптимальний план запиту. У наступній статті на прикладі випадку з зростаючим ключем буде показано, як через неточну оцінки кількості елементів оптимізатор може вибрати невдалу стратегію і що можна в цьому випадку зробити, щоб виправити становище.

---

Давно мрієте придбати ліцензію SQL Server, але обмежені в матеріальних засобах? Тоді вам дійсно точно слід заглянути на 1хбет сайт (https://1xbet.site/). Це топовий букмекер, який дозволить вам зробити цілий статок на простих і вигідних ставках. І завдяки йому ви напевне точно зможете стати щасливим володарем SQL Server!

---