So,
I know there are many solutions NHibernate offers to handle aggregated values, but I am struggling from a database performance standpoint... as I am not a dba and don't have one on payroll!

I have MANY pages in my web application that display counts and sums of associated records (i.e. number of line items, sum of line items, etc...).

The possible solutions for getting this information that I know of are:

1. Iterate over the associated objects in code and compute the values
2. Issue a separate query to get these fields during the binding of my data grids (select count(*)... etc...)
3. Put a formula property on my root object, which will issue a subselect and populate a field on my persistent object.
4. Create a new "view" object (VO) and do a "select into" statement. Then bind this VO to my data grid
5. Include the computed column directly into my HQL statement and do an order by / group by, then merge the objects (possibly using projections here)
6. Alter my db schema to include *running total / count* columns on these tables and manage the updating of these columns when I alter the root record (i.e. incrementing the number of lines)

I was leaning toward the subselect approach using a mapped formula property, but I don't always want to incur the overhead of the subselect when I don't need it. Also, after doing some preliminary research on SQL Server performance, I found a bold reference stating:

"developers, as a rule, should use JOINS in lieu of subselects when the subselect contains aggregate functions. [7.0, 2000] Tip provided by Silverscape Technologies, Inc (www.silverscape.net) Updated 8-21-2006"

Any help is much appreciated.

i'd be interested to see what some of the other vets say, too.

we have been using a combination of solutions depending upon the situation. if the association is mapped as a collection in the domain object then we use collection.Count when databinding. in other instances where we don't need the whole object (and associated graph) we have DTOs that are instantiated using the hql "select new..." syntax combined with aggregate functions and joins.

we are now, however, looking into creating some Views for some of the more comprehensive calculations (doing STDEV, SUM, AVG across a WHOLE table)...

-d

Does anyone have an opinion / advice on this subject? Any help is much appreciated.

Thanks,