delete generates lots of queries

[graphicsxp]

Hi,

If I remove all the items in my collection and saves my entity to
which the collection belongs, I get one DELETE query per items.
How can I have all these items deleted with the same DELETE query
using something like :

DELETE FROM myTable WHERE id IN (1, 2, 3 , ... )

Thanks

[Fabio Maulo]

on-delete="cascade" (attribute of <key> tag)

and/or check the inverse.

2009/4/6 graphicsxp <graph...@googlemail.com>

--
Fabio Maulo

[graphicsxp]

Thanks. But it's driving me nuts...

I've added on-delete "cascade" and I also have the inverse=true but I
still get as many DELETE as they are items to delete.

In my Post mapping file :

<bag name="Comments" inverse="true" cascade="all-delete-orphan"
lazy="true">
<key column="PostID" on-delete="cascade"/>
<one-to-many class="Metrica.CommentPost, Metrica" />
</bag>

In my Comment mapping file :

<many-to-one name="thePost" class="Metrica.Post, Metrica"
column="PostID" not-null="true" />

Is there something wrong with what I'm doing ?





On 6 avr, 16:21, Fabio Maulo <fabioma...@gmail.com> wrote:
> on-delete="cascade" (attribute of <key> tag)and/or check the inverse.
>
> 2009/4/6 graphicsxp <graphic...@googlemail.com>

[Fabio Maulo]

Let NH generate your DB and then see the difference ;)

2009/4/6 graphicsxp <graph...@googlemail.com>

--
Fabio Maulo

[graphicsxp]

er.. no I don't think this is the solution ! My db is well in place
and it's there for over three years and I can't re-design anything at
this stage.

Any other suggestions ?

On 6 avr, 16:45, Fabio Maulo <fabioma...@gmail.com> wrote:
> Let NH generate your DB and then see the difference ;)
>

> 2009/4/6 graphicsxp <graphic...@googlemail.com>

[James Crowley]

Fabio doesn't mean throw your old DB out... but if you look at the SQL Schema that NHibernate generates based on your mappings, that can generally be a good indicator of problems with the mapping that you're using (if you start seeing columns you're not expecting, for instance!)

2009/4/6 graphicsxp <graph...@googlemail.com>

--
James Crowley
Managing Director
Developer Fusion - Connecting developers worldwide

Developer Fusion Ltd | 58 Sandringham Close | Enfield, EN1 3JH
mob: 07986 624128 web: http://www.developerfusion.com/

[Fabio Maulo]

Or if you see a trigger that you don't have in your DB, for another instance.

2009/4/6 James Crowley <james....@gmail.com>

--
Fabio Maulo

[graphicsxp]

Ok, I had misunderstood.

I've used hbm2ddl and from what I can see, the generated SQL for
creating the tables give me the same database structure. It also adds
the correct foreign-primary key constrainsts. The primary key is an
identity column, same as my existing DB.
Fabio > No triggers is created.

so it all looks the same. What could explain the fact that I can't
have my DELETE grouped in one single query ?

On 6 avr, 17:22, Fabio Maulo <fabioma...@gmail.com> wrote:
> Or if you see a trigger that you don't have in your DB, for another
> instance.
>

> 2009/4/6 James Crowley <james.crow...@gmail.com>

>
>
>
> > Fabio doesn't mean throw your old DB out... but if you look at the SQL
> > Schema that NHibernate generates based on your mappings, that can generally
> > be a good indicator of problems with the mapping that you're using (if you
> > start seeing columns you're not expecting, for instance!)
>

> > 2009/4/6 graphicsxp <graphic...@googlemail.com>

[Fabio Maulo]

mmmm you are seeing another movie (the on-delete="cascade" is tested).

2009/4/6 graphicsxp <graph...@googlemail.com>

--
Fabio Maulo

[Fabio Maulo]

ah... you are deleting the master or you are simply clearing details ?

2009/4/6 Fabio Maulo <fabio...@gmail.com>

--
Fabio Maulo

[graphicsxp]

Ok, so what do you think could prevent NH from doing the DELETE *** IN
(1, 2, ...) ?

On 6 avr, 17:44, Fabio Maulo <fabioma...@gmail.com> wrote:
> mmmm you are seeing another movie (the on-delete="cascade" is tested).
>

> 2009/4/6 graphicsxp <graphic...@googlemail.com>

[Fabio Maulo]

don't use delete-orphan if you don't really need it.

Or create a NUnit test and a Improvement request in our JIRA.

2009/4/6 graphicsxp <graph...@googlemail.com>

--
Fabio Maulo

[Graham Bunce]

I think you have the same problem I had.... NH will not (as I
understand it) generate a single DELETE FROM entity WHERE foreignkey =
X. From my tests it will work out the keys of the child table and
delete them all one-by-one, i.e. DELETE FROM entity WHERE primarykey =
Y, where NH has already got the primary keys into its cache.

The documentation is very misleading IMO about this.

On-delete-cascade does not mean NH will do the above, it means NH will
not do a delete at all and leave it up to the database (RI cascade
delete or a trigger).

Therefore if you cannot change your DB then you are stuck, which does
surprise me TBH as there are plenty of brownfield situations where you
cannot change the DB.

I'm happy for a NH expert to come along and say "no, you're an idiot,
this is the mapping you need" (and I wish they would) but I've raised
this query myself on these forums a while ago and this was the
response I eventually managed to work out from some of the very
cryptic responses I recieved.

If I'm wrong, then please let me know.

[graphicsxp]

Fabio> We've cross-posted, I only saw your question now.

So that we are all on the same page :

class A{

IList<B> myChildren;

}

class B {

}

int Id = 5;
A myEntity = session.getById<A> (Id);

A.myChildren.clear();

session.SaveOrUpdate(A);

If there were 3 children in the collections, the sql generated is:

DELETE FROM TABLE_A WHERE CHILDREN_ID = 1
DELETE FROM TABLE_A WHERE CHILDREN_ID = 2
DELETE FROM TABLE_A WHERE CHILDREN_ID = 3

When what I would like NH to do is :

DELETE FROM TABLE_A WHERE CHILDREN_ID IN (1, 2, 3)

Hope it is clear to everyone now :)

