Сравнение производительности типов char и varchar

24

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Курсовая работа

по дисциплине «Базы данных»

тема: «Сравнение производительности типов char и varchar»

Содержание

Введение

1. Планирование эксперимента

2. Реализация эксперимента

2.1 Требования к программе

2.2 Настройка окружения и создание баз данных

2.3 Разработка приложения

2.4 Проведение экспериментов

3. Анализ результатов

Заключение

Приложение А (обязательное) «Скрипты на создание таблиц»

Приложение Б (обязательное) «Результаты выполнения программы»

Введение

Язык SQL является стандартом в области языков реляционных баз данных. Сегодня существует уже три версии стандарта от 1992, 1999 и 2003 годов. В каждой версии стандарта основные строковые типы это char и varchar определённой длины. Изначально предполагалось, что тип char, занимая фиксированный размер, способствует ускорению выполнения запросов, т.к. алгоритму, реализующему запрос, не потребуется выполнять дополнительных проверок на длину строки. Тип varchar был введён для уменьшения необходимого места для хранения данных, т.к. если строки имеют различную длину, а для поля указана максимальная, то строки почти всегда занимают меньше места, чем им отведено. В случае с char, незаполненное место записывается пробелами. Для реализации физического хранения значений типа varchar нужно дополнительно к каждому значению хранить целочисленный идентификатор - длину конкретного значения, либо использовать некий символ конца строки. Обработка этих дополнительных данных и уменьшает производительность СУБД.

Попытаемся определить, действительно ли существует разница в скорости выполнения запросов, построенных на таблицах с использованием char и varchar. Заметим, что язык SQL предполагает всего лишь интерфейс взаимодействия прикладных программ с СУБД. Это, в частности, значит, что разделение строковых типов, проведённое на уровне языка, может отсутствовать на уровне СУБД: типы могут быть реализованы одинаково.

1. Планирование эксперимента

В первую очередь мы должны разделить эксперименты на проводимые с типом char и типом varchar. Во вторых, следует уделить внимание тому, где расположены атрибуты: в первичном ключе или нет. Все СУБД создают явные или неявные индексы на атрибуты первичного ключа. Интересно узнать, как индексы влияют на выполнение запросов с использованием атрибутов рассматриваемых типов.

В третьих, эксперименты можно разделить по видам запросов. Нас интересуют основные виды запросов, т.е. запросы на вставку данных (insert), обновление данных (update) и выборку данных (select). Запросы на выборку можно провести с использованием 2х операций сравнения, специфичных для строковых типов: операция сравнения на равенство (=) и сравнение на совпадение по маске (like).

Таким образом, получаем следующее разделение (рисунок 1):

Рисунок 1 - Разбиение экспериментов

В итоге получается 16 экспериментов. Рассмотрим подробнее каждый эксперимент.

Ввиду ограничений конкретных СУБД (Interbase не поддерживает суммарную длину строк в первичном ключе более 200), возьмём длину строк равной 50. Будем заполнять эти атрибуты строками длиной от 10 до 48 символов (два символа потребуются далее, при обновлении). Длину и содержание строк будем генерировать случайным образом.

Для того чтобы расходы процессорного времени на обработку каждой строки таблицы были больше (или сопоставимы), чем расходы на перемещение между строками, в каждом случае будем заводить не одно, а несколько (возьмём 3) атрибутов типов char или varchar. В случае с первичным ключом, первичный ключ будут образовывать 5 соответствующих атрибутов плюс один идентификатор типа int, заполняемый возрастающей последовательностью чисел для поддержания явной уникальности первичного ключа. Остальные атрибуты (возьмём также 5 штук) будут иметь тип int и будут заполняться случайным образом. В случае без первичного ключа, будут присутствовать те же 9 атрибутов (3+1+5), только в таблице будет отсутствовать определение первичного ключа.

Таким образом, получаем скрипты на создание таблиц (4 штуки), представленные в приложении А. При реализации на конкретной СУБД знаки пунктуации могут отличаться от приведённых.

В случае вставки будем записывать заранее сгенерированный набор данных в 4 таблицы. В случае обновления, будем использовать запрос на обновление всех 3 полей типов char или varchar без условия на выборку (where). При обновлении данных, будем дописывать к значениям атрибутов по 2 символа в начало строки.

Update table1 set

f1='12'+f1,

f2='12'+f2,

f3='12'+f3

Уникальность первичного ключа в обоих случаях обеспечит специально введённый атрибут типа int. В случае выборки будем выбирать 5 полей типов char или varchar с использованием в одном случае сравнения на равенство (=), а в другом - сравнения по маске (like). В качестве аргумента для сравнения выберем значение атрибутов первой строки таблицы (для выполнения запроса СУБД всё равно должна просмотреть всю таблицу).

