Несколько приемов работы с базами данных

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

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

1. Выбираем из базы данных только те поля, которые нам нужны

Запросы вида:

select * from ...

могут очень сильно нагрузить как сервер, так и компьютер пользователя, особенно если таблицы содержат большие символьные или двоичные поля. Например, зачем выбирать поле с фотографией сотрудников, когда нужны только фамилии и дни рождений? Если в дальнейшем понадобится отображать и фотографии, это все равно потребует переработки программы, добавления новых компонентов на форму и т.д. Тогда же можно будет добавить дополнительное поле в запрос.

Поэтому, если полей много или они большие, то в select перечисляем только те, которые нам необходимы именно сейчас. Это не только уменьшит нагрузку на компьютеры, но и значительно уменьшит сетевой трафик.

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

2. Хранимые процедуры предпочтительнее процедур, реализованных в коде программы

И вот по каким причинам.

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

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

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

В-четвертых, вызов хранимой процедуры создает гораздо меньший сетевой трафик на сервер, чем передача текстов больших запросов.

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

Однако сильно увлекаться переносом запросов в хранимые процедуры тоже не стоит. Нет смысла, к примеру, переносить простые запросы из одной-двух-трех таблиц. Здесь уже действует принцип разумности. Если у вас просто запрос, лучше оставить его запросом. А если в запросе есть промежуточная обработка выбираемых данных, то выносим его в процедуру.

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

3. Строим фильтр

Предположим, что у вас есть набор данных, содержащий результат выполнения запроса или хранимой процедуры. Нужно выбрать некоторое подмножество записей из этого набора данных, отвечающих нескольким условиям. Условия задаются установкой соответствующих значений различных компонентов. Задача: построить строку фильтра вида: условие1 И условие2 И ...

Пример построим для трех полей:

числовое; имя поля NumericField; значение берется из SpinEdit, причем фильтрация происходит только в случае указания положительных значений
текстовое; имя поля StringField; берется из Edit, фильтрация производится в случае, если указана не пустая строка
ссылка из классификатора; имя поля 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. Устраняем повторяющиеся запросы

Часто в хранимых процедурах встречается следующая последовательность команд:

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

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

Ускорить выполнение такого кода в два раза можно, избавившись от первого запроса, вычисляющего количество записей:

заводим переменную-счетчик
обнуляем счетчик
открываем курсор для выбора записей
в теле цикла, обрабатывающего записи, делаем увеличение счетчика на единицу после обработки каждой записи
закрываем курсор
проверяем: если счетчик равен 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 секунд.

Максимальное быстродействие достигается при использовании кластерных индексов, однако здесь нужно помнить о двух моментах:

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

Кроме того, нужно отметить следующее.

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

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

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

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;

Первая процедура вызывается после подключения библиотеки. Вторая - перед отключением от нее.

Заключение

Здесь были рассмотрены некоторые приемы, облегчающие и ускоряющие Работу с базами данных. Вы можете их использовать, можете не использовать - право ваше. Главное - не бойтесь экспериментировать. Не нравится, как выполняется запрос - попробуйте перестроить его по-другому. Изменить порядок соединения таблиц (иногда это помогает), перестроить индексы, проверить список выбираемых полей... В итоге у вас будут получаться быстродействующие запросы и процедуры, а также наберется свой собственный набор методов, который поможет вам в работе.