Choosing an optimization method for deleting rows in oracle, although entity structure

Question

I have a scenario in which I have to periodically delete several hundred thousand rows (based on a server solution or a specific fixed time interval). I am using entityframework 6.0 and the problem is normal. The Remove () method is slow for mass use. I think of possible cases:

Case 1: Through the entity infrastructure, use RemoveRange.

var db = new MyDbContext(); var itemsToDelete = db.TableFoo.Where(x=>!x.new); db.MyTable.RemoveRange(itemsToDelete); db.SaveChanges(); 

I checked this and it is faster than using Remove alone foreach. But atomic sqlacan is still sending it instead of sql.

Case 2: Through the essence of the framework, they call some procedure or package, which in turn will perform the delete operation in the tables. Although it seems to me that this is the fastest option, but I still need to understand what is the best way to remove sql, I know what to truncate, but this will not serve the purpose here. I know:

 CREATE OR REPLACE PROCEDURE deleteDBFoo(p_toc IN DBFOO.TOC%TYPE) IS BEGIN DELETE DBFOO where TOC < "SOME DATE"; COMMIT; END; 

Case 3: To automate this task in oracle (I don’t know if this is possible or a good idea), in any case, it is necessary to delete all old lines from a certain time interval of the feed.

What is the optimized way to handle this scenario? If there is another better way than these cases, kindly shed light on this.

UPDATE1 : after some profiling, I found the following results:

To delete 1,000 rows in a database

The Trunc table took 3.46 seconds. Case 2: took 37.398 seconds. Case 1: took a few minutes. For my solution, I will go with Case 2, but I am still waiting for a solution better than Case 2 or somehow improve it.

Answer 1

The fastest way to delete bulk data is to do it on the server side.

In your case, since you want to use the entity infrastructure, you must create a stored procedure with parameters and execute this storage procedure from the code.

Entity structure is slow if you want to use case 1; because for each deletion it creates a request and executes it.

---

Try something like this:

 CREATE OR REPLACE PROCEDURE DeleteUnnecessaryData (endDate IN DATE) IS BEGIN DELETE DBFOO where TOC < endDate; COMMIT; END;