АНАЛіЗ ТА ОЦіНКА СКРИПТіВ SQL Текст научной статьи по специальности «Математика»

Розглянута оцшка CKpunmie для бази даних. Описан основт помилки та неопти-мальностi скриптiв SQL та гх вага для оцт-ки i оптимiзацiг скриптiв

Ключовi слова: база даних, скрипт,

помилки, оптимiзацiя, ощнка, швидтсть □-□

Рассмотрена оценка скриптов для бази данных. Описаны основные ошибки и неоптимальности скриптов SQL и их веса для оценки и оптимизации скриптов

Ключевые слова: база данных, скрипт,

ошибки, оптимизация, оценка, скорость □-□

We consider the evaluation of scripts for data bases. The basic errors and non-optimality of SQL scripts and their weights for the evaluation and optimization script are described

Keywords: database, script, errors, optimization, estimation, speed

УДК 004.415.5

анал1з та оц1нка скрипт1в sql

Б.П. Громюк*

Контактний тел.: (0472) 72-62-14 E-mail: gromuk.bogdan@gmail.com

А. А. Р1дкокаша

Кандидат техычних наук, доцент* Контактний тел.: (0472) 73-02-68 E-mail: redan@list.ru *Кафедра програмного забезпечення автоматизованих

систем

Черкаський державний технолопчний уыверситет бул. Шевченко, 460, м. Черкаси, 18006

Вступ

Аналiз скриппв може бути виконаний тд час те-стування oci6, як навчаються або бажають працювати над програмними проектами, а також у проце« про-ектування програмних систем (ПС).

При прийомi на роботу, у навчанш застосовуеться вщкрите тестування, зокрема з знань SQL i баз даних (БД). Написаш скрипти перевiрюеться на наявнiсть помилок i оптимальнiсть, визначаеться ix оцiнка.

Шд час розробки ПС проводиться ii тестування i оптимiзацiя - процес змш скриптiв i/або структури БД для зменшення використання обчислювальних ресурав (збiльшення швидкодii ПС i зменшення за-дiяноi емностi запам'ятовуючих пристроiв) при вико-наннi запитiв.

На автоматизащю цих питань спрямовують значну увагу та зусилля, зокрема [1- 4]. Але питання автома-тизацп повшстю не вирiшено.

Усi ПС, що виконують оптимiзацiю та аналiз скриптiв (у тому числi i IBM DB2 Performance Expert for Multiplatforms [5]), можуть представити лише аль-тернативнi варiанти формування скрипту або групи скриппв i не можуть мати вплив на саму систему та структуру БД.

Постановка задачi

n0Tpi6H0 розробити тестову ПС (ТПС), що виконуе тестування та оптимiзацiю.

Кшцеву, чисельну та неупорядковану множину Bcix службових слiв (СС) мови SQL подамо як C C = {Cj: 1 < i < n} , де ci - СС, n - цiле число.

Подамо також сукупнiсть СС деяко1 наявно! або потрiбноi БД як множину Т, яка е частиною множини C i мае ва ii властивостi, тобто

T с C; T = {ti: 1 < i < m},

де m - щле та m<n.

Особа, яку тестують, повинна з використанням СС множини Т написати задаш скрипти, тобто у вщповвд сформувати пiдмножини скриппв БД р(Т) i запитiв до БД р^), що потiм перевiряються на наявнiсть помилок i визначаеться близость скриптiв. Наприклад, для скрипта-еталона е та скрипта-вщповвд b можна знай-ти кiлькiсний показник !хньо! близькостi k = y(e,b), значення якого знаходяться в iнтервалi [0, 1]. Власти-востi та обмеження функцп y при використаннi:

- якщо k = y(e,b) = 1, то маемо повну вщповщшсть e та b;

- якщо k = Y(e,b)>0,5 , то маемо неповну ввдповщ-шсть e та b;

- якщо k = Y(e,b)<0,5, то маемо деяку вщповщ-нiсть e та b;

- якщо k = Y(e,b) = 0, то маемо повну невщповщ-шсть e та b.

При прийомi на роботу беруться до уваги верхш двi оцiнки, при навчанш - всь

