Швидкість обробки запитів на SQL-серверах (укр)

МНСТЕРСТВО ОСВТИ УКРАНИ КИВСЬКИЙ УНВЕРСТЕТ МЕН ТАРАСА ШЕВЧЕНКА Факультет кбернетики Кафедра теоретично кбернетики ВИПУСКНА КВАЛФКАЦЙНА РОБОТА БАКЛАВРА На тему Швидксть обробки запитв на SQL-серверах Виконавець студент 4 курсу Дима Павло Костянтинович Науковий кервник ассистент Коваль Ю.В.

Випускна квалфкацйна робота бакалавра Рекомендована до захисту в ДЕК Протокол 10, вд 25 травня 2000 р. Зав.кафедри проф. Белов Ю.А м Кив - 2000 ЗМСТ 1.Вступ 2.Швидксть роботи SQL сервера.2.1 Технологя DAO.2.2 Технологя ODBC 2.3 JSCRIPT 3. Результати тестування 17 Висновок 21 Список використано лтератури.22

Частина 1 Internet. Опорним елементом в цй мереж сервера баз данних. Саме на сервер збергаться нформаця до яко можуть отримати доступ користувач. При робот користувача з базою данних в Internet, вн посила запит до бази даннихю цей запит сформовано за правилами мови SQL Structured Query Language мова структурованих запитв.

В мережах використовуються так SQL сервера як Oracle, Microsoft SQL Server, Sybase, dbase, Informix, Progress та нш. Як приклад SQL сервера взьмемо Microsoft SQL Server 7.0 . Система MS SQL Server - це багаторвнева система обмну данними вд клнта до сервера, дозволя створювати пдтримувати бази данних. На вдну вд бази данних орнтованих на велик компютери та мн-компютери, до серверно

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

MS SQL Server 7.0 використову вбудован компоненти ОС Windows NT. Серверна СУБД MS SQL Server 7.0 добавля до мережевих компонент окрем елементи сервса, так як OLE-DB вдповда за звязок та упровадження обктв баз даних ODBC - вдкритий нтерфейс баз даних. Ц компоненти забезпечують тсний звязок необхдного програмного забезпечення. SQL Server 7.0 ма багато нструментв, призначених для розробки зовншнього нтерфейсу бази даних.

Окрм мови SQL для збергання, вибрки данних можна викоритовувати додатки приложения з графчним нтерфейсом користувача GUI. Це дуже спрощу розробку баз даних, таблиць звязкв мж ними. Зручно використовувати SQL Server разом з програмами для роботи в Internet. Центральним компонентом системи SQL Server реляцйна база даних та структура. В реляцйнй баз даних нформаця представляться в вигляд рядкв та стовбцв.

Рядки не впорядкован, крм випадку, коли для таблиц створються кластерний ндекс. Сама структура операторв запитв, з допомогою яких проходять виборка даних з рядкв таблиц, не залежить вд того впорядкован рядки чи н. Частина 2 ШВИДКСТЬ РОБОТИ SQL SERVER 7.0 В процес роззвитку SQL Server при розробц клнтських додаткв використовувалось деклька пдходв. Ядром системи завжди був процессор обробки запитв, який працював на сервер.

Запити писались на мов SQL вводились опрацьовувались через утлти типу Query Analyzer. При написанн програмних додаткв для взамод з SQL Server частше за усе використовують 1 DB-Library 2 ODBC DAO DB-Library явля собою специфчний для SQL Server нтерфейс прикладних програм . Вн менш зручний нж ODBC, для переносу програмного додатку в ншу серверну систему але з точки зору працездатност

вн реалзову самий швидкий доступ до нформац. Це забезпечуться не тльки тим, що вн нада оптимзований нтерфейс API, але використову в програмах особисту мову системи SQL Server. ODBC як SQL-OLE надають такий же рвень сервсу але створюють додатковий рвень абстракц мж додатком та процессором запитв SQL Server . В DB-Lib не пдтримуться звязок таблиць або автоматичного оновлення значень. Це можна розглядати як позитив оскльки гарантуться повний контроль над взамодю програмного