Select f1,f2,f3

From table1

Where f1='xxx' and f2='xxx' and f3='xxx'

Select f1,f2,f3

From table1

Where f1 like 'xxx' and f2 like 'xxx' and f3 like 'xxx'

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

Засечь время можно с точностью до миллисекунд. Для обеспечения малой погрешности, выполнение самого быстрого запроса (предположительно на выборку на равенство) должно составить несколько секунд. Предварительно можно заключить, что необходим набор данных от 10000 до 50000 строк.

запрос база данных char varchar

2. Реализация эксперимента

2.1 Требования к программе

Для автоматизированного проведения эксперимента создадим программу, которая будет выполнять запросы и засекать время их выполнения. Определим необходимые функции программы.

- Генерация данных для вставки в 4 таблицы, существующие в БД. В каждую таблицу должен быть вставлен один и тот же набор данных. Кроме того, генерация такого набора данных может занять некоторое время, которое не должно суммироваться с общим временем выполнения запроса. Таким образом, набор данных следует сгенерировать заранее, по определённым выше правилам. Имеет смысл генерировать запросы на вставку данных сразу в виде строки SQL кода, готовой к выполнению.

- Вставка данных с замером затраченного времени.

- Обновление данных с замером затраченного времени.

- Извлечение данных, с замером затраченного времени. Имеет смысл замерить только время получения отклика от запроса, т.е. нет необходимости просматривать возвращённые запросом результаты.

- Представление затраченного времени в форме, удобной для дальнейшей обработки. Достаточно вывести время в виде текста, который можно скопировать в буфер обмена.

В качестве инструмента для создания программы выберем Delphi 7.

2.2 Настройка окружения и создание баз данных

В качестве конкретной СУБД, проведём эксперименты сначала на Interbase 6.5, входящую в дистрибутив Delphi, а затем на свободно распространяемой СУБД MySQL 5.037. Для доступа к соответствующим СУБД в Delphi используются различные компоненты: для доступа к Interbase используются свои компоненты, в то время как для доступа к MySQL требуются компоненты BDE или ADO для доступа к ODBC. Мы используем компоненты BDE и ODBC драйвер для MySQL версии 3.51. Для того чтобы его настроить требуется создать БД. Для этого используем оболочку MySQL: MySQL ControlCenter версии 0.94 (рисунок 2).

Рисунок 2 - Создание БД в MySQL ControlCenter

Мы создали БД и назвали её test1. Создадим в БД таблицы, представленные в приложении А. Далее нам требуется настроить ODBC - создать запись для доступа к нашей БД. Поскольку мы работаем под операционной системой Windows XP, открываем «Пуск - Настройки - Администрирование - ODBC». Нажимаем «Добавить», выбираем драйвер для MySQL и настраиваем его как показано на рисунке 3:



Рисунок 3 - Настройка ODBC драйвера MySQL

Теперь мы можем обращаться к нашей БД по введённому нами псевдониму mysqltest1.

Далее создадим БД в Interbase. Для этого открываем IBConsole (встроенную оболочку СУБД Interbase). Соединяемся с сервером и создаём новую БД с именем test1 (рисунок 4).

Рисунок 4 - Создание БД в IBConsole

Далее создаём в БД таблицы, по скрипту из приложения А.

Теперь окружение настроено, и можно приступать к разработке приложения и проведению экспериментов.

2.3 Разработка приложения

Создадим в Delphi форму, разместим на ней 3 кнопки, компонент RichEdit для вывода результатов, и компоненты для доступа к базам данных (рисунок 5). Для доступа к Interbase используем компоненты (TIBDataBase, TIBQuery, TIBTransaction) с соответствующей вкладки, для доступа к MySQL используем компоненты (TdataBase, TQuery) с вкладки BDE. Для выполнения тестов нам достаточно одного запроса (TQuery, TIBQuery) на БД, т.к. текст запросов будет генерироваться динамически.

Рисунок 5 - Форма Delphi с размещёнными компонентами

Далее инициализируем компоненты для работы с базами данных. Свяжем запросы с соответствующими базами данных и настроем базы данных. Для Interbase укажем соответствующий файл БД, для MySQL, выберем созданный ранее ODBC алиас (псевдоним) (рисунок 6).



Рисунок 6 - Настройка компонента TDataBase

Для каждого компонента БД настроим параметры пользователя и пароля для автоматического соединения с БД. Явно укажем пользователя и его пароль для соединения с БД и снимем флаг «требовать проверку пароля».

Для хранения набора тестовых данных в программе создадим класс:

//класс для хранения тестовых вставляемых данных

