Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / use "delete from" to d...
Finance forum
Unanswered

use "delete from" to delete records

Posted on by 236
Hi, 

Can i get rid of the while loop in the code below and make the delete faster?

I have an order header table (table1),  where this order has 2 types of lines (tableline1 and tableline2)
so it's like salesTable and imagine there is salesLine1 table and SalesLine2 table both linked to header salesTable

and i want to delete all orders with their 2 table lines where the header has a specifIc value for InventLocationId
and of course i need to make sure i delete the lines tables first before the header
class DeleteOrderService extends SysOperationServiceBase
{

    public void deleteOrder()
    {
        Table1              table1;
        TableLine1          line1Delete;
        TableLine2          line2Delete;
        Table1              table1Delete;


        while select Id from table1 where table1.InventLocationId == XParameters::find().LocationId
        {
            delete_from line1Delete where line1Delete.Id == table1.Id;
            delete_from line2Delete where line2Delete.Id == table1.Id;
            delete_from table1Delete  where table1Delete.Id == table1.Id;
        }

    }

}

it's like i would like to delete everything that this query returns:
select TABLE1.ID,INVENTLOCATIONID,*from TABLE1
join TABLELINE1 on TABLELINE1.ID = TABLE1.ID
join TABLELINE2 on TABLELINE2.ID = TABLE1.ID
where INVENTLOCATIONID = 'XX'
 
  • Martin Dráb Profile Picture
    Martin Dráb 225,610 Super User on at
    use "delete from" to delete records
    2. It depends on whether delete_from will delete records one by one, which - as I mentioned - depends on other things than just your code, therefore the question can't be answered.
     
    3. Your original code will be the least efficient because you load every header record twice. If there is a delete action on header (and you don't skip it), deleting lines separately would make things less efficient because the header would still check if there aren't lines to delete by the delete action.
     
    4. Assuming that the code has a reason for existence, simply ignoring it would be a bug. You can skip this call if you do the deletion somewhere else.
     
    5. You can't say for sure that no DB logging is and never will be enabled in any environment. That it's not at the moment isn't sufficient. Also, it's about many more things than just logging (as I already explained there).
  • CU04051814-0 Profile Picture
    CU04051814-0 236 on at
    use "delete from" to delete records
    Hi Martin,

    2. by three i mean: delete from header, delete from three tables or the code i put in the question with while loop

    3. So does that mean if i use only delete_from header table OR if I use 3 delete_from statements, without skipping data methods for both ways, then would performance will be the same?  or would the 3 deletes be better because in case one of the tables doesn't have code in the delete method then it will be faster? but delete from header is a guranteed that it will run row by row?
     
    4. it was a typo, i edited the code, it's tableLine2 
     
    5. There is no logging, only one of the tables has code in the delete method. I will need to analyze more i guess
  • Martin Dráb Profile Picture
    Martin Dráb 225,610 Super User on at
    use "delete from" to delete records
    1. I'm talking about any delete_from statement, which includes yours and André's code too.
    2. Which three do you mean? Also note that looking at code alone won't tell you whether the execution will run row-by-row because it depends on other things too (e.g. whether there are delete actions, DB log etc.).
    3. Yes, deletion will run row-by-row, even if you use delete_from (unless you call a skip* method, as I mentioned).
    4. I can't answer that. I know nothing about rentDeviceLine, for example. But I would assume that the code was put there because deleting the related rentDeviceLine records is necessary (and not calling it would conflicting with that).
    5. If you want to invest your time to it, analyze all related tables, data methods, event handlers, business events, logging requirements etc. to see whether you really can skip everything and make a bulk delete. Maybe you'll find that you can't do that. If you find that it's possible, you must consider whether you really want to do it. You're comparing performance gains with risks regarding business logic. For example, if you skip data methods, there is a risk that code will be added there later without taking into account that it won't be called in some cases.
    If you don't want to do such as analysis, don't skip any business logic.
  • CU04051814-0 Profile Picture
    CU04051814-0 236 on at
    use "delete from" to delete records
    Hi Martin,

    1. When you say "your code" will still call delete() method. Are you talking about both the while loop in the original question and the other suggestion from Andre about putting three delete_from statements?

    2. and which one of the three options you think is the fastest?

    I just looked, and one of the line tables has code in the delete table method.    public void delete()
        {
            Id                id = this.Id;
            Table1            table1;
            TableLine2        tableLine2;    
        
        
            super();
            ttsbegin;
    
            tableLine2.tableLine2(true);
            delete_from tableLine2
                where tableLine2.Field1== this.Field1;
    
            this.updateTable1(id);
        
            table1 = this.table1();
            if (table1 && table1 .selectForUpdate(true))
                table1.updateOrderDates();
        
            ttscommit;
        
        }
    3. so does that mean that currently row by row operation is happening in this table?

    4. I don't think i need this because this code assumes table1 is still there but i want to delete table1 so i don't think i need to update it, so i can skip it right?

    5. but also to add, there are other tables that have relations with those three tables (but i don't think those tables will have any records, as the orders i'm going to delete are in created status and nothing is done to those related tables yet). So what do u suggest in this case? what's the best way to go with?
  • Martin Dráb Profile Picture
    Martin Dráb 225,610 Super User on at
    use "delete from" to delete records
    Note that your code will still call delete() method and execute delete actions (to check if there aren't any related records to delete) unless you suppress that by methods like skipDeleteActions(). But you must be 100% sure that there is no important logic in delete() methods or any event handler, that all delete actions can be ignored and so on, otherwise your code will cause data corruption. While there are cases when you can make such assumptions, it's very dangerous in general.
  • CU04051814-0 Profile Picture
    CU04051814-0 236 on at
    use "delete from" to delete records
    Hi Andre

    Thank you. would the row by row operation happen for the three tables? or just the header table?

    I wouldn't want row by row operation to happen, because i have lots of orders and the two line tables can have alot of records for each order as well.
    so in this case, I  think even my while loop will be better than deleting the header table alone where there is cascade on lines. I think I will go with the three delete_from statements in this case
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,995 Super User on at
    use "delete from" to delete records
    Hi,

    In case you have set delete actions to cascade, related lines will be deleted when you delete the records from table1. Note that in this case, the record based deletion will be converted to a row-by-row delete action. 
     
    An inner join doesn't make sense in the delete_from statement as it will delete the records from the first mentioned table only. Performance wise, your three delete_from statements you mentioned in your reply might be the fastest option.
  • CU04051814-0 Profile Picture
    CU04051814-0 236 on at
    use "delete from" to delete records
    Hi Andre,

    so delete from only works with exist join? if i use inner join it won't work?

    and do you mean i should be three delete statements like this?
    delete_from line1Delete
        exists join table1
            where table1.Id == line1Delete.Id
               && table1.InventLocationId == XParameters::find().LocationId;
    
    delete_from line2Delete
        exists join table1
            where table1.Id == line2Delete.Id
               && table1.InventLocationId == XParameters::find().LocationId;
    
    delete_from table1
            where table1.InventLocationId == XParameters::find().LocationId;

    Hi Martin,

    you mean if i only delete header like this, then it should be enough and it will delete both line tables automatically?
    delete_from table1
            where table1.InventLocationId == XParameters::find().LocationId;

    i remember i tried it before and it didn't work even though the two line tables has cascade delete value. But i will try it again
  • Martin Dráb Profile Picture
    Martin Dráb 225,610 Super User on at
    use "delete from" to delete records
    You need such code only when your tables aren't designed correctly. Because order lines don't make sense without any order header, when an order is deleted, lines must be deleted too. The system will do it automatically for you, you just need to set On Delete property on the relation from the line table to the header table. The value should be Cascade.
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 287,995 Super User on at
    use "delete from" to delete records
    Hi,

    You can add a table join when using the delete_from command, an example:
    delete_from line1Delete
        exists join table1
            where table1.Id == line1Delete.Id
               && table1.InventLocationId == XParameters::find().LocationId;
     

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,995 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,610 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans