DataAccessAdapter.DeleteEntityCollection generating individual delete statements

Posts   
 
    
mprothme avatar
mprothme
User
Posts: 78
Joined: 05-Oct-2017
# Posted on: 05-May-2021 18:11:57   

Build Version: 5.5 (5.5.1) RTM Build Date: 17-Jan-2019 Project: Adapter project targetting .NET 4.5.2 Database: MS SQL 2019

We have a parent-child table structure (one parent, many children) in our database. We have a method that

  1. Loads the parent and pre-fetches the children
  2. Calls DeleteEntityCollection on all children in the parent entity (we put them all in one entity collection)
  3. Calls DeleteEntityCollection on the parent entities (also put into one collection)

What we've noticed is that the deletes in step 2 run in one large query with a bunch of parameters, but the deletes in step 3 run as individual delete statements which seems to take a lot longer. Any thoughts as to why this is?

Thanks!

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 06-May-2021 00:34:41   

So you are calling DeleteEntityCollection() in both cases, right?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 06-May-2021 09:27:07   

For clarity: there's no query batching implemented for Deletes in our runtime so this is a bit odd indeed. You have any customization added to the runtime perhaps? You measured the query executed using orm profiler, tracing or sql profiler?

Frans Bouma | Lead developer LLBLGen Pro
mprothme avatar
mprothme
User
Posts: 78
Joined: 05-Oct-2017
# Posted on: 06-May-2021 21:07:55   

Sorry about this!

I was using a SQL trace to debug this, and it looks like there's another set of deletes in the same call chain that leverages Adapter.DeleteEntitiesDirectly (passing the primary key fields in a range compare predicate). When I looked through the trace I thought the direct delete was generated by the code in step 2, but it was unrelated.

It also had nothing to do with LLBLGen pro. It turns out another table (with many records) contained a foreign key to the parent table, and that foreign key wasn't indexed, which hurt delete performance.

Thanks!