Reducing DB-Traffic on Queries?

eiben · **Joined:** Tue Dec 04, 2007 4:48 pm **Posts:** 21

Hi,

I have a n:m association of two classes (Project and Line). I created the following query:

Code:

DetachedCriteria matchingProjects = DetachedCriteria.For(typeof (Project))
                .CreateCriteria("Linen", "projectLinen")
                .Add(Subqueries.PropertyIn("projectLinen.LineId", lineOfUser));

            IList<Project> projekts = matchingProjects.GetExecutableCriteria(session)
                .List<Project>();

But I noticed while studying the SQL send to the server, that all columns of Project as well as all columns of Line are being retrieved from the server. For one, the columns of Line are actually not needed (that's why I made the association of lines to products lazy! :) ) and second this gives me way too many results in my list.

I managed to narrow the list returned by the query by adding

Code:

.SetResultTransformer(new NHibernate.Transform.DistinctRootEntityResultTransformer())

but this still fetches way to many data from the DB, which is totally unnecessary.

Speaking of unnecessary "DB-noise" ... since Project and Line have a n:m relation there exists a db-table 'ProjectLine', which holds the primary keys of Project and Line. When I add

Code:

.CreateCriteria("Linen", "projectLinen")

a join from Project->ProjectLine->Line is actually performed. But since I'm just filtering on the primary key of Line, the last join to the Line-table is actually not necessary, because this information could also be retrieved from the ProjectLine-table - but how can I tell the criteria to do such an "optimization"?

wolli · **Posted:** Thu Oct 02, 2008 9:59 am

What happens if you do it this way:

Code:

DetachedCriteria matchingProjects = DetachedCriteria.For(typeof (Project),"p")
                .Add(Subqueries.PropertyIn("p.Linen.LineId", lineOfUser)); 

eiben · **Joined:** Tue Dec 04, 2007 4:48 pm **Posts:** 21

wolli wrote:

What happens if you do it this way:

Code:

DetachedCriteria matchingProjects = DetachedCriteria.For(typeof (Project),"p")
                .Add(Subqueries.PropertyIn("p.Linen.LineId", lineOfUser)); 

well ... like in the other thread at http://forum.hibernate.org/viewtopic.php?t=991142 I changed the property-name from 'LineId' back to 'Id' ... so I get this:

Code:

NHibernate.QueryException: could not resolve property: Lines.Id of: NHibernateQueries.Project

:(

BTW: misspelled something there ... the collection-property is called 'Lines' not 'Linen' :)