Реферат: Скорость обработки запросов на SQL серверах

МІНІСТЕРСТВО ОСВІТИ УКРАЇНИ

КИЇВСЬКИЙ УНІВЕРСІТЕТ ІМЕНІ ТАРАСА ШЕВЧЕНКА

Факультет кібернетики

Кафедра теоретичної кібернетики

ВИПУСКНА КВАЛІФІКАЦІЙНА РОБОТА

БАКЛАВРА

На тему : Швидкість обробки запитів

на SQL-серверах

Виконавець: студент 4 курсу

Дима Павло Костянтинович

Науковий керівник : ассистент Коваль Ю.В.

Випускна кваліфікаційна робота бакалавра

Рекомендована до захисту в ДЕК

Протокол №10, від “25” травня 2000 р.

Зав.кафедри проф. Белов Ю.А

мКиїв - 2000

ЗМІСТ

1.Вступ………..………………………………………………………….2

2.Швидкість роботи SQLсервера…………………………………….5

2.1Технологія DAO…….………………………………………………6

2.2Технологія ODBC…..………………………………………………6

2.3 JSCRIPT……………………………………………………………..7

3. Результати тестування..…………………………………………….17

Висновок………………………………………………………………..21

Список використаної літератури…………………………………….22

Частина 1 : ВСТУП

У світі сучасних інформаційних технологій особливо виділяє-ться всесвітня мережа Internet. Опорним елементом в цій мережі є сервера баз данних. Саме на сервері зберігається інформація до якої можуть отримати доступ користувачі. При роботі користувача з базою данних в Internet, він посилає запит до бази даннихю цей запит сформовано за правилами мови SQL(Structured Query Language) – мова структурованих запитів.

В мережах використовуються такі SQL–сервера як Oracle, Microsoft SQLServer, Sybase, dbase, Informix, Progress та інші.Як приклад SQL– сервера візьмемо Microsoft SQLServer 7.0 .

Система MS SQLServer- це багаторівнева система обміну данними від клієнта до сервера, дозволяє створювати і підтримувати бази данних.

На відіну від бази данних орієнтованих на великі комп”ютери та міні-комп”ютери, до серверної бази данних користувачі отримують доступ зі своїх комп”ютерів, а не з допомогою терміналів. При цьому механізми роботи SQL-сервера допомагає розв”язувати проблеми, що виникають у результаті звернень до бази данних багатьох комп”ютерів, кожен з яких може незалежно звертатись до різних частин бази данних, що зберігається на сервері.

Зв”язок системи клієнт/сервер з комп”ютерами-клієнтами забезпечу-ється через засоби передачі данних і базової операційної системи. Для цього система клієнт/сервер MS SQL Server 7.0 використовує вбудовані компоненти ОС WindowsNT.

Серверна СУБД MS SQLServer7.0 добавляє до мережевих компонент окремі елементи сервіса, такі як OLE-DB відповідає за зв”язок та упровадження об”єктів баз даних і ODBC — відкритий інтерфейс баз даних. Ці компоненти забезпечують тісний зв”язок необхідного програмного забезпечення.

SQLServer7.0 має багато інструментів, призначених для розробки зовнішнього інтерфейсу бази даних. Окрім мови SQL для зберігання, вибірки данних можна викоритовувати додатки [приложения] з графічним інтерфейсом користувача GUI. Це дуже спрощує розробку баз даних, таблиць і зв”язків між ними. Зручно використовувати SQLServer разом з програмами для роботи в Internet.

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

Частина 2 : ШВИДКІСТЬ РОБОТИ SQLSERVER7.0

В процесі роззвитку SQLServer при розробці клієнтських додатків використовувалось декілька підходів. Ядром системи завжди був процессор обробки запитів, який працював на сервері. Запити писались на мові SQL вводились і опрацьовувались через утіліти типу Query Analyzer.

При написанні програмних додатків для взаємодії з SQLServer частіше за усе використовують :

1<span Times New Roman"">

DB-Library

2<span Times New Roman"">

ODBC і DAO

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

Це забезпечується не тільки тим, що він надає оптимізований інтерфейс API, але використовує в програмах особисту мову системиSQLServer. ODBC (як і SQL-OLE) надають такий же рівень сервісу але створюють додатковий рівень абстракції між додатком і та процессором запитів SQL Server.

В DB-Lib не підтримується зв”язок таблиць або автоматичного оновлення значень. Це можна розглядати як позитив оскільки гарантується повний контроль над взаємодією програмного додатку з сервером і оптимізацією зв”язків. Додатково програмісту додається повний контроль над виявденням і обробкою помилок, пов”язаних з транзакціями.

2.1 ТЕХНОЛОГІЯ DAO

(Data Access Object– об”єкти доступу до данних) приззначена для викоритання об”єктів, методів, властивотей, значно полегшшують роботу програмного додатку з базою даних. Для обміну інформацією з SQLсервером в об”єктах DAO викоритову-ються рівні дотупу Jetі ODBC, одна вони утворюють ще один рівень абстракції між додатками і викликами функцій ODBC, які використовуються при обробці запитів.

Технологія DAO для роботи з базами даних, таблицями, видами передбачає викоритання коллекцій об”єктів. Наприклад для створення нової таблиці легше викликати метод Add відповідного об”єкта, між використовувати стандартний підхід технології ODBC.

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

Але треба відмітити, що технологія DAO поступово витісняється більш прогресивною технологією ADO(ActiveX Data Object – об”єкти данх ActiveX) .

2.2 ТЕХНОЛОГІЯ ODBC

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

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

Головним недоліком ODBCє необхідність в трансляції запитів. Проведення цієї додаткової роботи відповідно відображається на швидкості доступу до даних. При роботі з ODBC можна отримати суттєвий виграш у швидкості обробки даних, якщо в системі клі-єнт/сервер саме на сервері системи SQLServer буде оброблятись запит, а не на клієнтському комп”ютері. Також приріст у швидкості роботи ODBC дає використання встроєнних процедур.

2.3 JSCRIPT

Тепер перейдемо до процесів, що пов”язані з вимірюванням швидкості системи SQLServer7.0 . У данній роботі прпонується написання програмного додатку на встроєнному shell OS Windows NT - Java Script. Сам програмний додаток це програма написана для інтерпритуюча мова. Файл з розширенням . js буде виконавчим для OS Windows NT. Спочатку напишемо Script для створення бази даних :

// ADO Flags

varadLockOptimistic = 3;

var adOpenStatic =3;

var SQL_DataBase =«DSN=OTSt;UID=sa;PWD=;DATABASE=OTSt»

//var SQL_DataBase =«DSN=OTStt;UID=;PWD=»

var oConn;

var oRs;

function db_Connect(){

// Create ADO ConnectionObject. Use IISSDK OBDC Souce with

// default sa account andno password

oConn= new ActiveXObject(«ADODB.Connection»);

oConn.Open(SQL_DataBase);

oConn.CommandTimeout=600;

}

function db_Record(SQL){

var oRs;

// Create ADO RecordsetComponent, and associate it with ADO

connection

oRs = newActiveXObject(«ADODB.Recordset»);

oRs.ActiveConnection =oConn;

// Get empty recordset

oRs.Source = SQL;

oRs.CursorType =adOpenStatic; //use a cursor

otherthan Forward Only

oRs.LockType =adLockOptimistic; //use a locktype

permitting insertions

oRs.Open();

return oRs;

}

function db_Close(){

oConn.Close();

}

function db_Requery(Rec,SQL){

Rec.Close();

Rec.Source = SQL;

Rec.Open();

}

db_Connect();

