Optimize ISession.Delete(string, ...)

[Alexander Zaytsev]

Hi,

There is a "Delete" method on ISession which can accept query. But unfortunately, it is slow because it is actually equivalent to

foreach(var o in s.Delete("from Entity e")) s.Delete(o);

Eg. it first loads all objects into the memory and then deletes them one-by-one.

We could, potentially, delegate the implementation to just CreateQuery("delete from Entity e").ExecuteUpdate();

The difference between this approach that the second one does not trigger cascade deletes. 

What do you think?

Best Regards,

Alexander

[Michael Powell]

On Tue, Dec 19, 2017 at 7:28 PM, Alexander Zaytsev <haz...@gmail.com> wrote:
> Hi,
>
> There is a "Delete" method on ISession which can accept query. But
> unfortunately, it is slow because it is actually equivalent to
>
> foreach(var o in s.Delete("from Entity e")) s.Delete(o);
>
> Eg. it first loads all objects into the memory and then deletes them
> one-by-one.

Lazy load collections/properties?

> We could, potentially, delegate the implementation to just
> CreateQuery("delete from Entity e").ExecuteUpdate();
>
> The difference between this approach that the second one does not trigger
> cascade deletes.

I'm not sure what else you mean by "deleting them one by one". You
mean you have a collection of children also being deleted?

> What do you think?
>
> Best Regards,
> Alexander
>

> --
>
> ---
> You received this message because you are subscribed to the Google Groups
> "nhibernate-development" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to nhibernate-develo...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Frédéric Delaporte]

Hi,

Alexander was surely willing to mean s.Delete() is equivalent to:

foreach(var e in s.CreateQuery("from Entity e").List()) s.Delete(e);

So here we have at least two queries, one loading all matching entities into memory, the other batching the deletes together (assuming batching is enabled and batch size is big enough to accommodate all deletes).

Delegating the implementation to CreateQuery("delete from Entity e").ExecuteUpdate(); instead will not be equivalent not only due to cascade delete which will not be handled unless set on database foreign keys too, but also because it will not benefit of the batch ordering feature which takes care of deleting according to foreign key constraints values.

In tests, I tend to favor CreateQuery("delete from Entity e").ExecuteUpdate() over s.Delete("from Entity e"), but I have seen many times the former failing due to foreign key constraints (especially with auto-join cases), while the later was succeeding thanks to batch ordering.

So changing its implementation can be a serious breaking change. I would be more in favor of rewording a bit its xml documentation for making it more obvious that it loads all entities to delete in memory. Currently, this is:

Delete all objects returned by the query.

It could be changed to:

Load all objects returned by the query then delete them.

[Alexander Zaytsev]

Ok, let's reword to clarify the behaviour.

--

---
You received this message because you are subscribed to the Google Groups "nhibernate-development" group.

To unsubscribe from this group and stop receiving emails from it, send an email to nhibernate-development+unsub...@googlegroups.com.