Tdata = class

//поля таблицы

f1,f2,f3:string;

i1,i2,i3,i4,i5,i6:integer;

//запросы на вставку данных в 4 таблицы

query_pk_ch,

query_pk_vc,

query_np_ch,

query_np_vc:string;

//генерировать данные и текст запросов

procedure generate(num:integer);

end;

Каждый экземпляр этого класса - строка в каждой из 4х таблиц. Экземпляры хранятся в свойстве Data формы. Построим программу таким образом, чтобы инициализировать набор данных только один раз. Для этого используем свойство Enabled кнопок. В методе generate класса Tdata создана подпрограмма genstr для инициализации каждого строкового атрибута случайным набором символов.

Теперь напишем обработчики для кнопок, взывающих выполнение экспериментов с БД. Код каждого обработчика включает следующие элементы:

- подключение к БД,

- внесение данных в таблицы с замером времени,

- обновление данных таблиц с замером времени,

- чтение данных из таблиц с замером времени,

- удаление данных из таблиц (для возможности дальнейших экспериментов),

- отключение от БД.

Теперь приложение нужно отладить и можно проводить эксперименты.

2.4 Проведение экспериментов

Предполагалось, что тестовый набор будет содержать от 10000 до 50000 строк. Тогда чтобы выполняться программе нужно от 4 до 11 Мб свободной оперативной памяти, чтобы не использовать файл подкачки и не замедлять выполнение запросов. Эта цифра получена следующим путём: хранение тестовых данных, минимум: 10000…50000*(3*50+6*4) байт = 1700…8500 Кб = 1,7…8,5 Мб. Пространство для выполнения программы: минимум 2 Мб.

В ходе проведения эксперимента выяснилось, что производительность СУБД сильно отличается: в MySQL запросы на вставку выполняются очень медленно, что, вероятно, вызвано, использованием медленного ODBC драйвера. СУБД активно использовала жёсткий диск. Поэтому было принято решение увеличить мощность тестового набора для Interbase (до 30000 строк) и уменьшить для MySQL (до 3000 строк).

Тестирование производилось на компьютере cо следующими характеристиками: процессор - Intel Sempron 2800+ GHz, оперативная память 512 Mb, винчестер -WD , 7200 Об/мин, операционная система Windows XP SP 2.

Для достоверности эксперименты с каждой СУБД проведены 3 раза. Результаты выполнения программы представлены в приложении Б.

Рисунок 7 - Программа во время тестирования

3. Анализ результатов

Проведём анализ результатов, исходя из цели, поставленной во введении. На следующих графиках представлено время выполнения тестов в миллисекундах.

Рисунок 8 - Вставка данных Interbase

Как видно из рисунка 8 вставка данных в атрибуты типа varchar по времени не отличается от вставки в атрибуты типа char. Есть значительное (~30%) отличие в использовании первичного ключа. Это говорит о том, что происходит обновление соответствующего индекса первичного ключа, которое увеличивает время обработки.

Рисунок 9 - Вставка данных MySQL

Из рисунка 9 видно, что в MySQL есть различие в использовании char и varchar. Как и ожидалось, varchar обрабатывается чуть медленнее (что более заметно при использовании первичного ключа). Так же наблюдается различие в использовании первичного ключа, однако гораздо менее заметное (~2%), чем в случае Interbase.

Рисунок 10 - Обновление данных Interbase

Рисунок 11 - Обновление данных MySQL

Как видно из рисунков 10 и 11, при обновлении данных имеет значение, какой тип имеют обновляемые атрибуты. Varchar в первичном ключе обрабатывается медленнее на ~30%. Не в первичном ключе можно считать скорость обновления примерно равной, и заметно меньшей, чем в первичном ключе. Таким образом, можно заключить, что различие между типами может лежать в способах построения индекса первичного ключа.

Рисунок 12 - Выборка данных Interbase

Глядя на рисунок 12 можно сделать следующие выводы. Во-первых, индекс имеет большое значение при выполнении запросов на выборку в СУБД Interbase. В обоих случаях сравнения скорость выполнения запроса на индексируемых полях значительно выше, более того, её можно считать нулевой. Во-вторых, при выборке незначительно быстрее оказываются поля типа varchar.



Рисунок 13 - Выборка данных MySQL

Из рисунка 13 видно, что в СУБД MySQL индексы так же имеют значение, более того, индексы на varchar работают эффективнее. В целом запросы выполняются довольно быстро (заметим однако, что при тестировании MySQL брался набор данных в 10 раз меньший чем при тестировании Interbase).

Заключение

В целом можно заключить, что производительность запросов при обработке типов char и varchar примерно одинакова, если не используются индексы.