При оптимiзацii з множини Т розробником сфор-мованi пiдмножини скриптiв БД р(Т) i запитiв до БД р^). Пiдмножини ix оптимiзованих версiй позначимо ввдповвдно ор(Т) та ор(S). При оптимiзацii можливе використання СС множини С, тому потужшсть множини може бути зб^ьшеною i |op(T)|>|p(T)|,|op(S) >|p(S)|.

Вiдношення а мiж р(Т) та ор(Т), яке описуе змши потрiбного апаратного ресурсу, маскуеться рiзним рiв-нем наповнення БД, тому його розглядати не будемо.

Зменшення часу на виконання запиив ор(S) вра-хуемо за допомогою функцп ß , що для двох одно-спрямованих скриппв запиту надае тренд швидкодп ß = p(S) ^ op(S).

Основний 3MiCT

Основними етапами обробки тестового скрипту-вiдповiдi е перевiрка та оптимiзацiя. Цi дii виконуе

розроблена ТПС (рис. 1), результати дп в -версп яко1 наводяться даль

Рис. 1. Блок схема ТПС

При nepeBip^ проводиться лексичний та синтак-сичний аналiз - скрипт розбиваеься на його синтак-сичш компоненти, перевiряються помилки у напи-саннi. Якщо у скриптi запит, то ТПС вибирае об'екти з каталогу БД та формуе внутршне представлення запиту, ця форма внутршнього представлення буде використана на наступних етапах.

Одними з основних помилок е лексичш i синтак-сичш. Крiм помилок невiрного написання операторiв SQL, невiрного порядку команд i так далi, е й таю, що не е фатальними, але при певних умовах такий скрипт може крiм зменшення швидкоси обробки ви-кликати помилку при виконанш.

Найпростiшi помилки

Щоб даш вiдрiзнити вiд СС SQL, iмена полiв i таблиць слiд укладати в зворотш одинарнi лапки - "ч ". Адже iм'я поля може сшвпадати з СС, але якщо ми використаемо зворотш лапки, то ТПС зро-зумiе все правильно:

SELECT * FROM 4table WHERE Mate = '2006-04-04 ';

Наприклад, у скриптi SELECT * FROM 4table4 WHERE name = Bill;

ТПС сприйме Bill як iм'я iншого поля, не знайде його, i видасть помилку. Тому даш, що пiдставляються (Bill), треба брати в лапки.

Однак, i в самих даних теж зустрiчаються лапки. Примiром,

SELECT * FROM 4table4 WHERE name = 'комп'ютер';

Тут ТПС вирiшить, що 'комп' - це данi, а ютер - СС, якого нема в множиш Т, i теж видасть помилку. Щоб пояснити, що лапки (i деяк iншi спецсимволи) вщно-сяться до даних, треба поставити слеш,. В результат отримаемо вiрний запит, який помилок не викличе:

SELECT * FROM 4able4 WHERE name = 'комп \ ютер';

При on™Mi3a^'i знаходяться помилки у струк-турi БД, запитiв, тобто скрипт написаний вiрно, але результат запиту формуеться пов^ьно, через неопти-мальнiсть по кiлькостi операцiй запису та зчитування тд час його виконання. Основна вдея синтаксично! оптимiзацii (syntax optimization) - використання еквь валентних алгебра!чних перетворень.

При оптимiзацii виконуються три етапи - лопчна оптимiзацiя, вибiр оптимального плану, формування машинних кодiв.

На етапi лопчно! оптимiзацii виконуеться перетво-рення скрипта запиту в оптимальний формат з точки

зору синтаксису.

При виборi оптимального плану формуеться д е к i л ь к а альтернатив для ix виконання. Пот1м ТПС обирае кращий план запиту зНдно статистики або евристичних правил. HafinacTinie обираеться "найдешев-ша" альтернатива (мен-ше pecypciB та часу ви-конання).

На етапi формування машинних кодiв вибраний план переформовуеться у машинш коди для подальшого виконання. Далi формуеться вщповщь i пересилаеться користувачу.

Приклад

Розглянемо запит, який робить вибiрку даних з таблиць Product та Vendor:

SELECT Vendor_Code, Product_Code, Product_Des-c FROM Vendor, Product WHERE Vendor.Vendor_Code = Product.Vendor_Code AND Vendor.Vendor_Code = "100";

Найб^ьш очевидний шлях обробки цього запиту полягае в наступному:

- формуемо декартовий добуток таблиць Product i Vendor;

- обмежуемся в результуючш таблищ рядками, якi задовольняють умовi пошуку в реченнi WHERE;

- виконуемо проекщю результуючо! таблищ на список полiв, вказаний у реченш SELECT.

Оцiнимо вартiсть процесу обробки цього запиту в операщях введення / виведення. Нехай таблиця Ven-dor мштить 50 рядкiв, а таблиця Product - 1000 рядюв. Тодi для формування декартового добутку потрiбно 50000 операцiй читання i операцiй запису (в результуючу таблицю). Для обмеження резуль-туючо! таблицi потрiбно бiльше 50000 операцiй читання i, якщо 20 рядкiв задовольняють умовам пошуку, то потрiбно 20 операцш запису. Виконання операцii проекцii викличе ще 20 операцiй читання i 20 операцiй запису. Таким чином, для обробки цього запиту системi потрiбно 100080 операцiй чи-тання i запису.

Для даного прикладу запиту можна використо-вувати таку еквiвалентнiсть: (A JOIN B) WHERE restriction ON A OR (A WHERE restriction ON A) JOIN B. Це означае, що обмеження за умовою пошуку може бути виконано якомога рашше для того, щоб обмежити число рядюв, яю можуть бути оброблеш тзшше. Застосовуючи це правило до запиту, наве-деного вище, отримуемо наступний процес обробки запиту:

1. Обмеження за умовою пошуку в другш таблищ (Vendor_Code = "100") призведе до 50 операцш читання i 20 операцш запису.

2. Для виконання з'еднання отриманоi на 1-му крощ результуючоi таблищ з таблицею Product по-трiбно 20 операцш читання результуючоi таблицi, 100 операцш читання з таблищ Product i 20 операцш запису в нову результуючу таблицю.

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

Щоб уникнути перевантаження серверу БД великою юлькостю операцш зчитування та запису необхщно використовувати спещальш реляцшш операцп. Будемо виконувати Проекщю, Вибiр, З'ед-нання.

Операщя проекцп (Projection) обмежуе число по-лiв таблицi, на якi е посилання в командi SELECT name, phone FROM customer.

Операщя вибору або обмеження (Selection or restriction) обмежуе результуючу множину ильки тими рядками, яю задовольняють умовi пошуку (SELECT * FROM Customer WHERE Name = 'Jones';)

Операщя з'еднання (Join) створюе результуючу множину за допомогою конкатенацп рядкiв декiлькох таблиць. Щ зчепленi рядки повиннi задовольняти де-якiй умовi з'еднання.

З'еднання створюе результуючу множину з двох або б^ьше таблиць таким же чином, що i декар-товий добуток. Воно здшснюе конкатенацiю рядкiв для кожного рядка однiеï таблицi з кожним рядком iншоï таблицi. Вщмшшсть мiж декартовим добутком i з'еднанням полягае в тому, що в цю операщю за-лучаються ильки тi рядки, якi задовольняють умовi з'еднання.

Це е логiчним еквiвалентом декартового добутку, для якого була виконана операщя селекцп за умо-вою.

Проте операщя з'еднання реалiзуеться б^ьш ефективно бшьшштю СУБД, так як ощнювання ряд-кiв виконуеться до первинного формування декартового добутку як промiжного результату.

SELECT P.Name, O.Order_num, C.NameFROM Customer C, Order O, Product PWHERE (C. Cust_no = O. Cust_no) AND (P. Cust_no = O. Cust_no);

Двi найважливiших характеристики операцп з'еднання - то, що вона е комутативною i асощативною:

A JOIN B = B JOIN A (комутатившсть); A JOIN (B JOIN C) = (A JOIN B) JOIN C (асощатившсть).

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

Це дае можлившть виконувати з'еднання будь-якого числа таблиць як серж з'еднань двох таблиць, що дозволяе уникати виконання специфiчних фп зичних операцш при з'еднанш дов^ьного числа та-блиць.

