Try fast search NHibernate

04 May 2009

Oh… beautiful SQL

Given a complex mapping like this

<class name="Animal">
<
id name="id">
<
generator class="native"/>
</
id>
<
property name="description"/>
<
property name="bodyWeight" column="body_weight"/>
<
many-to-one name="mother" column="mother_id"/>
<
many-to-one name="father" column="father_id"/>
<
many-to-one name="zoo" column="zoo_id"/>
<
property name="serialNumber"/>
<
set name="offspring" order-by="father_id">
<
key column="mother_id"/>
<
one-to-many class="Animal"/>
</
set>
<
joined-subclass name="Reptile">
<
key column="animal"/>
<
property name="bodyTemperature"/>
<
joined-subclass name="Lizard">
<
key column="reptile"/>
</
joined-subclass>
</
joined-subclass>
<
joined-subclass name="Mammal">
<
key column="animal"/>
<
property name="pregnant"/>
<
property name="birthdate" type="date"/>
<
joined-subclass name="DomesticAnimal">
<
key column="mammal"/>
<
many-to-one name="owner"/>
<
joined-subclass name="Cat">
<
key column="mammal"/>
</
joined-subclass>
<
joined-subclass name="Dog">
<
key column="mammal"/>
</
joined-subclass>
</
joined-subclass>
<
joined-subclass name="Human">
<
key column="mammal"/>
<
component name="name">
<
property name="first" column="name_first"/>
<
property name="initial" column="name_initial"/>
<
property name="last" column="name_last"/>
</
component>
<
property name="nickName"/>
<
property name="height"/>

<
property name="intValue"/>
<
property name="floatValue"/>
<
property name="bigDecimalValue"/>
<
property name="bigIntegerValue"/>

<
bag name="friends">
<
key column="human1"/>
<
many-to-many column="human2" class="Human"/>
</
bag>
<
map name="family">
<
key column="human1"/>
<
map-key column="relationship" type="string"/>
<
many-to-many column="human2" class="Human"/>
</
map>
<
bag name="pets" inverse="true">
<
key column="owner"/>
<
one-to-many class="DomesticAnimal"/>
</
bag>
<
set name="nickNames" lazy="false" table="human_nick_names" sort="natural">
<
key column="human"/>
<
element column="nick_name" type="string" not-null="true"/>
</
set>
<
map name="addresses" table="addresses">
<
key column="human"/>
<
map-key type="string" column="type"/>
<
composite-element class="Address">
<
property name="street"/>
<
property name="city"/>
<
property name="postalCode"/>
<
property name="country"/>
<
many-to-one name="stateProvince" column="state_prov_id" class="StateProvince"/>
</
composite-element>
</
map>
</
joined-subclass>
</
joined-subclass>
</
class>

Which should be the result of s.CreateQuery("delete Animal").ExecuteUpdate() ?

Oh… beautiful SQL

create table #Animal (id BIGINT not null) 

insert into #Animal SELECT animal0_.id as id FROM Animal animal0_

DELETE FROM Human WHERE (mammal) IN (select id from #Animal)

DELETE FROM Dog WHERE (mammal) IN (select id from #Animal)

DELETE FROM Cat WHERE (mammal) IN (select id from #Animal)

DELETE FROM DomesticAnimal WHERE (mammal) IN (select id from #Animal)

DELETE FROM Mammal WHERE (animal) IN (select id from #Animal)

DELETE FROM Lizard WHERE (reptile) IN (select id from #Animal)

DELETE FROM Reptile WHERE (animal) IN (select id from #Animal)

DELETE FROM Animal WHERE (id) IN (select id from #Animal)

drop table #Animal

Do you see the temp table ? Do you see the order of queries ?

“se me cayó una lagrima”



kick it on DotNetKicks.com

8 comments:

  1. Hola.
    Primero que nada felicitarte por tu blog, el cual es un maravillosa fuente de información.
    Soy estudiante y estoy haciendo una aplicación bastante grande. Por querer abstraerme de la capa de datos he acabado conociendo Nhibernate (y ORM en general). ¿Cómo no me hablaron sobre esto los profesores? Seguramente porque ni lo saben...
    El diseño orientado a modelos es algo verdaderamente hermoso y práctico. La de tiempo que he perdido haciendo aplicaciones diseñadas a partir de sus datos.
    Bueno, perdona por el "rollo" pero quería agradecerte que tu blog me haya hecho ver la luz y quería preguntarte una cosa que seguramente sea una bobería pero no consigo hacer; ¿Cómo se puede establecer una relación uno a muchos en el diseñador de diagramas de visual studio? Veo que en algunos diagramas en tu blog existe esa relación. Sin embargo no soy capaz de reproducirlo. Tengo una clase con una propiedad de tipo IList de C y quiero relacionarla con muchos C. ¿Podrías indicarme el modo?
    Saludos desde Tenerife.

    ReplyDelete
  2. Why are you using a temp table instead of just repeating the where clause?

    ReplyDelete
  3. @DotWoot
    1) Think about how much complex, and slow, may be is what you call "where clause" and then repeat the same query 8 times (as in the example).
    2) All deletes must be consistent so all should use same source of Ids to delete; same select in different moments may have different result.

    As last: talk with your DBA and ask him which will be his solution in a stored procedure.

    ReplyDelete
  4. 1) If the where clause isn't complex then you actually lose performance.
    2) Why not wrap the delete in a transaction?

    ReplyDelete
  5. @DotWoot
    1) I hope you can prove it, using some real life domain. Don't forget that you have both behavior available (the classic session.Delete and the new executable HQL).
    2) Yes you should involve any kind of persistence action in a transaction.

    ReplyDelete
  6. When you call bulk deletes, it does not fire the IPre/IPost events, correct?

    ReplyDelete
  7. correct... no event related to each entity is fired using executable-HQL because no entities will be loaded (true even with cascade actions).

    ReplyDelete
  8. This can sometimes be a rather slow approach, since temp tables are physical tables (in MSSQL) and the lack of a primary key or index on the temp table will cause inefficient IN matching.

    All in all though, it's a good approach, since it's the only uniform way to ensure a consistent delete. Reusing the where clause does not ensure consistency.

    In MySQL, maybe a multi-table delete could be used.

    ReplyDelete