try {

oRs=db_Record(«CREATE TABLEdbo.DBTEST ( String char (30) NULL, Number int NOT NULL DEFAULT (0), Random intNOT NULL DEFAULT (0))»);

} catch (e) {

}

oRs=db_Record(«SELECT* FROM DBTEST WHERE 0=1»);

for(i=0;i<100000;i++){

oRs.Addnew();

oRs(«String»).value=«User»+i;

oRs(«Number»).value=i;

oRs(«Random»).value=Math.round(100*Math.random());

oRs.Update();

if(i%10000==0)

WScript.echo(«Now inbase present „+i+“ records»);

}

WScript.echo(«Completed»);

База даних буде мати вид :

String

Number

Random

User1

User2

User3

....

... .

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

DSN– ім”я зв”язку, визначається при утворенні джерела даних ODBC.

UID–ідентифікатор користувача, використовується при регістрації на сервері, у SQLServerідентифікатор користувача завжди має починатись з “ sа ” .

PWD–або “ password” кодове слово доступу , необхідний для регістрації на сервері. В даному випадку не використовуємо.

Функція Record проводить запис у всі рядки бази даних DBTestстворені значення, у стовбчик Random– створені генератором випадкових чисел значення від 0 до 99 .

Кількість рядків у таблиці буде 10 000, створення займає 3,5 хвилин. Стовбчик Number буде ключем.

Напишемо ще один Script, для тестування швидкості роботи SQLServer . Щоб визначети час відповіді на запит як умога точніше будемо посилати на сервер не один запит, а декілька десятків однакових запитів, обрахувавши середній час відповіді отримаємо точний час обробки запиту і знаходження сервером результуючої множини елементів. Але треба враховувати такий випадок — якщо для підвищення точності послати декілька сотень однакових запитів, то SQLServer почне хешування таблиці для оптимізації своєї роботи

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

// ADO Flags

var adLockOptimistic= 3;

var adOpenStatic =3;

var SQL_DataBase =«DSN=OTSt;UID=sa;PWD=;DATABASE=OTSt»

//var SQL_DataBase =«DSN=OTStt;UID=;PWD=»

var oConn;

var oRs;

functiondb_Connect(){

// Create ADO Connection Object. Use IISSDK OBDC Souce with

// default sa account and no password

oConn= new ActiveXObject(«ADODB.Connection»);

oConn.Open(SQL_DataBase);

oConn.CommandTimeout=600;

}

functiondb_Record(SQL){

var oRs;

// Create ADO Recordset Component,and associate it with ADO connection

oRs = newActiveXObject(«ADODB.Recordset»);

oRs.ActiveConnection = oConn;

// Get empty recordset

oRs.Source = SQL;

oRs.CursorType = adOpenStatic; // use a cursorother than Forward Only

oRs.LockType = adLockOptimistic; // use a locktypepermitting insertions

oRs.Open();

return oRs;

}

functiondb_Close(){

oConn.Close();

}

functiondb_Requery(Rec,SQL){

Rec.Close();

Rec.Source = SQL;

Rec.Open();

}