Два найб^ьш вiдомi алгоритми виконання з'еднання наведеш нижче.

З'еднання за допомогою вкладеного циклу (Nested Loop Join). У цьому алгоритмi рядок читаеться з першоï таблищ - зовнiшньоï (outer) таблищ, i поим читаеться кожен рядок другоï таблищ - внутрiшньоï (inner), як кандидата для з'еднання. Поим читаеться другий рядок першоï таблищ i знову кожен рядок з другой i так до тих шр, поки в« рядки першоï таблищ не будуть прочиташ. Якщо у першш таблищ знахо-диться M рядюв, у другiй - N, то читаеться M x N рядюв;

З'еднання за допомогою об'еднання (Merge Join). Цей метод виконання з'еднання передбачае, що обид-

вi таблицi розсортованi таким чином, що рядки чита-ються в порядку значень поля (полiв), за якими вони з'еднуються. Це дозволяе виконувати з'еднання за допомогою читання рядюв з кожноï таблицi та по-рiвнювання значень полiв з'еднання до тих шр, поки вщповщшсть цих значень мае мiсце. У цьому способi з'еднання завершуеться за один прохщ по кожнiй таблищ.

Також важливим моментом е використання в скриптах шдексованих полiв. Використання техшки iндексування збiльшуе складнiсть обробки запиту. Наприклад, якщо таблиця мае шдекси за трьома рiзними полями, то кожне з них може бути викори-станим для доступу до таблищ (крiм послщовного доступу до таблищ у фiзичному порядку розташу-вання рядкiв).

Наведений нижче опис показуе, що створювана ТПС суттево покрие в« оцiночнi та оптимiзацiйнi функцiï.

Структура аналiзатора ТПС (табл. 1) складаеться з трьох обробниюв:

- лексичний аналiзатор - перевiряе написання операторiв а також послщовшсть '¿х написання;

- синтаксичний аналiзатор - перевiрка позицiï даних та назв об'екпв бази даних, а також синтаксису самих даних;

- оптимiзацiйний аналiзатор - аналiзуе скрипт та формуе рекомендацп щодо його покращення.

Таблиця 1

Структура аналiзатора ТПС

Лексичнi Синтаксичнi Оптимiзацiя

Помилки в написанш операторiв Вiдсутнiсть лапок " ' " у даних Використання * в операторi SELECT

Невiрний порядок операторiв та даних Вщсутшсть лапок " v "у об'екйв Використання складно'1 умови замiсть з'еднання

Перевiрка репстру скрипта Вiдсутнiсть екранування спещальних символiв Постшне звернення до об'екта замють складно'1 передачi

Помилки приведення до типу Використання математичних операцiй в скрипт

Використання оператора OUTER JOIN

Пошук по не ¡ндексованому полю

Найскладшша (та основна) частина ТПС - це перевiрка ланцюга операторiв, вш декларуе поло-ження того чи шшого оператора i перевiряе скрипт вщповщно до набору ланцюгiв, якщо ланцюг не виявлений.

Приклад ланцюга: SELECT ^ FROM [^ WHERE] Н ORDER BY].

На мiсцi стр^ок в ланцюгу стоять вiдповiдно даш або назви об'екив. У ТПС це запис, що посила-еться на певний оператор зi списку констант та на тип даних (на мшщ стр^ок).

yci iншi перевiрки - це npocTi умови на наявнiсть певного символу.

nepeBipKa на оптимальнiсть запиту з умовою е найскладнiшою задачею для ТПС, але у цьому ви-падку можна вивести користувачу час виконання запиту та рекомендащю щодо використання з'ед-нань, що допоможуть зменшити кiлькiсть операцiй запису/зчитування.

Крiм ланцюгiв, необхiдно також описати струк-тури, що допоможуть визначити умови та арифме-тичнi операцп. У випадку з умовами в скриптах опис е доволi складною задачею. Але найпродук-тивнiшим ршенням е пошук умовних операторiв та покрокове ïx видалення з кеш-скрипту при правильному значеннi комбшацп i так до повного видалення умов або виявлення помилки. Арифметичш операцп знаходяться за допомогою пошуку у скриптi вщ-повщних операторiв (i з подальшим зауваженням розробнику).

