Кілька прийомів роботи з базами даних

Автор: Сергій Дуплік, Королівство Delphi


Дана стаття призначена в основному для тих, хто починає працювати з базами даних. Тут зібрані прийоми, спрямовані на оптимізацію та прискорення роботи з базами даних. Описані приклади є результатом багаторічної роботи автора з СУБД MS SQL Server, Oracle і Access. Приклади описуються в загальному вигляді, без прив'язки до будь-якої конкретної СУБД.

Дана стаття призначена в основному для тих, хто починає працювати з базами даних. Тут зібрані прийоми, спрямовані на оптимізацію та прискорення роботи з базами даних. Описані приклади є результатом багаторічної роботи автора з СУБД MS SQL Server, Oracle і Access. Приклади описуються в загальному вигляді, без прив'язки до будь-якої конкретної СУБД.

1. Вибираємо з бази даних тільки ті поля, які нам потрібні


Запити види:

select * from …

можуть дуже сильно навантажити як сервер, так і комп'ютер користувача, особливо якщо таблиці містять великі символьні або виконавчі поля. Наприклад, навіщо вибирати поле з фотографією співробітників, коли потрібні тільки прізвища і дні народжень? Якщо надалі знадобиться відображати і фотографії, це все одно зажадає переробки програми, додавання нових компонентів на форму і т.д. Тоді ж можна буде додати додаткове поле в запит.

Тому, якщо полів багато або вони великі, то в select перераховуємо тільки ті, які нам необхідні саме зараз. Це не тільки зменшить навантаження на комп'ютери, але і значно зменшить мережевий трафік.


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


2. Збережені процедури краще процедур, реалізованих у коді програми


І ось з яких причин.

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


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


По-третє, зміни процедури, що зберігається не тягнуть оновлення програми. А отже, користувач може працювати з програмою, не помічаючи змін. У більшості випадків йому навіть не доведеться перезавантажувати програму. При зміні запиту, що зберігається в коді програми, всі користувачі, що запустили програму, повинні будуть її перезапустити. Більш того, якщо програма запускається з якогось певного місця (наприклад, загальнодоступного каталогу на сервері), файл з програмою можна буде оновити до тих пір, поки є хоча б один користувач, який її запустив. Система просто не дасть змінити або видалити файл.


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


І, нарешті, по-п'яте, з точки зору безпеки, процедури, що зберігаються краще тим, що по мережі не передаються тексти запитів, по яких можна проаналізувати структуру бази даних. З цієї ж точки зору можна використовувати уявлення (view), але уявлення часто негативно позначаються на часі роботи, особливо якщо одне подання робить вибірки з інших.


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


Даний розділ, зрозуміло, годиться тільки для тих СУБД, які підтримують роботу зі збереженими процедурами.


3. Будуємо фільтр


Припустимо, що у вас є набір даних, що містить результат виконання запиту або збереженої процедури. Потрібно вибрати деяку підмножину записів з цього набору даних, що відповідають декільком умовам. Умови задаються установкою відповідних значень різних компонентів. Завдання: побудувати рядок фільтра виду: условіе1 І условіе2 І …


Приклад побудуємо для трьох полів:


  1. числове; ім'я поля NumericField; значення береться з SpinEdit, причому фільтрація відбувається тільки у випадку зазначення позитивних значень
  2. текстове; ім'я поля StringField; береться з Edit, фільтрація здійснюється в разі, якщо вказана не порожній рядок
  3. посилання з класифікатора, а ім'я поля ClassField; береться з DBLookup-ComboBox, з яким пов'язаний класифікатор, фільтрація здійснюється в разі, якщо обраний елемент класифікатора (ідентифікатор не дорівнює 0).

Строкову змінну, в якій буде отриманий результат, назвемо sFil-ter.

Часто доводиться бачити такий код:

sFilter:=””;
/ / Перше поле
if SpinEdit.Value > 0 then
sFilter := “NumericField = ” + inttostr(SpinEdit.Value);
/ / Друге поле
if Edit.Text <> “” then
begin
if sFilter <> “” then
sFilter: = sFilter + "and StringField =" "" + Edit.Text + "" ""
else
sFilter := “StringField = “”” + Edit.Text + “”””;
end;
/ / Третє поле
if DBLookupComboBox.KeyValue <> 0 then
begin
if sFilter <> “” then
sFilter := sFilter + ” and ClassField = ” +
inttostr(DBLookupComboBox.KeyValue)
else
sFilter: = "ClassField =" + inttostr (DBLookupComboBox.KeyValue);
end;

