I'm beginning to think that the issue lies more on the sheer volume
and whatever the heck those views are doing behind the scenes the more
I look into it.
Here's the actual HasManyToMany code:
HasManyToMany(x => x.Students)
.BatchSize(50) // Average class size
.Fetch.Select()
.Schema("dev")
.Table("dev_roster_view")
.ParentKeyColumn("teacher_id")
.ChildKeyColumn("pupil_number");
That generates a gigantic outer join of two slow views...
SELECT {fields and stuff}
FROM dev.dev_roster_view students0_
left outer join dev.dev_students_xnr_view student1_
on students0_.pupil_number = student1_.Id
WHERE students0_.teacher_id = 12345 /* :p0 */
A pull of ~20 records takes 22181ms.
I pulled the logic out and tried a different route, using a criteria
that is, essentially, doing the same thing...
var studentLookupQuery =
@"SELECT DISTINCT pupil_number
FROM dev_roster_view
WHERE teacher_id = :teacherId";
var studentsForTeacher =
Session.CreateSQLQuery(studentLookupQuery)
.SetInt32("teacherId",id);
var criteria = DetachedCriteria.For<Student>()
.Add(Restrictions.In("Id", studentsForTeacher.List()));
var results = FindAll(criteria);
return results;
This runs in 3 calls (fetching the teacher, fetching the list of
student ids, fetching the details) in about 7500ms, a bit better, but
still painfully long on a web request.
Having had fairly simple data models (and/or models I could change,
unlike this vendor model)... I keep thinking I'm overlooking something
simple. Something like a switch from 'slow' to 'fast'. ;)
-dl
---
David R. Longnecker
blog: http://blog.drlongnecker.com
twitter: dlongnecker
On Thu, Jan 14, 2010 at 8:41 PM, David R. Longnecker
<[email protected]> wrote:
> 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.
