How to improve INSERT performance?

I have the following entity, and I found event with "cached update", inserting 10000 records still takes 15 seconds (postgresql server), which is unacceptable for my realtime application.

mORMot is x200 times faster than Aurelius with its Batch mode (the same server).

  [Entity, Automapping]
  [Id('FId', TIdGenerator.None)]
  TCustomer = class
  strict private
    FId: Integer;
    FName: string;
    FCity: string;
  public
    property Id: Integer read FId write FId;
    property Name: string read FName write FName;
    property City: string read FCity write FCity;
  end;

Performance issues always have to be measured. There might be several specific reasons for that. I'd appreciate if you could send me a minimal sample project reproducing the problem. Then we will profile and let you know the specific reasons for that.

@wlandgraf
Thanks a lot for the kind support and help.

The test project adapted the Demo "Cached Updates" to insert 10000 entities. It can be downloaded from this link

https://www.sugarsync.com/pf/D6147121_09656321_200552

Thank you again. Your help and support is greatly appreciated!

Thank you. You are using ZeosLib. According to the documentation, Aurelius uses real batch update only with direct driver, FireDAC and UniDAC. When using ZeosLib, it simply simulates the batch statement but inserts each record individually.

When using FireDAC, for example, the time for inserting less than half the time when using ZeosLib. Note that we are right now heavily refactoring and optimizing it. TMS Aurelius 5 will come with great performance improvement and lots of new features thanks to that.

Thank you for the heads up.

I choose ZeosLib, mainly due to my benchmarking results (using mORMot) : ZeosLib > FireDAC > UniDAC. ZeosLib has amazing performance indeed (see below)

I don't expect Aurelius to have the same performance as mORMot. Aurelius ORM design better suits my needs than mORMot, with its ease of use and learn. I do expect Aurelius to have a balanced performance versus ORM ease of use.

  • That being said, it would be great if Aurelius can give some priority to ZeosLib, and optimize its ZeosLib-based batch insert. ZeosLib has better performance than FireDAC, overall.

2020-09-07_10-03-03

Would you consider improving the Aurelius/ZeosLib batch performance in Aurelius 5?

P.S. What is the expected/estimated release date for Aurelius 5?

AFAIK these benchmarks for ZEOS are using Zeos DBC 'drivers' so they do not deal with TDataSet stuff, while ZeosLib driver for Aurelius do use de TDataSet descendants.

I've created a simple Aurelius driver using ZDBC (based on TMS Aurelius ZeosLib driver) that I have tested only with Firebird (but never did a benchmark of it). It may need some twiks since I do not fully understand Zeos transaction handling (witch I find very confusing). But it mostly works fine for me.

It does not do any batch optimization since Firebird (until v.3) do not support batch inserts (and I use Firebird only), so it will execute one insert at a time, but on a prepared statement.

Attached if you find useful:

Aurelius.Drivers.ZeosDbc.pas (10.8 KB)

2 Likes

@Farias_Anderson
Thank you very much for the kind sharing.

I re-run the test, using your new driver ZeosDbc, and it cut the total insertion time of 10000 entities, from previous 15 second to 5 second.

  • The 5 second total insertion time is comparable to FireDAC running batch
  • But consider Aurelius uses FireDAC in real batch mode, while using Zeos in the simulated batch mode
    • I would expect that ---- if running in real batch model, Zeos would accomplish much much faster speed than FireDAC, as manifested in the mORMot benchmarking

Hope Aurelius 5 could really do something about Zeos, so we have an ORM with great ease of use, and a blazing performance at the same time. @wlandgraf Please consider.......

2 Likes

@Farias Anderson,
@Wuping Xin,

Please raise this topic on the ZEOS forum.
https://zeoslib.sourceforge.io/

Michal

It's our intention to always improve Aurelius, of course. Support for ZDBC is something that worth investigating, for sure.

Note that we also have the intention to provide our own drivers, which will have, theoretically, even better performance, since we can tailor them specifically for Aurelius needs. SQLite and SQL Server drivers are already available, and Postgres will be the next, even though we are moving forward slower than we would like (mainly because we are also focusing on other features and improvements).

Thank you, really looking forward to the dedicated postgresql driver. We used to use MySQL but abandon it due to its tricky licensing terms for commercial applications. Postgresql is really good!

And, I know, it is politically incorrect to ask a software developer for an estimated release date of these new features, so I just hush-up and hope they will be available soon

1 Like

Will native postgres driver be included in Aurelius 5? Or any estimated release time?

(BULK INSERT) Performance with Aurelius seems a problem. For example it takes 5~6 seconds to insert 10000 records (with Cache Update On). It is shocking to find out that for the same amount of records, mORMot takes less than 0.05 second.

We don't have a timeframe yet.

Inserting 10000 records is not that a common task with an ORM. mORMot is widely known to be focused on performance. That's not much shocking. In any case, we are constantly improving Aurelius performance, Postgres driver will come, but mainly performance improvements will come very soon. Actually the very next version to be released very soon is already almost twice faster than the current one when it comes recording inserting and updating.

2 Likes

Understand. Looking forward to the next release. Thanks a lot for your focus and attention to user needs.

1 Like