KB Article A1603
Visible to All Users

How to delete a large set of persistent objects

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

Show previous comments (2)

    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 !!!

    Anatol (DevExpress) 7 years ago

      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).

      Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

      Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.