Description:
Sometimes it's necessary to delete a couple of hundred objects in one go. This can be very slow. Is there a way to do this in some type of batch or speed it up?
Answer:
Persistent objects can implement some rules and perform some actions when they are deleted. The order in which objects are deleted can also have an effect. Legacy applications can have triggers or additional constraints.
We don't recommend that you delete records manually because this may well be a complicated task:
- objects may be stored in several tables;
- after deleting records from a table, you should remove corresponding cached objects;
- there can be aggregated objects, which should be deleted as well.
You can use the Session.Delete method. Pass a collection of objects that you want to delete to this method as a parameter and it will call the Delete method for every object so that the business logic will work during the deletion process and then it will prepare and execute a query to delete all necessary records from the database at once.
The operation above can be time/memory consuming. To improve the application performance, start a background thread, load persistent objects in small portions, and delete them. Each time you delete a single portion, dispose of the used Session or UnitOfWork and create a new one. This trick helps you keep the amount of used memory low.
Note that the Session and SimpleDataLayer components are not thread-safe. Do not share a Session instance between threads. If you are using background threads in your application, it is necessary to switch to ThreadSafeDataLayer or create a separate SimpleDataLayer instance in a background thread. Refer to the following article for details: After update from 15.2.5 to 16.1.5 got "Reentrancy or cross thread operation detected" exception.
If you are using the Deferred Deletion feature and your intention is just to remove objects marked as deleted from the database, you can use the Session.PurgeDeletedObjects method for this purpose. We suggest that you create a separate tool for database maintenance. This tool can be used to create/update the database schema and to physically remove objects marked as deleted.
See also:
Fast Batch Deletions With DevExpress XPO
Direct SQL Queries
Hello, Dan. It my situation I need to delete 30000 of records. Direct SQL-query needs a few seconds for this operation, but Session.Delete needs 2-3 minutes. But SQL-query can not be used in wcf-connection.
So, is there any way to speed up deletion of very big datasource on xpo-leverl?
You might like to check out my new blog post which offers an alternative method.
http://blog.zerosharp.com/fast-batch-deletions-with-devexpress-xpo/
The How to add support for direct SQL queries and stored procedures to XPO service example demonstrates how to execute SQL queries when a WCF channel is used to access a database.
See also Transferring Data via WCF Services and Command 'DevExpress.Xpo.Helpers.CommandChannelHelper.ExecuteQuerySQL' is not supported by DevExpress.Xpo.DB.MSSqlConnectionProvider.??.
Dear,
Is there a chance to get a small vb.net example on this topic ?
http://blog.zerosharp.com/fast-batch-deletions-with-devexpress-xpo/
TX a lot !!!
Hello Chris,
You can compile this C# project as is and then reference the received DLL in your VB.NET project. Alternatively, use a C# to VB converter (e.g., you can find free online converters).