Please someone, tell me NH can do that :)

[Fabio Maulo]

If what you are looking for is only one round trip you can obtain the same result configuring

<property name="adonet.batch_size">100</property>

If you want exactly 

DELETE FROM TABLE_A WHERE CHILDREN_ID IN (1, 2, 3)

you should add a Improvement request in our JIRA.

2009/4/6 graphicsxp <graph...@googlemail.com>

--
Fabio Maulo

[graphicsxp]

Ok, so it's not possible to do what I want, that's all I wanted to
know. I will add the JIRA request.

Regarding the batch thing, I was told I can't do it when doing an
INSERT because my table has a identity field. Is it true for DELETE
as well ?

On 6 avr, 22:13, Fabio Maulo <fabioma...@gmail.com> wrote:
> If what you are looking for is only one round trip you can obtain the same
> result configuring<property name="adonet.batch_size">100</property>
>
> If you want exactly
> DELETE FROM TABLE_A WHERE CHILDREN_ID IN (1, 2, 3)
>
> you should add a Improvement request in our JIRA.
>

> 2009/4/6 graphicsxp <graphic...@googlemail.com>

[graphicsxp]

I'll reply to myself ....

I've used NHProfiler (really good tool : ) ) and I can see the DELETE
are batched according to the value in <property
name="adonet.batch_size">5</property>

I've created the JIRA request for the IN thing.

Thanks to all !

[Graham Bunce]

I'm a little confused over your mapping of table to class but,
assuming you meant TABLE_A relates to class A and TABLE_B relates to
class B, your example would therefore be:

DELETE FROM TABLE_B WHERE CHILDREN_ID IN (1, 2, 3) - Children_id is
the primary key

Correct?

If so, then If you're raising the JIRA then I would suggest that
surely the SQL:

DELETE FROM TABLE_B WHERE PARENT_ID = ? is more efficent (as NH knows
the parent ID already). The way you are suggesting would mean the IN
statement would be massive if there were a large number of rows as it
needs to list every primary key value.

[graphicsxp]

Yes but that would delete all of the children for a given parent !
What if I only want to delete a few of them ? The IN would still be
appropriate and would be :

DELETE FROM TABLE_B WHERE CHILDREN_ID IN (1, 2, 3)

[Graham Bunce]

If you only wanted to delete a few then, yes you are correct. However,
how many is "a few" before the IN becomes extremely long?. I don't
know if there are any performance hits for a large number of IN
statements. If there is, I presume this would be different for each
database type. Could (should?) NH be coded to deal with this?

btw, you example said:

A.myChildren.clear();

Which suggested to me to delete all the children for the current
parent, hence my SQL suggestion.

Unless someone tells me differently, NH will not do this "delete all
children for a foreign key", which I feel it should as it performs
significantly better than row by row delete or, I expect, via a list
of IN key values. This is something I suspect is quite common.