Коли не знайдено операторiв та е неправильна послщовшсть символiв, подальший аналiз немож-ливий i мае сенс зниження ощнки k.

Наведемо приклади, що показують швидкодж описаних вище методiв та шформащю у виглядi ло-гiв, що формуе ТПС.

У вих прикладах виконуеться оператор COU-NT(*), що допомагае показати "чшту" швидкiсть без затрат на формування результуючоï таблищ. ТПС працювала на комп'ютерi конф^урацп: про-цесор - Intel Dual-Core E5400 4 GHz, оперативна пам'ять - DDR2 1300 MHz, сервер БД - mysql-5.5.17-win32.

1. Швидюсть вибiрки 10 та 200 елеменив майже не вiдрiзняеться, це шдтверджуе, що запит здiйснюе перебiр усix значень таблицi table_big.

SQL>SQL> SELECT COUNT(*) SQL>

FROM table_big WHERE Rownum < SQL> SELECT COUNT(*) FROM 10; table_big WHERE Rownum < 200;

COUNT(*)

COUNT(*)

9

Executed in 0,015 seconds

199

Executed in 0,016 seconds

SQL>

SQL> SELECT COUNT(») FROM Table(expand_big) t

WHERE t.id NOT IN (

SELECT tl.id FROM

Table(expand_small) t1 )

AND Rownum < 10; COUNT(*)

SQL>

SQL> SELECT COUNT(») FROM TABLE(expand_big) t

WHERE t.id NOT IN (

SELECT tl.id FROM

TABLE(expand_small) tl )

AND Rownum < 200; COUNT(*)

9

Executed in 0,578 seconds

199

Executed in 12,063 seconds

SQL> SQL>

SQL> SELECT /*+ INDEX(table1 SQL> SELECT /*+ NO_INDEX(table1

ix_table1)* ix_table1) */

COUNT(*) FROM table1; COUNT(*) FROM table1;

COUNT(*)

COUNT(*)

1099998

Executed in 0,172 seconds

1 099998

Executed in 0,063 seconds

Використання складних iндексiв по двом i бiльше полям з малою ушкальшстю поля робить запит по-вiльнiшим нiж запит з умовами.

4. Наступш два логи показують, що для з'еднання важлива не шдексовашсть поля з'еднання, а юльюсть з'еднуваних полiв.

SQL>

SQL> SELECT COUNT(*) FROM table1 t1 INNER JOIN table2 t2 ON t2.codea = t1.codea AND t2.codeb = t1.codeb; COUNT(*)

SQL>

SQL> SELECT COUNT(*) FROM table1 t1 INNER JOIN table2 t2 ON t2.id = t1.id;

COUNT(*)

99999

Executed in 0,078 seconds

99999

Executed in 0,079 seconds

2. Два логи як приклад того, що умови повинш бути правильно роставлеш вiдповiдно до вiрогiдностi роботи умов.

Рiзниця в чаи виконання цих двох запипв зумов-лена тим, що складна умова виконуеться при кожному входжеш в елемент при перебiрцi.

3. Приклад використання шдексованих полiв та полiв без шдексу

Невелика рiзниця в швидкостi зумовлена малою юльюстю даних (100000 запиив) i буде зб^ьшувати-ся при зб^ьшеш кiлькостi записiв. Також з'еднання сильно залежить вщ типу полiв, по яким проводиться з'еднання. З'еднання також виконуе функцп формування результуючоï таблищ, тому, якщо запит не буде з оператором COUNT(*), а з певними полями, то з'еднання покаже свш результат ще б^ьш явно.

ТПС надае можливосп виконання запипв з рiз-ними планами для набору статистики, наприклад, за статистикою у веб-проектах для покращення швид-кодiï всiеï ПС потрiбно виключити автоматичнi фор-мувачi скриптiв.

Щ формувачi збiльшують кiлькiсть малих за-питiв до БД, це виникае тому, що бiблiотеки запитiв робляться ушверсальними, а отже е досить пов^ь-ними. Виключення формувачiв запиив допомагае сконструювати оптимальний запит, враховуючи осо-бливостi предметноï областi, а також налаштування ПС.