додатку з сервером оптимзацю звязкв. Додатково програмсту додаться повний контроль над виявденням обробкою помилок, повязаних з транзакцями. 2.1 ТЕХНОЛОГЯ DAO Data Access Object обкти доступу до данних приззначена для викоритання обктв, методв, властивотей, значно полегшшують роботу програмного додатку з базою даних. Для обмну нформацю з SQL сервером в обктах DAO викоритову-ються рвн дотупу

Jet ODBC, одна вони утворюють ще один рвень абстракц мж додатками викликами функцй ODBC, як використовуються при обробц запитв. Технологя DAO для роботи з базами даних, таблицями, видами передбача викоритання коллекцй обктв. Наприклад для створення ново таблиц легше викликати метод Add вдповдного обкта, мж використовувати стандартний пдхд технолог

ODBC. При робот з базою даних можна використати стандартний пдхд для доступу до майже будь-якого сховища даних, що пдтриимуться засобоми ODBC. Але треба вдмтити, що технологя DAO поступово витсняться бльш прогресивною технологю ADOActiveX Data Object обкти данх ActiveX . 2.2 ТЕХНОЛОГЯ ODBC З метою можливого встановлення звязку з рзними

СУБД фрмою Microsoft була розроблена технологя ODBC . Вона забезпечу створення додаткового рвня абстракц мж програмним додатком СУБД. Даний рвень дозволя створити одну команду Select використовувати для виборки даних з будь-яких пдтримумих типв баз даних, враховуючи т випадки, коли СУБД взагал не пдтриму мову SQL. Служби ODBC являють собою окремий рвень доступу до файлв баз даних.

ODBC бере на себе вдповдальнстьза отримання вд до-даткв запитв на вибрку нформац переведення х на мову, що використову ядро база даних, для обробки запиту. Гарним прикладом використання ODBC технолог СУБД Access фрми Microsoft. Головна рзниця мж ODBC BD-Lib поляга у тому, що ODBC вимага побудови передач йому лише стандартних

SQL команд, а BD-Lib вимага особливого синтаксису при побудов у буфер команд, що напрравляються безпосередньо у серверне середовище. Головним недолком ODBC необхднсть в трансляц запитв. Проведення ц додатково роботи вдповдно вдображаться на швидкост доступу до даних. При робот з ODBC можна отримати суттвий виграш у швидкост обробки даних, якщо в систем кл-нтсервер саме на сервер системи SQL Server буде оброблятись запит, а не на клнтському компютер.

Також прирст у швидкост роботи ODBC да використання встронних процедур. 2.3 JSCRIPT Тепер перейдемо до процесв, що повязан з вимрюванням швидкост системи SQL Server 7.0 . У даннй робот прпонуться написання програмного додатку на встронному shell OS Windows NT - Java Script . Сам програмний додаток це програма написана для нтерпритуюча мова. Файл з розширенням . js буде виконавчим для OS Windows

NT. Спочатку напишемо Script для створення бази даних ADO Flags var adLockOptimistic 3 var adOpenStatic 3 var SQLDataBase DSNOTStUIDsaPWDDATABASEOTSt var SQLDataBase DSNOTSttUIDPWD var oConn var oRs function dbConnect Create ADO Connection Object. Use IISSDK OBDC Souce with default sa account and no password oConn new

ActiveXObjectADODB.Connection oConn.OpenSQLDataBase oConn.CommandTimeout600 function dbRecordSQL var oRs Create ADO Recordset Component, and associate it with ADO connection oRs new ActiveXObjectADODB.Recordset oRs.ActiveConnection oConn Get empty recordset oRs.Source SQL oRs.CursorType adOpenStatic use a cursor other than

