I pinged James about this earlier and have been tinkering off and on
tonight--and am up for ideas (or a LOT more booze/coffee). I'll try to
explain best I can without the lawyers yammering on about NDAs with
our vendor's schema.
Previously, our entities (students & teachers) were dished out by
speedy materialized views and the response time was fantastic. Over
the past few days, that structure's changed from a somewhat simplistic
one-to-many to a flattened out many-to-many. In addition, they're
moving from materialized views to standard, dynamic views.
The new structure looks something like this:
teachers_view (teacher entity object)
id
schoolid
buildingid
students_view (student entity object)
id
schoolid
buildingid
roster_view (no entity object, just a lookup reference table)
teacherid
studentid
buildingid
schoolid
That's fine, the roster_view can serve as the reference table for our lookups.
So, in FNH, the TeacherMap has something similar to:
HasManyToMany(x => x.Students)
.Table("roster_view")
.ParentKeyField("teacherid")
.ChildKeyField("studentid")
.Fetch.Select();
and the StudentMap:
HasManyToMany(x => x.Teachers)
.Table("roster_view")
.ParentKeyField("studentid")
.ChildKeyField("teacherid")
.Fetch.Select();
It's a many-to-many because of the data. A teacher has many students,
a student has many teachers (like secondary students have anywhere
from 4 to 14 distinct teachers). Pretty sure the maps are right--doing
this by memory at the moment, our VPN appears to be down.
And that works... and works well actually, the result are what I want,
but the performance is atrocious. It's doing a left-outer-join on two
non-materialized views (teachers_view & roster_view)... and
roster_view has a couple hundred million rows (query times anywhere
from 16 to 30 seconds for 200ish returns). I ripped the generated SQL
out of NHProf, tossed it into SQL Developer, replaced the left outer
join with an inner join, and boom, back in 0.02 sec with the results I
want (meaning: matches the saved data set I had ran the other day
LOL).
Following James' idea, I ditched the idea of a mapped property and
came up with a criteria, but found that it would simply run select n+1
and query out each student. Not ideal, but a metric ton faster than
the outer join.
This doesn't seem fancy, I'm assuming that my past 10 hours of staring
at it and the other breakages we've had in the past 24 hours has
simply burned the neurons a bit too much.
I was hopeful there was a property on HasManyToMany that I could
toggle (and there MIGHT BE after this is all said and done (^_~))...
though I assume it pops out as an outer join for a reason and that
someone far smarter than me with query optimization can enlighten me.
:)
Thanks all!
-dl
---
David R. Longnecker
blog: http://blog.drlongnecker.com
twitter: dlongnecker
--
You received this message because you are subscribed to the Google Groups
"Fluent NHibernate" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/fluent-nhibernate?hl=en.