functionTest(TestNumber, Query, CountTests){

Max=0;

Min=1000000;

Delta=0;

Ave=0;

WScript.echo(«Query#»+TestNumber);

for(i=0;i<CountTests;i++){

StartTime=new Date();

db_Requery(oRs,Query);

EndTime=new Date();

Delta=(EndTime-StartTime);

WScript.echo(" Probe #"+i+"Result="+Delta+" ms");

Ave=(Ave*i+Delta)/(i+1);

}

WScript.echo("Result="+Ave+" ms");

}

db_Connect();

oRs=db_Record(«SELECT* FROM DBTEST WHERE 1=0»);

Test(1, "SELECT * FROM DBTEST ", 50 );

Test(2, "SELECT * FROM DBTEST ORDER BY String",50);

Test( 3 ,"SELECT * FROM DBTEST ORDER BY Number ",50);

Test(4,«SELECT Sum(Number) FROM DBTEST GROUP BY String»,50);

Test(5, "SELECT Sum(Number) FROM DBTEST GROUP BY

Random",50);

Test( 6, "SELECT * FROM DBTEST WHERE Number = 99999", 50);

Test(7 ,"SELECT * FROM DBTEST WHERE String ='User99999'",50);

Test(8,«SELECT* FROM DBTEST WHERE Number in (SELECT

Number FROM DBTEST ) », 50 );

Test(9,«SELECT * FROM DBTEST WHERE Number in (SELECT

Number FROM DBTEST WHERE String < ' User50000 ' ) »,50);

Test(10, "SELECT * FROM DBTEST WHERE Number in (SELECT

Number FROM DBTEST WHEREString='User50000')", 50);

Test(11," SELECT * FROM DBTEST WHERE Number in (SELECT

Number FROM DBTEST WHEREString > ' User10000 ' ) ", 50);

Test(12,«INSERT INTO DBTEST (String,Number)

VALUES('User30',30) », 50);

Test(13,«DELETE FROM DBTEST (String,Number)

VALUES('User30',30) »,50);

Test(14 ,"DELETE FROM DBTEST ", 1);

db_Close()

Головна функція Test у циклі 50 разів посилає запит на сервер, обраховує різницю між початком обробки запиту і часом закінчення обробки :

StartTime=new Date();

« обробка запиту »

EndTime=new Date();

Delta=(EndTime - StartTime);

У останньому script маємо 14 тестів для тестування різних можли-востей SQLServer. Розглянемо їх докладніше .

Тести 12 і 13 характеризують швидкістьвставки і вилучення елементів у базі даних.

Тест 14 знищує усю таблицю.

Запити на вибірку інформації :

Тест 1:" SELECT * FROM DBTEST " - виділити всі елементи;

Тест 2: " SELECT * FROM DBTEST ORDER BY String" -відсортувати усі елементи за значенням у стовбчику “String”;

Тест 3:" SELECT * FROM DBTESTORDER BY Number"- відсортува-ти усі елементи за значенням у стовбчику “ Number ”;

Тест 4:«SELECT Sum(Number) FROM DBTEST GROUP BY String»

— скласти усі елементи стовбчика “ Number ” за значенням у стовбчи-ку “String”;

Тест 5:" SELECT Sum(Number) FROM DBTEST GROUP BY

Random" — скласти усі елементи стовбчика “ Number ” за значенням у стовбчику “ Random ”;

Тест 6:" SELECT * FROM DBTEST WHERE Number = 99999" – визначити усі елементи, у яких значення у стовбчику “ Number ” = 99999;

Тест 7:" SELECT * FROM DBTEST WHERE String = 'User99999'"- визначити усі елементи, у яких значення у стовбчику “String “ = 'User99999' ;

Тест 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 WHEREString='User50000')" — визначити усі елементи з множини елементів, у яких значення у стовбчику “String “ =' User50000';

Тест 11: " SELECT * FROM DBTEST WHERE Number in (SELECTNumber FROM DBTEST WHERE String > ' User10000 ' ) "- визначити усі елементи з множини елементів, у яких значення у стовбчику “String “ > ' User10000' ;

Частина3 : РЕЗУЛЬТАТИ ТЕСТУВАННЯ

Для наведення прикладу універсальності при використанні написан-ного script при вимірюванні швидкодії, виміряємо швидкість обробки запитів у системі SQLServer 7.0 та Microsoft Access97.

Щоб script сприймався системою Access без суперечень треба змінити параметри ідентифікації :

//var SQL_DataBase = «DSN=OTSt;UID=sa;PWD=;DATABASE=OTSt»

var SQL_DataBase = «DSN=OTStt;UID=;PWD=»

Отримані результати приведемо у таблиці :

Тест №

SQL Server 7.0

Access97

2,673

1,81

7,781

5,603

7,356