Forward Only oRs.LockType adLockOptimistic use a locktype permitting insertions oRs.Open return oRs function dbClose oConn.Close function dbRequeryRec,SQL Rec.Close Rec.Source SQL Rec.Open dbConnect try oRsdbRecordCREATE TABLE dbo.DBTEST String char 30 NULL, Number int NOT NULL DEFAULT 0, Random int NOT NULL DEFAULT 0 catch e oRsdbRecordSELECT

FROM DBTEST WHERE 01 fori0i 10i oRs.Addnew oRsString.valueUseri oRsNumber.valuei oRsRandom.valueMath.round100Math.random oRs.Update ifi10 WScript.echoNow in base present i records WScript.echoCompleted База даних буде мати вид StringNumberRandomUser1134User2212User33 75 У наведеному Script функця Connect вдповда за регстрацю в SQL Server утворення бази даних DBTest . DSN мя звязку, визначаться при утворенн джерела даних

ODBC. UID дентифкатор користувача, використовуться при регстрац на сервер, у SQL Server дентифкатор користувача завжди ма починатись з sа . PWD або password кодове слово доступу , необхдний для регстрац на сервер. В даному випадку не використовумо. Функця Record проводить запис у вс рядки бази даних DBTest створен значення , у стовбчик Random створен генератором випадкових чисел значення вд 0 до 99

. Кльксть рядкв у таблиц буде 10 000, створення займа 3,5 хвилин. Стовбчик Number буде ключем. Напишемо ще один Script , для тестування швидкост роботи SQL Server . Щоб визначети час вдповд на запит як умога точнше будемо посилати на сервер не один запит , а деклька десяткв однакових запитв, обрахувавши середнй час вдповд отримамо точний час обробки запиту знаходження сервером результуючо множини елементв.

Але треба враховувати такий випадок - якщо для пдвищення точност послати деклька сотень однакових запитв, то SQL Server почне хешування таблиц для оптимзац сво роботи результати роботи будуть не точн, оскльки при звичайному запит хешування таблиц не робиться. ADO Flags var adLockOptimistic 3 var adOpenStatic 3 var SQLDataBase DSNOTStUIDsaPWDDATABASEOTSt var SQLDataBase

DSNOTSttUIDPWD var oConn var oRs function dbConnect Create ADO Connection Object. Use IISSDK OBDC Souce with default sa account and no password oConn new ActiveXObjectADODB.Connection oConn.OpenSQLDataBase oConn.CommandTimeout600 function dbRecordSQL var oRs Create ADO Recordset Component, and associate it with ADO connection oRs new

ActiveXObjectADODB.Recordset oRs.ActiveConnection oConn Get empty recordset oRs.Source SQL oRs.CursorType adOpenStatic use a cursor other than Forward Only oRs.LockType adLockOptimistic use a locktype permitting insertions oRs.Open return oRs function dbClose oConn.Close function dbRequeryRec,SQL Rec.Close Rec.Source SQL Rec.Open function TestTestNumber,

Query, CountTests Max0 Min10 Delta0 Ave0 WScript.echoQuery TestNumber fori0i CountTestsi StartTimenew Date dbRequeryoRs,Query EndTimenew Date DeltaEndTime-StartTime WScript.echo Probe i ResultDelta ms AveAveiDeltai1 WScript.echo ResultAve ms dbConnect oRsdbRecordSELECT FROM DBTEST

WHERE 10 Test1, SELECT FROM DBTEST , 50 Test2 , SELECT FROM DBTEST ORDER BY String,50 Test 3 , SELECT FROM DBTEST ORDER BY Number ,50 Test 4,SELECT SumNumber FROM DBTEST GROUP BY String,50 Test5 , SELECT SumNumber FROM DBTEST GROUP BY Random,50 Test 6 , SELECT FROM