Також за статистикою в« СС повиннi бути в верх-ньому регiстрi, невиконання цiеï умови не призведе до виникнення критичноï помилки, але зменшить швидюсть за рахунок додатковоï обробки внутрш-нiм оптимiзатором СУБД.

Висновки

Максимальний ефект у формуванш правильних запиив буде за наявностi системного шдходу до ПС.

Виходячи з цього, потрiбно загалом писати скрипти i ощнювати швидкодiю системи. Описанi в стат-тi помилки та варiанти '¿х вирiшення допоможуть здiйснювати аналiз та оцiнку скриптiв. Постiйний контроль за скриптами, що джть в проект^ допо-може майже повшстю виршити проблеми швидкостi та системних помилок шд час виконання скрипта на великих проектах.

Лиература

1. Jarke, M. Query Optimization in Database Systems [Текст] / М. Jarke, J. Koch // Computing Surveys. - 1984. - Vol. 16, № 2 - Р. 111-152.

2. Бшоусова, Л. I. Потенщал комп'ютерного тестування [Текст] / Л. I. Бшоусова // Вюник Т1МО. - 2008. - №10. - С. 40-44.

3. Фюун, М. Т. Розробка синтаксичного анал1затора мови програмування PL/I для решженфшгу блок-схем алгоритмiв [Текст] / М.Т. Фюун, О.В. Гнездьонова // Проблеми програмування. - 2006. - №2-3. - С. 617-625.

4. Моисеенко, С. SQL. Задачи и решения. Интерактивный учебник [Електроннш ресурс] - Режим доступу: http://www.sql-tuto-

rial.ru/ru/content.html.

5. Интерфейс Ltd. Internet&Software Company - Режим доступу: http://www.interface.ru/home.asp.

Застосування принцитв локально-пара-лельног обробки до клтинних uemoMumie дозволяв суттево тдвищити розмiри моделей. Запропоновано i продемонстровано на тестових прикладах локально-паралельний стековий алгоритм бтарного клтинного автомату

Ключовi слова: клтинт автомати,

локальна паралельтсть, стек

□-□

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

Ключевые слова: клеточные автоматы,

локальная параллельность, стек

□-□

Application of principle of local-parallel processing to cellular automaton allows greatly enlarge the sizes of models. Local-parallel stacking algorithm of the binary cellular automaton is offered and demonstrated on test examples

Keywords: cellular automatons, local parallelism, stack

УДК 519.87

стековый алгоритм

для локально-параллельного бинарного клеточного автомата

Бенаддия Абдельлатиф

Аспирант*

О.Ф.М ихал ь

Доктор технических наук, доцент, профессор* *Кафедра Электронно-вычислительных машин Харьковский национальный университет радиоэлектроники пр. Ленина, 14, г. Харьков, 61166 Контактный тел. (057) 40-93-54 Е-mail: fuzzy16@pisem.net

1. Введение

Моделирование крупномасштабных систем с распределённой обработкой информации актуально в связи с изучением динамики процессов, происходящих в человеческом окружении, на различных уровнях от глобальных климатических явлений до живой природы и социальных структур (экология, климатология, демография, социология). Общность подхода состоит в том, что система рассматривается как состоящая из большого числа дискретных элементов (ячеек), взаимодействующих друг с другом в соответствии с фиксированным наборами правил. Перспективным аппаратом компьютерного модели-

рования подобных систем являются клеточные автоматы (КА). Распространённость моделирования поведения систем на КА обусловлена простотой описания конфигурации и набора рабочих правил. Дополнительный выигрыш по эффективности работы КА может быть обеспечен локально-параллельными (ЛП) алгоритмами [1], которые разработаны [2, 3] для целочисленных неотрицательных значений операндов, что применимо к описанию ячеек КА. В частности, если ячейка принимает одно из значений (0, 1), это соответствует подвиду КА - бинарным клеточным автоматам (БКА) [4]. Для полноты оперирования данными, необходимо дополнить аппарат описания [2, 3] ЛП алгоритмами, реализующими