SQLite3 or Embedded Firebird?

Roberto Schneiders · 2013-07-22 12:57:00

I need to decide which of these embedded databases use in an point of sale application. It's an important decision because it will affect 5000 users.

From what I've seen the Firebird is faster (http://ea.tl/embeddeddb.shtml). But what really worries me is support for multithreaded and data corruption.

What is your experience about it? Which one would you choose?

1. Can I use the set mORMot + SQLite3 with multiple threads?

I ask this because through the documentation I could not identify what, exactly, is thread-safe and what is not.

SQLite3 documentation: ".. in order to be thread-safe, SQLite must be compiled with the SQLITE_THREADSAFE preprocessor macro set to 1"
mORMot documentation: "SQLite3 source code was compiled without thread mutex: the caller has to be thread-safe " / "low level sqlite3_ aware * () functions are not thread-safe" / "SQLITE_THREADSAFE 0 in sqlite.c"

Is there any example of multiple threads with a mORMot client?

ab · 2013-07-22 14:25:37

In mORMot, the SQLite3 kernel is protected with a global lock, when it accesses the engine and tries to retrieve the content from its internal result cache.
By definition, SQlite3 is a "locking database", so using a critical section does make sense to ensure it will work safely.
Accessing the same database from multiple threads may in fact make the process slower.
It also ensure that our global caching mechanisms (of both results and statements) are working as expected.
See http://synopse.info/forum/viewtopic.php?pid=571#p571

Most of the time won't be spent within SQLite3, but in the HTTP process itself, which will be multi-threaded.

About performance, your link is somewhat wrong, AFAIK, for two reasons:
1. Assumption that Firebird waits for disk synchronization is not true;
2. Statements are not prepared.

1. Assumption that Firebird waits for disk synchronization is not true

In fact, FireBird works as if it was compiled with PRAGMA synchronous = off, to follow the SQlite3 implementation.
SQLite3 is MUCH SAFER than FireBird by default.

Setting PRAGMA synchronous = off is as safe as FireBird process, and much faster.

You can even not set PRAGMA synchronous = off, but set the PRAGMA locking_mode=EXCLUSIVE directive.
When used within mORMot, you will get tremendous speed, and still have very good ACID behavior.
See http://blog.synopse.info/post/2013/06/1 … cking-mode

2. Statements are not prepared for SQLite3

In this test, Sqlite3 statements are not prepared.

Within mORMot, all statements are prepared and cached.
Then all ?,?,?,... parameters are bound on the fly, without parsing the SQL.

Conclusion

Note also that those tests were made in .Net, with several layers above the DB clients, and garbage collector use.
mORMot won't be affected by those.

For use within mORMot as embedded database, SQLite3 is IMHO the best option, about both speed and safety, IMHO.
When you take a look at http://blog.synopse.info/post/2013/06/1 … for-mORMot I suspect you will be convinced.

Roberto Schneiders · 2013-07-22 14:43:14

thank you very much!

I have a problem. What am I doing wrong?

I have this code (Using OmniThread Library):

procedure TForm1.FormCreate(Sender: TObject);
begin
  Model := CreateSampleModel;
  Database := TSQLRestServerDB.Create(Model, ChangeFileExt(paramstr(0),'.db3'));
  TSQLRestServerDB(Database).CreateMissingTables(0);
end;

procedure TForm1.AddValues;
var Rec: TSQLSampleRecord;
  i: Integer;
begin
  for i:=0 to 100 do
  begin
      Rec := TSQLSampleRecord.Create;
    try
      Rec.Name := GetCurrentThreadId().ToString + GetCurrentTime.ToString + i.Tostring;
      Rec.Question := StringToUTF8('TESTE - '+GetCurrentThreadId().ToString + GetCurrentTime.ToString + ' Loop:'+i.ToString);
      if Database.Add(Rec,true)=0 then
        raise exception.create('Error adding the data');
    finally
      Rec.Free;
    end;
  end;
end;

procedure TForm1.BtnParallelClick(Sender: TObject);
var
  i: Integer;
begin
  for i := 0 to 20 do
    Parallel.Async(AddValues);
end;

Sometimes when running BtnParallelClick I encounter this error: ESQLite3Exception with message 'database is locked';

ab · 2013-07-22 15:02:24

I suppose you will need to use a TSQLRestClientDB instance instead of a TSQLRestServerDB.

But I do not understand why you got this error.
If you take a look at TSQLRestServerDB.EngineExecute(), you will find out that every data access is protected via DB.Lock..DB.Unlock so should be thread-safe.
Perhaps you reached the issued described at http://delphitools.info/2011/11/30/fixi … alsection/ but as far as I understand it, this is a performance issue, not a not-working issue.

What is the stack trace when the error occurs?
Is it really within the Add() method?

Perhaps OmniThreadLibrary is more advanced that I expect.
Could you try with regular TThread.Execute?

In fact, TSQLRestServerDB gives a direct access to the database, whereas TSQLRestClientDB has additional protections (and features, as being able to be replace with a remote TSQLRestClientURI in the future).

Roberto Schneiders · 2013-07-22 17:06:05

I think I know what caused the problem. During the test, the database was loaded in SQLite Expert. I did not realize that. My mistake. Apparently it's working as expected with SQLRestServerDB.

So, I should use the TSQLRestClientDB?

I tried this way:

Database := TSQLRestClientDB.Create(Model, Model, ChangeFileExt(paramstr(0),'.db3'), TSQLRestServerDB);
TSQLRestClientDB(Database).Server.CreateMissingTables(0);

but...sometimes this error occurs:
{"ErrorCode":408,"ErrorText":"Invalid Request"}

ab · 2013-07-25 12:38:26

We have just added a new set of regression tests.
The new TTestMultiThreadProcess class is intended for multi-threaded stress test: it will test all direct or remote access protocols with a growing number of concurrent clients (1,2,5,10,30,50 concurrent threads), to ensure stability, scalibility and safety of the framework in high concurrent mode.
See http://synopse.info/fossil/info/95aef53bc2

Thanks to those tests, some small fixes have been committed.
Including logging and also ORM access.
See http://synopse.info/fossil/timeline

Hope it will fix your problem.

Roberto Schneiders · 2013-07-25 13:28:19

Thank you Arnaud!

Roberto Schneiders · 2013-07-29 18:31:19

Today I ran the same software again and got some new errors. I have the latest mORMot version (updated-to: 21a1dd69da94b5736f090f533681abeefcb8460b 2013-07-29 16:18:35 UTC)

When running the test with 4 threads i'm getting this message:
{
"ErrorCode": 408
"ErrorText": "Request Timeout"
}

ab · 2013-07-29 20:14:18

Because your requests are not spread equaly.

Add a Sleep(0) in the loop.

Try sample 21 and you will find out.

mORMot Open Source

#1 2013-07-22 12:57:00

SQLite3 or Embedded Firebird?

#2 2013-07-22 14:25:37

Re: SQLite3 or Embedded Firebird?

#3 2013-07-22 14:43:14

Re: SQLite3 or Embedded Firebird?

#4 2013-07-22 15:02:24

Re: SQLite3 or Embedded Firebird?

#5 2013-07-22 17:06:05

Re: SQLite3 or Embedded Firebird?

#6 2013-07-25 12:38:26

Re: SQLite3 or Embedded Firebird?

#7 2013-07-25 13:28:19

Re: SQLite3 or Embedded Firebird?

#8 2013-07-29 18:31:19

Re: SQLite3 or Embedded Firebird?

#9 2013-07-29 20:14:18

Re: SQLite3 or Embedded Firebird?

Board footer