DBTEST WHERE Number 9 , 50 Test7 , SELECT FROM DBTEST WHERE String User9,50 Test8,SELECT FROM DBTEST WHERE Number in SELECT Number FROM DBTEST , 50 Test9 ,SELECT FROM DBTEST WHERE Number in SELECT Number FROM DBTEST WHERE String User50000 ,50 Test10 , SELECT FROM DBTEST

WHERE Number in SELECT Number FROM DBTEST WHERE StringUser50000, 50 Test11, SELECT FROM DBTEST WHERE Number in SELECT Number FROM DBTEST WHERE String User10000 , 50 Test12 , INSERT INTO DBTEST String,Number VALUESUser30,30 , 50 Test13 , DELETE FROM DBTEST String,Number VALUESUser30,30 ,

50 Test14 , DELETE FROM DBTEST , 1 dbClose Головна функця Test у цикл 50 разв посила запит на сервер, обрахову рзницю мж початком обробки запиту часом закнчення обробки StartTimenew Date обробка запиту EndTimenew Date DeltaEndTime - StartTime У останньому script мамо 14 теств для тестування рзних можли-востей SQL Server. Розглянемо х докладнше . Тести 12 13 характеризують швидксть вставки вилучення елементв

у баз даних. Тест 14 знищу усю таблицю. Запити на вибрку нформац Тест 1 SELECT FROM DBTEST - видлити вс елементи Тест 2 SELECT FROM DBTEST ORDER BY String -вдсортувати ус елементи за значенням у стовбчику String Тест 3 SELECT FROM DBTEST ORDER BY Number- вдсортува-ти ус елементи за значенням у стовбчику Number Тест 4SELECT SumNumber FROM DBTEST GROUP BY

String - скласти ус елементи стовбчика Number за значенням у стовбчи-ку String Тест 5 SELECT SumNumber FROM DBTEST GROUP BY Random - скласти ус елементи стовбчика Number за значенням у стовбчику Random Тест 6 SELECT FROM DBTEST WHERE Number 9 визначити ус елементи, у яких значення у стовбчику Number 9 Тест 7 SELECT FROM DBTEST WHERE String User9 - визначити ус елементи, у яких значення у стовбчику

String User9 Тест 8 SELECT FROM DBTEST WHERE Number in SELECT Number FROM DBTEST - визначити ус елементи з множини чисел Тест 9 SELECT FROM DBTEST WHERE Number in SELECT Number FROM DBTEST WHERE String User50000 - визначити ус елементи з множини елементв, у яких значення у стовбчику String User50000 Тест 10 SELECT FROM DBTEST WHERE Number in

SELECT Number FROM DBTEST WHERE StringUser50000 - визначити ус елементи з множини елементв, у яких значення у стовбчику String User50000 Тест 11 SELECT FROM DBTEST WHERE Number in SELECT Number FROM DBTEST WHERE String User10000 - визначити ус елементи з множини елементв, у яких значення у стовбчику String User10000 Частина 3 РЕЗУЛЬТАТИ ТЕСТУВАННЯ Для наведення прикладу унверсальност при використанн

написан-ного script при вимрюванн швидкод, вимрямо швидксть обробки запитв у систем SQL Server 7.0 та Microsoft Access 97. Щоб script сприймався системою Access без суперечень треба змнити параметри дентифкац var SQLDataBase DSNOTStUIDsaPWDDATABASEOTSt var SQLDataBase DSNOTSttUIDPWD Отриман результати приведемо у таблиц

Тест SQL Server 7.0 Access 9712,673 1,81 27,7815,60337,3565,21148,2397,5450,9030, 49460,3450,23170,3900,287811,2077,1193,8 432,53100,6550,5071111,4699,816120,30,28 5130,310,221455,6234,324 Як видно з результатв проведенного вимрювання запити на локальнй баз даних Access опрацьовуються значно швидше нж на сервер. Аналзуючи запити 2 3, 4 5, 6 7 бачимо, що обробка елементв рзних типв у однакових запитах ма рзний час. Так наприклад сортувати усх елементв за значенням у стовбчику

