Performance problem with Firebird

Roberto Schneiders · 2013-01-21 19:44:20

I'm doing a some basics performance tests. My first test is basically insert 10.000 records.

I tested using ClientDataSet (DBExpress), Aurelius and mORMot.

I expected the ORM's were slower than DBExpress in this test. This is confirmed. But I did not expect so weak performance of mORMot (Usually the mORMot destroys its competitors in these tests ).

I must have done something wrong or there must be some problem (perhaps related to ODBC).

This is the code I use.

var
  oPeople: TPeople;
  i:Integer;
  results: TIntegerDynArray;
  StopWatch:TStopWatch;
begin
  StopWatch := TStopwatch.StartNew;
  StopWatch.start;

  fClient.BatchStart(TPeople);
  fclient.TransactionBegin(TPeople);
  for i:=0 to 9999 do
  begin
    oPeople := TPeople.Create;
    oPeople.Name := 'People '+IntToStr(i);
    oPeople.Age  := i;
    fClient.BatchAdd(oPeople, true);
    oPeople.free;

    if (i mod 1000) = 0 then
    begin
      fClient.BatchSend(results);
      fClient.BatchStart(TPeople);
    end;
  end;
  fClient.BatchSend(results);
  fClient.Commit();
  StopWatch.Stop;
  showmessage(IntTOStr(StopWatch.ElapsedMilliseconds));
end;

I read this topic
http://synopse.info/forum/viewtopic.php?id=769
I tried to use transactions and BATCH, but none brought me gain than I expected.