Код досить заплутаний, містить дублювання команд перетворення даних (наприклад, двічі зустрічається рядок inttostr (DBLookupComboBox.KeyValue)) і при необхідності внесення змін або доповнень в нього легко зробити помилку. Тому такий код потрібно спростити. Наприклад, наступним чином.

sFilter:=””;
/ / Перше поле
if SpinEdit.Value > 0 then
begin
if sFilter <> “” then sFilter := sFilter + ” and “;
sFilter: = sFilter + "NumericField =" + inttostr (SpinEdit.Value);
end;
/ / Друге поле
if Edit.Text <> “” then
begin
if sFilter <> “” then sFilter := sFilter + ” and “;
sFilter := sFilter + ” StringField = “”” + Edit.Text + “”””;
end;
/ / Третє поле
if DBLookupComboBox.KeyValue <> 0 then
begin
if sFilter <> “” then sFilter := sFilter + ” and “;
sFilter: = "ClassField =" + inttostr (DBLookupComboBox.KeyValue);
end;

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


Часто виникає питання, навіщо в найпершому фільтрі потрібна рядок

if sFilter <> “” then sFilter := sFilter + ” and ”

Адже в цей момент мінлива sFilter завжди буде порожньою. Однак наявність цього рядка дозволяє спокійно додавати нові фільтри в початок коду, не замислюватися про те, що треба додати "and" у фільтр, який з першого став другим. Таким чином, ми страхуємо себе від виникнення можливої помилки.


4. Накладаємо фільтр


Отже, рядок фільтра була успішно сформована в строкової змінної sFilter. Як її застосувати до набору даних?


Нехай набір даних має ім'я DataSet. Як робиться зазвичай:

DataSet.Filter := sFilter;
if sFilter <> “” then DataSet.Filtered := true
else DataSet.Filtered := false;

Зробимо простіше:

DataSet.Filter := sFilter;
DataSet.Filtered := (sFilter <> “”);

Заощаджуємо при цьому і рядок коду, і розмір виконуваного модуля.

5. Усуваємо повторювані запити


Часто в збережених процедурах зустрічається наступна послідовність команд:


  1. запитом select count (*) as cnt підраховується кількість записів за деякими умовами
  2. якщо cnt більше 0 (тобто записи, що задовольняють умові, існують), то відкривається курсор для вибору цих записів (тобто той же самий select, тільки замість count (*) вказується список необхідних полів)

Виходить, що один і той самий запит виконується два рази – спершу для визначення наявності записів у базі даних, а потім для їх вибірки. Якщо в запиті присутньо багато таблиць, є обчислювані поля, накладаються складні умови в розділі where або присутній складна угруповання, виконання запиту може зайняти суттєвий час.


Прискорити виконання такого коду в два рази можна, позбувшись від першого запиту, що обчислює кількість записів:



  1. заводимо змінну-лічильник
  2. Обнуляємо лічильник
  3. відкриваємо курсор для вибору записів
  4. в тілі циклу, обробного запису, робимо збільшення лічильника на одиницю після обробки кожного запису
  5. закриваємо курсор
  6. перевіряємо: якщо лічильник дорівнює 0 (тобто записів, що задовольняють умові, не існують), виконуємо дії, необхідні при відсутності записів

Замість змінній-лічильника можна завести логічну змінну, якої присвоюється false перед циклом і true всередині циклу. Лічильник зручний тоді, коли після циклу потрібно знати кількість оброблених записів.


6. Об'єднуємо схожі запити


Нехай є два компоненти TDataSet із запитами:

select field1, field2 from table
select field2, field3, field4 from table

Їх можна об'єднати в один із загальним запитом:

select field1, field2, field3, field4 from table

Якщо ці два компоненти були розташовані на різних формах, то компонент із загальним запитом можна розташувати на формі або модулі даних (DataModule), використовуваному обома формами. Таким чином, економиться один компонент. Різниця в часі для вибірки декількох зайвих для кожного випадку полів несуттєва, особливо для таблиць з невеликою кількістю записів.


У реальних програмах запити бувають складні, з вибіркою з декількох таблиць. Такі запити теж можна об'єднувати, вписуючи в результуючий запит всі необхідні поля і таблиці. Головне, щоб умови вибірки були однаковими. Однак у цьому випадку не варто забувати про можливе зниження продуктивності за рахунок звернення до більшої кількості таблиць.