String опрацьовуться повльнше нж у стовбчику Number- причина у розмру елементв цих типв. Теж саме при обробц запитв 4 5 при сумуванн елементв стовбчика Number за елементами стовбчикв String Random. Стовбчик String зберга значення типу String стовбчик Random зберга значення типу Integer, елементи типв мають рзний розмр обробляються з рзною швидкстю.

Розглядаючи швидксть обробки множин елментв зазначимо, що чии бльше елементв обробля сервер у баз даних тим бльше часу на це йде. Так наприклад запит 10 на пошук у баз даних елемента типу String - User50000 ззайма набагато менше часу нж пошук елементв, що менше User50000. Таких елементв приблизно 50 тисяч х пошук займа досить довгий час. Пошук елементв, що бльше User10000 займа у сервера ще бльший час.

Запити 12,13 визначають час занесення до бази нового рядка вилучення рядка з бази даних. Проводячи наведен дослдження за роботою сервера ми отримумо повну характеристику його роботи. Використамо наведен вище програмн додатки проведемо вимри на нших SQL-серверах. Порвнямо швидкост роботи SQL Server 7.0 Oracle 8 Тест SQL Server 7.0 Oracle 812,673 2,71 27,7818,0637,3568,0948,2398,7550,9031,04 160,3450,470,3900,432811,20711,91793,843 4,05100,6550,781111,46912,021120,30,2951 30,310,2911455,6243,134

Як видно з результатв SQL Server 7.0 працю швидше за Oracle 8 при обробц виброк, а при вставц вилученн елементв у Oracle краш результати. Це можна пояснити рзною будовою ядер обробки запитв. Проведемо такж замри для SQL сервера Informix. Тест SQL Server 7.0 Informix12,673 2,11 27,7816,93237,3567,13848,2397,93450,9030 ,85660,3450,45670,3900,471811,20713,0493

,8434,41100,6550,7481111,46912,814120,30 ,315130,310,311455,6251,48 По результам видно, що SQL Server 7.0 працю повльнше за Informix при обробц простих запитв, а при пошуку порвнянн елементв працю швидше. На запитах по вставц вилученн елементв у Informix SQL Server 7.0 майже однаков результати. Таким чином ми досягли мети данно роботи - побудували програмн

додатки, якими змогли визначити характеристики працездатност рзних серверних баз даних. ВИСНОВОК В результат проведенно роботи було розглянуто рзн фактори, що впливають на швидксть роботи системи SQL Server 7.0. Були написан дв програми, що дають змогу вимрювати швид-ксть обробки запитв багаторвневою серверною системою Microsoft SQL Server 7.0 тим самим дають можливсть дослджувати ефективнсть настройки параметрв системи, дають змогу оцнити оптимальнсть побудови бази даних, оптимальнсть використання

ндексв, правил, обмежень, ефективнсть роботи з транзакцями. Були розглянут сильн слабк боки технологй роботи з програмними додатками - DB-Library та ODBC з DAO. Як приклад роботи програмних додаткв отриман дан про швидкодю систем баз даних SQL Server 7.0 та Microsoft Access 97. Порвнян результати застосування програмних додаткв у системах SQL-серверв таких як Oracle та Informix з швидкстю роботи

Microsoft SQL Server 7.0. Використовуючи написан script-и програмсти розробники систем баз даних типу клнтсервер можуть визначати характеристики створених ними систем з цллю аналзу удосконалення. СПИСОК ВИКОРИСТАНО ЛТЕРАТУРИ 1. Використання Microsoft SQL Server 7.0 Стефан Внкоп вид. Москва 1999 р. 2. Teach Yourself Oracle 8 In 21 Days Cary N. Prague 3.

Секреты Access 97 Уильям Амо изд.Диалектика 1997р. 4. Технологя Java Джо Вебер вид. BHV 1996 р. 5. Журнал PC World 3,7,8 за 97 р 1,9 за 98 р. 6. www.techrepublic.com