Delphi, SQLite, Пинговалка.

Готовится к выходу обновленная версия программы “Пинговалка”. За время, прошедшее с момента выхода последней версии программы, было много исправлено, дописано, удалено кода. Причин для подготовки новой версии было как минимум две: обращение пользователей программы и вторая причина – я решил заняться рефакторингом проекта, в результате чего проект кое в каких местах “похудел” иногда даже значительно. Есть идея добавить в программу пару новых “фишек”, но когда дойдут руки до внедрения новых функций в программу пока не ясно. Пока могу лишь сказать, что в ближайшее время постараюсь выложить в доступ обновление “Пинговалки”. Если есть предложения по каким-либо новым функциям в программе, или заметили ошибки в работе о которых не сообщалось здесь – пишите. А пока поговорим немного о работе с SQLite в Delphi.

Некоторое время назад я уже писал про SQLite в Delphi. С того времени SQLite несколько раз обновлялся, а вместе с этим обновлялся и wrapper, который я использую для работы с SQLite, так что можете обновиться из репозитория и приступим к рассмотрению нескольких примеров использования SQLite.

Первое, на что следует обратить внимание – это на те примеры, которые можно в большом достатке увидеть в Сети по части работы с SQLite, ну, например, на такой (пример работы с SQLite в PHP):

if (!$query_table) exit("Невозможно создать таблицу в базе данных!");
  // Запишем что-нибудь в таблицу
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('PHP5+', 'Apache');");
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('SQLite – ', 'классная вещь');");
  sqlite_query($db, "INSERT INTO table1(field1, field2) VALUES ('Посетите ', 'sqlite.org');");
  // Сделаем выборку данных
  $res = sqlite_query($db, "SELECT * FROM table1;");

Что здесь, собственно, не так? На первый взгляд пример составлен правильно, да и если проверите – результат проверки будет положительным. Но есть одно большое “НО” – НО так делать не надо :). Действительно, можете проверить работу приведенного выше примера хоть в PHP, хоть в C#, хоть в Delphi (не суть) – скорость работы с БД оставит желать лучшего.

Приведу простой пример на Delphi – добавление 100 записей в таблицу:

unit main;
 
interface
 
uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, SQLite3, SQLiteTable3, StdCtrls;
 
const SQLTable = 'CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, strfield TEXT, intfield INTEGER)';
      SQLInsert = 'INSERT INTO test (strfield, intfield) VALUES ("%s", %s)';
 
type
  TfMain = class(TForm)
    Button1: TButton;
    Label1: TLabel;
    lbTime: TLabel;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;
 
var
  fMain: TfMain;
 
implementation
 
{$R *.dfm}
 
procedure TfMain.Button1Click(Sender: TObject);
var Base: TSQLiteDatabase;
    iCounterPerSec: TLargeInteger;
    T1, T2: TLargeInteger; //значение счётчика ДО и ПОСЛЕ операции
    i:integer;
begin
  Base:=TSQLiteDatabase.Create('base.sqlite');
  try
  if not Base.TableExists('test') then //таблица в БД отсутствует - создаем
    Base.ExecSQL(SQLTable);
 
    QueryPerformanceFrequency(iCounterPerSec);
    QueryPerformanceCounter(T1);
 
    for I := 1 to 100 do
       Base.ExecSQL(Format(SQLInsert,['AnyText',IntToStr(i)]));
 
    QueryPerformanceCounter(T2);
    lbTime.Caption:=FormatFloat('0.0000', (T2 - T1) / iCounterPerSec) + ' сек.';
 
  finally
    base.Free;
  end;
end;
 
end.

Смотрим на время, которое потребовалось, чтобы вписать в таблицу 100 значений:

Жесть да и только – 8 с лишним секунд на 100 записей, а что если надо записать не 100, а 1000 записей? Вот Вам и ответ, почему приведенный выше код PHP я считаю недостаточно корректным – идея верная, а реализация на поверку оказалась фиговая. И я, следуя вот таким примерам “наколбасил” в “Пинговалке” много чего нехорошего в плане быстродействия. А все потому, что не достаточно внимательно читал документацию по SQLite. Теперь ускорим работу нашего приложения, следуя официальным рекомендациям. Допишем в процедуру обработчик две строки:

Base.BeginTransaction;//начали транзакцию
for I := 1 to 100 do
   Base.ExecSQL(Format(SQLInsert,['AnyText',IntToStr(i)]));
Base.Commit;//завершли

Смотрим результат:

Неплохо ускорились, да? А причина столь стремительного роста скорости объясняется все в той же официальной документации – здесь.

Теперь второй момент, который следует учитывать при работе с SQLite в Delphi (и не только). Смотрим на размер файла БД:

Попробуем удалить из нашей таблицы все записи:

 Base.ExecSQL('DELETE FROM test');

Вроде бы удалили все записи и SELECT тоже вернет нам пустую таблицу, а размер файла не изменяется. Ответ на вопрос “Ошибка ли это?” я нашел на одном из сайтов в рунете. Следуя изложенному материалы у нас для решения вопроса с размером файла базы данных есть два варианта:

1. Включить auto_vacuum, если у нас версия SQLite 3.1. или выше
2. Самостоятельно вызывать команду VACUUM в нужный момент для “сжатия” файла базы данных

Реализация в Delphi:

Base.ExecSQL('PRAGMA auto_vacuum = 1');
Base.ExecSQL('VACUUM');

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

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

Вообще тема ускорения работы SQLite довольно занимательная и ей уделено много статей как в Рунете так и по Сети в целом, есть что почитать и чему поучиться у профессионалов, использующих SQLite. Теперь вот сижу, дописываю wrapper для работы с SQLite в Delphi, исправляю кое-что в “Пинговалке” и, надеюсь, что следующий релиз программы будет работать значительно быстрее предыдущего, а вы сможете с помощью Пинговалки ускорять индексацию своих сайтов ещё удобнее :). До новых встреч»!

Книжная полка

	Автор: Андрей Шкрыль Название: Разработка клиент-серверных приложений в Delphi Описание: Рассмотрены практические вопросы по разработке клиент-серверных приложений в среде Delphi 7 и Delphi 2005 с использованием СУБД MS SQL Server 2000, InterBase и Firebird. Приведена информация о теории построения реляционных баз данных и языке SQL. Освещены вопросы эксплуатации и администрирования СУБД.
	Автор: Антон Григорьев Название: О чем не пишут в книгах по Delphi Описание: Рассмотрены малоосвещенные вопросы программирования в Delphi. Описаны методы интеграции VCL и API. Показаны внутренние механизмы VCL и приведены примеры вмешательства в эти механизмы. Рассмотрено использование сокетов в Delphi: различные режимы их работы, особенности для протоколов TCP и UDP и др.