7. Індекси, індекси і ще раз індекси …


По полях, часто беруть участь у зв'язках таблиць, а також в умовах на вибірку, рекомендується будувати індекси. Використання індексів може істотно прискорити виконання запитів. Автору відомі випадки, коли запит, що виконувався 3 хвилини, після правильної побудови індексів став виконуватися 15 секунд.


Максимальна швидкодія досягається при використанні кластерних індексів, однак тут потрібно пам'ятати про два моменти:


  1. кластерний індекс у таблиці може бути тільки один, і звичайно він будується автоматично по ключовому полю (полями);
  2. запису в таблиці розташовуються в тому порядку, який задано кластерним індексом, що означає уповільнення операцій вставки даних у таблицю, особливо якщо в таблиці багато записів.

Крім того, потрібно відзначити наступне.


  1. Індекс не обов'язково повинен складатися з одного поля. Чомусь деякі розробники вважають, що індекси повинні бути однополевимі. Бували випадки, коли однополевие індекси не давали такого підвищення швидкодії, як дво-, а іноді навіть трехполевие.
  2. Буває, що сервер самостійно використовує для обробки запиту не найефективніший індекс. У таких випадках корисно явно вказати, який індекс треба використовувати.

За яких полях краще будувати індекси, скільки полів у них включати і які саме індекси повинен використовувати сервер зазвичай визначається експериментальним шляхом, виконуючи запит з новими індексами та аналізуючи дані про продуктивність окремих його частин. Інструменти аналізу в кожній СУБД свої. Зручно, коли процес виконання запиту представляється в графічному вигляді з зазначенням імен таблиць і використовуваних індексів, кількості вибраних записів, часу виконання, вартості виконання та інших даних.


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


8. Виключаємо умова із запиту


Нерідко виникає наступна задача. Є запит з умовою виду:

select *
from table
where field = :param

Потрібно зробити так, щоб при передачі певного значення параметра запиту (param) умова ігнорувалося.


Наприклад, є запит на вибір співробітників. Умова визначає, до якого підрозділу повинні ставитися співробітники, але є необхідність показати всіх співробітників по всіх підрозділах.


Вирішується таке завдання досить просто:

select *
from table
where :param in (field, 0)

Як працює така конструкція? Якщо параметру передати ненульове значення, будуть відібрані тільки ті записи, у яких значення поля одно зазначеному значенням. Якщо передати 0, умова буде проігноровано, і запит поверне всі записи.


Умов у частині where може бути декілька, при цьому ігнорується тільки те, до якого відноситься параметр. Тобто запит:

select *
from table
where :param in (field, 0) and IsActual <> 0

буде завжди повертати лише ті записи з ненульовим значенням поля IsAc-tual, незалежно від того, яке значення параметра param буде задано.

Виключаються умов у запиті може бути декілька:

select *
from table
where :param1 in (field1, 0) and
:param2 in (field2, 0) and
IsActual <> 0

При цьому можуть виключатися як будь-яке з них, так і обидва відразу.

Для строкових полів умова прийме вигляд:

where :param in (field, “”)

Недоліком такого підходу є те, що втрачається можливість фільтрувати набір даних за значенням, яке передається як значення для виключення умови (в прикладах – нуль або порожній рядок). Тому найзручніше застосування подібного методу – фільтрація по полях, пов'язаним з іншими таблицями. Як правило, нуль і негативні значення в якості ідентифікатора записів не використовуються, тому можна сміливо використовувати їх в якості значень для виключення умови.


9. Курсори – це довго


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


Тому коли мова йде про заповнення таблиці за результатами запиту, набагато більш швидким і ефективним рішенням є використання конструкцій типу insert-select, якщо результуючий набір даних повертається запитом або insert-exec, якщо результуючий набір даних повертається збереженої процедурою. Причому запит може містити як звичайні, так і обчислювані поля.


Правда, подібні конструкції існують не для всіх СУБД. Вони точно існують для SQL Server і Oracle, insert-select працює також у MS Ac-cess. Синтаксис подібних запитів тут описуватися не буде, тому зверніться до опису мови запитів Вашій СУБД.


10. Відновлюємо ідентифікатор в тригері


Однією з часто зустрічаються проблем є втрата значення ідентифікатора (identity) у тригері, який виконує додавання записів в інші таблиці.


Розглянь наступну ділянку коду збереженої процедури (у синтаксисі мови запитів MS SQL Server):

insert into table (field1, field2, field3)
values (1, 2, 3)

@id = @@IDENTITY

… деякі дії, де використовується значення змінної @ id …


На таблицю table встановлений тригер на додавання записів, який здійснює запис в іншу таблицю за допомогою оператора insert. При цьому відбувається наступна ситуація. Тригер спрацьовує ДО присвоєння @ @ IDENTITY в змінну. Insert з трігера псує значення глобальної змінної @ @ IDENTITY. Тому необхідно на початку тригера зберегти це значення, а перед виходом відновити.


Зробити це можна в такий спосіб.

ALTER TRIGGER [dbo].[trg_InsertUpdateTable]
ON [dbo].[Table]
FOR INSERT, UPDATE
AS

DECLARE
@Idt int,
@s varchar(255)

– Запам'ятаємо значення глобальної змінної @ @ Identity
SET @Idt = @@Identity

– Виконуємо дії тригера

INSERT (…) VALUES (…) – змінюється значення @ @ Identity

– Відновлюємо значення глобальної змінної @ @ Identity
IF @Idt IS NOT NULL
BEGIN
Set @s = “Select Identity(Int, ” +
Cast(@Idt As VarChar(10)) +” ,1) As idt Into #Tmp”
Execute(@s)
END
END


Сенс цієї конструкції наступний. Спочатку запам'ятовується значення глобальної змінної @ @ Identity в локальній змінної @ Idt. При завершенні тригера запам'ятоване значення з змінної @ Idt вставляється в тимчасову таблицю динамічним запитом за допомогою конструкції Select Into, яка змінює значення @ @ Identity. Оскільки вставляється тільки один запис, а @ Idt використовується як початкове значення поля-лічильника, то запис в тимчасовій таблиці отримує ідентифікатор, рівний @ Idt. @ @ Identity містить значення ідентифікатора останньої вставленої в будь-яку таблицю запису. У даному разі це і буде @ Idt, тобто раніше збережене значення. Тимчасова таблиця автоматично знищується при завершенні роботи тригера.


Перевірка @ Idt IS NOT NULL необхідна, якщо тригер спрацьовує на операції UPDATE або DELETE. У цьому випадку значення змінної @ @ Identity не визначено, і відновлювати його не потрібно.


11. Передаємо з'єднання з базою даних у бібліотеку


При розробці програми, що складається з основного виконуваного модуля (exe) і бібліотек, що підключаються (dll) рано чи пізно стикаєшся з таким завданням. В основному модулі проводиться підключення до бази даних. Необхідно виконати підключення до тій же базі даних з бібліотеки, але не відкриваючи друге з'єднання. Тобто постає завдання передачі з'єднання в бібліотеку.


Розглянемо можливий шлях вирішення на прикладі компонентів ADO.


У бібліотеці на одній з форм або модулів даних розташований об'єкт ADOConnectionLib типу TADOConnection.


Опишемо в бібліотеці глобальну змінну SaveConnectionObject типу _Connection і дві службові експортуються процедури:


1. Ініціалізація бібліотеки

procedure InitLibrary(var Con : TADOConnection);
begin
/ / Зберегти існуючий об'єкт
SaveConnectionObject := ADOConnectionLib.ConnectionObject;
/ / Замінити його на переданий об'єкт
ADOConnectionLib.ConnectionObject := Con.ConnectionObject;
/ / Інші дії з ініціалізації бібліотеки

end;

2. Вивантаження бібліотеки

procedure FreeLibrary;
begin
/ / Дії, що виконуються при вивантаженні бібліотеки

/ / Відновити вихідний об'єкт
ADOConnectionLib.ConnectionObject := SaveConnectionObject;
end;

Перша процедура викликається після підключення бібліотеки. Друга – перед відключенням від неї.


Висновок


Тут були розглянуті деякі прийоми, що полегшують і прискорюють Роботу з базами даних. Ви можете їх використовувати, можете не використовувати – право ваше. Головне – не бійтеся експериментувати. Не подобається, як виконується запит – спробуйте перебудувати його по-іншому. Змінити порядок з'єднання таблиць (іноді це допомагає), перебудувати індекси, перевірити список вибраних полів … У результаті у вас будуть виходити швидкодіючі запити і процедури, а також набереться свій власний набір методів, який допоможе вам у роботі.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*