BlogAll Blog Posts | Next Post | Previous Post
Cached Updates in TMS Aurelius, Batch (Bulk) Update in TMS RemoteDBBookmarks:
Thursday, April 9, 2020
Two new features in TMS Business: TMS Aurelius Cached Updates and TMS RemoteDB Batch/Bulk Updates
Photo by Arshad Pooloo on Unsplash
We have new releases in TMS Business world! TMS Aurelius 4.12 and TMS RemoteDB 2.5 have just been released, each with a very nice feature:
TMS Aurelius Cached Updates
When persisting objects by calling object manager methods like Save, Flush or Remove, the manager will immediately perform SQL statements to reflect such changes in the database. For example, calling this:
Will immediately execute the following SQL statement in the database:
With cached updates feature, you have the option to defer the execution of all SQL statements to a later time, when you call ApplyUpdates method. This is enabled by setting CachedUpdates to true. Take the following code as an example:
DELETE FROM CUSTOMERS WHERE ID = :id
The above code should perform the following SQL statements:
Manager.CachedUpdates := True; Manager.Save(Customer); Invoice.Status := isPaid; Manager.Flush(Invoice); WrongCity := Manager.Find
(5); Manager.Remove(City); Manager.ApplyUpdates;
- INSERT (to save the customer - see "exceptions" below);
- UPDATE (to modify the status field of the invoice);
- DELETE (to delete the city)
You can check this new feature yourself in the new Cached Updates demo which is included in TMS Aurelius install. Here is a screenshot:
TMS RemoteDB Batch/Bulk Updates (Array DML)
In RemoteDB, if you want to insert, update or delete several records at the same time, using the same SQL statement, you can now use the batch update feature - also known as Array DML.
In this mode, a single SQL statement is sent to the server, and multiple values are passed for each parameter. For example, consider the following SQL statement:
If you want to insert three records using the same statement, this is how you should do it:
XDataset1.SQL.Text := 'INSERT INTO Cities (Id, Name) VALUES (:Id, :Name)';
The advantage of this approach is that a single HTTP request containing the SQL statement and all parameters will be send to the server. This increases performance, especially on environments with high latency.
XDataset1.ParamByName('Id').DataType := ftInteger; XDataset1.ParamByName('Name').DataType := ftString; XDataset1.Params.ArraySize := 3; XDataset1.ParamByName('Id').Values := 1; XDataset1.ParamByName('Name').Values := 'London'; XDataset1.ParamByName('Id').Values := 2; XDataset1.ParamByName('Name').Values := 'New York'; XDataset1.ParamByName('Id').Values := 3; XDataset1.ParamByName('Name').Values := 'Rio de Janeiro'; XDataset1.Execute;
In addition to that, if the database-access component you are using server-side supports Array DML (like FireDAC or UniDAC), then it will also increase performance server-side significantly, by also using Array DML to actually save data in the database. Otherwise, a batch update will be simulated, by preparing the SQL statement and executing it for each row of parameters.
You can try TMS Aurelius, TMS RemoteDB, and all the other TMS Business products right now! Download the trial version, watch tutorial videos and many more from the TMS Business main page.
This blog post has received 5 comments.
2. Friday, April 17, 2020 at 9:37:20 PM This is awesome!
3. Friday, May 8, 2020 at 5:42:01 PM Does this expose .old and .new field properties where .old is the original value and .new is the modified value?
4. Friday, May 8, 2020 at 6:05:18 PM Aurelius does have such feature in events and datasets, yes.
Wagner R. Landgraf
5. Friday, May 8, 2020 at 6:05:19 PM Aurelius does have such feature in events and datasets, yes.
Wagner R. Landgraf
All Blog Posts | Next Post | Previous Post
I hope that i hear good news