I'm writing an import program and using llblgen to go through and delete the old items so that the new can be added. There are 9 tables with one "master" table. I get the key from the master and propagate through to the children (there's about 2 levels beneath the master). As I traverse, I go through and delete the child records all the way back up the chain. For each level I call DeleteEntitiesDirectly (using adapter). The master list only has 3000 records (very small) and there are potentially 1 to 4 child records. This seems like an action that should take a few seconds, however, it's taking around 5 minutes.

This leads me to believe that I'm doing something wrong, or at least there's a much better way. Am I missing something?

Thanks.

I think you're executing 3000 deleteentitiesdirectly queries. I think it's best to execute delete queries on the child tables, with a filter on the master table, so you'll get the right children, then move one level up. This should result in much less queries.

Otis wrote:

I think you're executing 3000 deleteentitiesdirectly queries. I think it's best to execute delete queries on the child tables, with a filter on the master table, so you'll get the right children, then move one level up. This should result in much less queries.

Thanks for the quick reply. I actually do a deleteentitiesdirectly at the end on a filter for the master table, however, there is always at least one child per and maybe another beneath that, so in theory I could be doing 3000 deleteentities. The children of children are not related to the master, so that's quite possible. Still seemed like it should be faster

Perhaps not.

billb wrote:

Otis wrote:

I think you're executing 3000 deleteentitiesdirectly queries. I think it's best to execute delete queries on the child tables, with a filter on the master table, so you'll get the right children, then move one level up. This should result in much less queries.

Thanks for the quick reply. I actually do a deleteentitiesdirectly at the end on a filter for the master table, however, there is always at least one child per and maybe another beneath that, so in theory I could be doing 3000 deleteentities. The children of children are not related to the master, so that's quite possible. Still seemed like it should be faster

Perhaps not.

I'm not entirely sure I understand the datamodel completely, but I think you should pick this up 'per table'. So for each table, delete ALL entities in one go which have a parent in the parent table. This should result in a single delete statement, so with 9 tables, that's 9 delete statements. That's what I was implying, but perhaps this isn't possible. A hierarchy with FK's is deleted the fastest by just starting at the leaves and delete per leave LEVEL in 1 go.

Another way is to define cascading deletes in the database on the FK's and simply remove the PK rows.