Hello,
I am trying to convert a lot of data from an old file
based database to postgresql with Aurelius and Firedac in Delphi Berlin
10.1.
Our datamodel has multiple levels so I use a lot of cascading when saving the data to the database.
Since this is very slow I tried to use a transaction but it does not make it any faster.
Do transactions work with cascading?
Alex
Transactions are not related with cascading. Transaction is just a database transaction. About cascading, you can simply disable it in your mapping. Another useful approach is if you use Flush, avoid having lots of objects in the manager. In each Flush operation Aurelius will iterate through all objects to check for changes. You can use Flush(SingleObject) instead.
Maybe an example can clarify what I try to accomplish.
I am writing a conversion routine which has to convert tables with possibly more then 100.000 records each.
For example a simplified part of our datamodel consist of a table with orders and a table with orderlines,
First
I fill the objectmanager with 10.000 orders and 100.000 orderlines. The
orderlines are in the lists (ManyValuedAssociation) of the orders .
As
far as I know I have to save every order (I hoped that using a
transaction might speed up this proces), If I save the order the
orderlines are also saved (cascade).
This works but is very slow.
How can I make this more efficient, but still use the Aurelius objectmodel.
Using flush is not an option because these are all inserts and not updates, or am I missing something?
But don't you want to save the orderlines as well? Is Aurelius doing more inserts than necessary, or is it just that the inserts are slow? Indeed there is no batch mode yet, so the inserts will probably not be faster then they are. If you don't want to save orders or order lines, than you could rebuilt your model so that the associations are just in the orderline class.
I want to save orders and orderlines.
I want to know if there is a way to make this faster, indeed something like batchmode.
I hoped that doing everything in one transaction would trigger something like a batchmode.
Is there another way of doing this faster with help of the Aurelius objectmodel?
Unfortunately no, currently. We have in our backlog to implement such batch modes.
Any idea when you will be implementing batch modes?
There is
something else that does not seem to work as efficient as possible. In
the example with the orders and the orderlines, If I look at the SQL
statements, I see that first the orders and orderlines are inserted, but
after that there is an update of every orderline with the orderid, this
is not needed because the orderid is already available at the insert.
Or am I doing something wrong?
Nothing wrong, that's how it is for now. One of the things we want to implement is a stateless manager so all that process will be simplified and you can fine tune better what db operations will be performed. Unfortunately we're still finishing Linux support for most TMS Business products, when it's finished we can try to investigate the batch mode implementation.