I'm going insane.

So let's say you have an object in database, with 20-30 related objects (Or lists of objects) (All related objects have a foregn key to the "main" object).

Now, as long as you just edit/create thinga everything is fine.

But the deletion... Oh MY GOD

"Just put on delete cascade", right ? RIGHT ?

WRONG ! presence of some objects should block delete, while others can be deleted and some are "situational" depending on the first object state.

So delete operation with all the checks takes .... 1 - 2 seconds.

Seems fine ? WRONG ! When you have 40 or more objects to delete, even 1 second is too long.

Should I say "fuck it" and just write a stored proc which will crash if object cannot be deleted for any reason ? because with Entity Framework, I don't see how I can do it effitenly.

But I HATE stored proc, after couple of month/years noone remembers how they work...


Ok I feel better.

  • 1
    Update delete is down to 400 ms, still too much
  • 1
    Are deletes happening so often in your project?
  • 1
    @asgs No.

    But clients have a possibility to apply "templates" which triggers delete of "curently applied template" and all manual data entered.

    And they have ability to do it in bulk on a 1000+ objects in 1 click.

    I'm working to move all of that in async mode.

    That usually happens in the first couple of months of implementation where they are testing different templates (And ofc, they don't test on 1 object but on multiple thousands)
  • 3
    Update : Delete down to 9 ms. I can breath now
  • 0
    Many DBs support an DELETE JOIN.

    Which does what it says: Deleting from multiple tables by using JOINs.

    When associations come into play, this becomes a crucial thing, as u nuke everything in one DDL operation.

    Just make sure the WHERE part is safe or good bye all tables. XD
  • 0
    @IntrusionCM not a solution.

    It doesn't allow to check "if the link Q is present then delete should be blocked" :)
  • 1

    I couldn't help but noting: It is a solution. Just make the button red and call it fast delete.


    But yes, such things need to be handled seperately then.

    :) Good job anyway 👍
  • 0

    I found a middle ground finally.
    Delete is still around 50ms, but all logic is handled.
    I think it’s more of database structure / indexes issue right now and I don’t have time allowed for that research.
    But we should have a DB expert consulting soon. I’ll put that in his/her todo list.
Add Comment