Этот вывод наиболее очевиден при рассмотрении обновления данных, где обновление атрибутов не первичного ключа выполняется примерно одинаково, в то время как обновление атрибутов первичного ключа выявляет различие между двумя рассматриваемыми типами. Т.е. различие между типами может лежать в способах построения индекса.

При выполнении запросов на выборку, тип varchar оказывается эффективнее, особенно при использовании индексов. При выполнении запросов на вставку и обновление данных тип varchar менее эффективен, опять же особенно при использовании индексов.

Список использованной литературы

"SQL Полное руководство" BHV, Москва, 2003

Мамаев Е., Шкарина Л. «Microsoft SQl Server 2000 для профессионалов». - СПб:Питер, 2002

Хоторн Роб «Разработка баз данных, Micrososoft SQL Server 2000». - Вильямс, 2001

Шарон Б., Мэйбл Грэг «Sql Server 2000, Энциклопедия программиста». - ДиаСофт, 2001

Приложение А

(обязательное)

«Скрипт на создание таблиц»

/*не первичный ключ, char */

CREATE TABLE "TEST_NPK_CHAR"

(

"F1"CHAR(50),

"F2"CHAR(50),

"F3"CHAR(50),

"I1"INTEGER,

"I2"INTEGER,

"I3"INTEGER,

"I4"INTEGER,

"I5"INTEGER,

"I6"INTEGER

);

/*не первичный ключ, varchar */

CREATE TABLE "TEST_NPK_VARCHAR"

(

"F1"VARCHAR(50),

"F2"VARCHAR(50),

"F3"VARCHAR(50),

"I1"INTEGER,

"I2"INTEGER,

"I3"INTEGER,

"I4"INTEGER,

"I5"INTEGER,

"I6"INTEGER

);

/* первичный ключ, char */

CREATE TABLE "TEST_PK_CHAR"

(

"F1"CHAR(50) NOT NULL,

"F2"CHAR(50) NOT NULL,

"F3"CHAR(50) NOT NULL,

"I1"INTEGER NOT NULL,

"I2"INTEGER,

"I3"INTEGER,

"I4"INTEGER,

"I5"INTEGER,

"I6"INTEGER,

PRIMARY KEY ("F1", "F2", "F3", "I1")

);

/* первичный ключ, varchar */

CREATE TABLE "TEST_PK_VARCHAR"

(

"F1"VARCHAR(50) NOT NULL,

"F2"VARCHAR(50) NOT NULL,

"F3"VARCHAR(50) NOT NULL,

"I1"INTEGER NOT NULL,

"I2"INTEGER,

"I3"INTEGER,

"I4"INTEGER,

"I5"INTEGER,

"I6"INTEGER,

PRIMARY KEY ("F1", "F2", "F3", "I1")

);

Приложение Б

(обязательное)

«Результаты выполнения программы»

Примечание: первая колонка - начало выполнения запросов, вторая - окончание, третья - длительность в миллисекундах, третья - условное название теста. Приведены результаты первого выполнения тестов. Для Interbase тестовый набор из 30000 строк, для MySQL - из 3000.

25052773250560983325генерация данных для вставки

начало тестирования Interbase

250585322509429335761вставка пк char

250942932513008535792вставка пк varchar

251300852515332823243вставка не пк char

251533282517649123163вставка не пк varchar

25176501251849848483обновление пк char

251849942519837313379обновление пк varchar

25198373252034705097обновление не пк char

25203470252092085738обновление не пк varchar

252092082520924840выборка (=) пк char

25209258252092580выборка (=) пк varchar

2520926925209709440выборка (=) не пк char

2520971925210140421выборка (=) не пк varchar

25210150252101500выборка (like) пк char

252101502521016010выборка (like) пк varchar

2521016025210721561выборка (like) не пк char

2521072125211271550выборка (like) не пк varchar

конец тестирования Interbase

2534997125350301330генерация данных для вставки

начало тестирования MySQL

253533362542593072594вставка пк char

254259302549848472554вставка пк varchar

254984942556820569711вставка не пк char

255682052564004871843вставка не пк varchar

2564004825640619571обновление пк char

25640629256417801151обновление пк varchar

2564178025642281501обновление не пк char

2564229125642612321обновление не пк varchar

256426122564263220выборка (=) пк char

25642652256426520выборка (=) пк varchar

256426622564267210выборка (=) не пк char

256426822564269210выборка (=) не пк varchar

256427022564271210выборка (like) пк char

25642712256427120выборка (like) пк varchar

256427222564274220выборка (like) не пк char

256427422564276220выборка (like) не пк varchar

конец тестирования MySQL

Размещено на Allbest.ru