mORMot Open Source

xiwuping

Member

Registered: 2018-02-11

Posts: 32

TSQLDBUniDACConnectionProperties NOT used in an optimal way by mORMot?

I run the Samples 15-External DB performance, and I got the following results:
Insertion speed
   
                        Direct        Batch        Trans         Batch Trans
FireDAC SQlite3    22468       42605         53038       168953
UniDAC  SQlite3    401          8744           33784       45844

I am very upset with the poor performance of UniDAC, hence I contacted Devart. Here is their response:

"Note that unlike FireDAC, our components use by default the same settings as the client library sqlite3.dll when working with SQLite. Therefore, it’s necessary that you set the same values for the Locking_Mode and Synchronous parameters as for FireDAC:

  UniConnection.ExecSQL('PRAGMA locking_mode=EXCLUSIVE');
  UniConnection.ExecSQL('PRAGMA synchronous=0');

We are not able to identify the settings and function invocation order in the mORMot framework."

They also sent me their own sample code comparing UniDAC versus FireDAC, and the results are TOTALLY different:

UniDAC : 2.672 sec, INSERT count = 100000
FireDAC: 2.735 sec, INSERT count = 100000

UniDAC : 2.562 sec,  BATCH INSERT count = 1000000
FireDAC: 21.297 sec, BATCH INSERT count = 1000000

My feelings are that mORMot probably does not use UniDAC in an optimal way, which might not be fair for UniDAC users that wishes to use mORMot.  Any thoughts?  The the sample code that Devart sent back to me can be downlaode from this gist address: https://gist.github.com/wuxixigit/f32d2 … 8812e337a8

Thanks.

Last edited by xiwuping (2019-05-26 22:55:19)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: TSQLDBUniDACConnectionProperties NOT used in an optimal way by mORMot?

1. Please don't post huge source code in the forum, as stated by https://synopse.info/forum/misc.php?action=rules

2. UniDAC support is right: FireDAC is somewhat cheating with the default settings of SQlite3.
See http://blog.synopse.info/post/2013/06/1 … for-mORMot
and https://synopse.info/files/html/Synopse … #TITLE_139

3. Don't use FireDac or UniDAC with SQLite3 databases, but our mORMotSQLite3 connection, which is the best/faster approach.
Using "external" tables has an uneeded overhead.

xiwuping

Member

Registered: 2018-02-11

Posts: 32

Re: TSQLDBUniDACConnectionProperties NOT used in an optimal way by mORMot?

Please don't post huge source code in the forum, as stated by https://synopse.info/forum/misc.php?action=rules

Thank you for the heads up.  I edited the post and removed the source code.   The source code is now posted at gist.