DBExpress: 12.1 s
Aurelius: 44.3 s (Not exactly the same table as used in other tests, but is similar. I'll do more tests tomorrow.)
mORMot: 140s (Using BATCH and Transactions)

NOTE: All tests are using ODBC.

ab · 2013-01-21 21:52:10

What is slow here is the ODBC layer, I suspect.
You are perfectly right.
When we tested it with Oracle, we found out the ODBC layer to be much slower than direct Oracle access.

Such inserts performs at 50,000 per second with Oracle, and more than 30,000 with SQLite3 (within a transaction).
On a Core2 duo basic configuration.

So don't trust those results: what is slow here is not mORMot nor its ORM, it is the ODBC layer.
When we will have direct FireBird access, I'm quite confident it will be much faster than DBExpress.

Roberto Schneiders · 2013-01-22 10:24:42

I agree. But the tests I did with DBExpress and Aurelius also used ODBC.

Is not a problem with the mORMot ODBC?

If I find something I notice.

ab · 2013-01-22 10:58:43

I think it is perhaps because the statements are not cached.

A statement is created at each INSERT.

I'm currently adding such a cache to SynDB units.

Up to now, only SQlite3 statements are cached.

With direct Oracle access, there is no issue when recreating statements, since Oracle Client is smart enough to do the cache on the client side by itself.
But this features may certainly be lacking with ODBC.

ab · 2013-01-22 20:46:52

I've just added SQL statement client-side cache in ODBC (just as it was available for SQlite3).
Still need to implement it for OleDB.

And also implemented server-side statement cache for Oracle.
http://docs.oracle.com/cd/B28359_01/app … tm#i471377

This is a MAJOR performance boost for individual INSERT or SELECT, for instance.
See http://synopse.info/fossil/info/0827a73762

See also http://synopse.info/fossil/info/17141351ca which makes a huge performance boost when external tables are used.
When inserting individual data rows in an external table, the last inserted IDs are maintained in memory instead of executing "select max(id)" - added new property EngineAddUseSelectMaxID to unset this optimization.

Overall, I observed from x2 to x8 performance boost with simple Add() operations, using ODBC and direct Oracle access.

Could you retry your performance tests with this commit?

Roberto Schneiders · 2013-01-23 10:41:58

Amazing.

I do not know what to say. Simply Fantastic.

DBExpress: 5,7s
Aurelius: 28,4s
mORMot: 1,3s

I would like to test with this setting EngineAddUseSelectMaxID. I do not know how. this property is in TSQLRestServerStaticExternal class. How do I access this object from my client (TSQLRestClientDB)?

Thank you very much.

ab · 2013-01-23 12:36:51

You can do it only on the server side.

So something like Client.Server.StaticVirtualTable[TPeople] as TSQLRestServerStaticExternal

Roberto Schneiders · 2013-01-23 17:16:08

With this property enables mORMot took 31.5s.

Last edited by Roberto Schneiders (2013-01-23 19:18:12)

ab · 2013-01-23 19:06:35

Roberto Schneiders wrote:

With this propreidade enables mORMot took 31.5s.

So FireBird is very slow at preparing a statement (at least with the ODBC connector).
SQL statement cache does make sense here.

With Oracle, I found it less problematical: the speed gain is about 2x at most.

I'm currently working on the native direct Firebird access.

Roberto Schneiders · 2013-01-23 19:21:02

okay.

When the driver is ready I can help you on the tests.

ab · 2013-01-23 20:24:15

Thanks.

Now it creates or attaches to a database file.

There is a specific connection properties for Firebird Embedded.

ab · 2013-01-25 14:55:01

Perhaps we reached this known "limitation" of Firebird:
http://www.firebirdfaq.org/faq205/

The first implementation did (ab)use of SELECT MAX(ID) FROM TABLE - which is told to be slow according to this faq article.

New implementation indeed makes the system fly.

ab · 2013-01-28 07:19:41

I wrote a blog article about those speed performance.
See http://blog.synopse.info/post/2013/01/2 … provements

Thanks for the feeback!

TPrami · 2013-01-28 08:54:09

Hello,

In Firebird you need separate index for the Max() statement... Descending I think... (Easy to find with Google which I am too lazy to do now)... In Firebird indexes are Unidirectional (Maybe 3.0 can change this, not sure tough)

If you add the Extra index for the ID field then it should speed up dramatically...

-Tee-

ab · 2013-01-28 09:45:19

This is the case - speed increase is amazing for Firebird.
http://synopse.info/forum/viewtopic.php?pid=6380#p6380
From 31 seconds to 1 second.

No need to search the Internet for the reference - just look one message up here - I posted the link.
http://synopse.info/forum/viewtopic.php?pid=6413#p6413

TPrami · 2013-01-28 10:43:10

OK,

It had info how to go around it, did not see it

I was just thinking that this is good to know, because sometimes caching the ID:s in the engine level is not good idea (If there are some other thingys accessing the DB, and you can't control that)...

I had system that looked up the pk values and so at the startup and it tool so long without the proper index... (There was pretty big tables)...

-Tee-

mORMot Open Source

#1 2013-01-21 19:44:20

Performance problem with Firebird

#2 2013-01-21 21:52:10

Re: Performance problem with Firebird

#3 2013-01-22 10:24:42

Re: Performance problem with Firebird

#4 2013-01-22 10:58:43

Re: Performance problem with Firebird

#5 2013-01-22 20:46:52

Re: Performance problem with Firebird

#6 2013-01-23 10:41:58

Re: Performance problem with Firebird

#7 2013-01-23 12:36:51

Re: Performance problem with Firebird

#8 2013-01-23 17:16:08

Re: Performance problem with Firebird

#9 2013-01-23 19:06:35

Re: Performance problem with Firebird

#10 2013-01-23 19:21:02

Re: Performance problem with Firebird

#11 2013-01-23 20:24:15

Re: Performance problem with Firebird

#12 2013-01-25 14:55:01

Re: Performance problem with Firebird

#13 2013-01-28 07:19:41

Re: Performance problem with Firebird

#14 2013-01-28 08:54:09

Re: Performance problem with Firebird

#15 2013-01-28 09:45:19

Re: Performance problem with Firebird

#16 2013-01-28 10:43:10

Re: Performance problem with Firebird

Board footer