Inefficient map-reduce question

[nickchampion]

Hi,

I have a requirement to run a query and aggregate the results by 5 fields from the returned documents.

Normally I would set up a map reduce index for this, however the query needs to support many fields (about 12) setting up a map reduce index grouped by those 12 fields will not be efficient as we'd end up with very few documents aggregated into each group making the map reduce index a bit pointless.

So I am considering running the query and loading the results into memory to do the aggregations, but am obviously concerned with performance due to having to load so many docs with this approach.

Wanted to see if I am missing anything that may help in this situation and if not which approach you would recommend, map reduce or in-memory aggregations.

MongoDB has the concept of aggregation pipelines where we can feed the results of one query into a pipeline to do the aggregations, so wondering what options we have related to this with RavenDB

Thanks

[ayende]

What do you mean 5 or 12 fields, what you are using the group by on?

Is this something that you can use facets on? They are a great solution for this need?

[nickchampion]

If I did this via a map reduce index I'd need to group on about 12 fields to allow for all the filtering options we need to support, I could do this but due to so many group by fields I'd expect the index to be pretty inefficient.

I don't think facets would work as I need to group the results by multiple fields to show the sum of a combination of facets, the domain is farming commodities, they want a table of results view like this (each row is an aggregation of the available weight to sell by commodity and location)

Commodity | Type | Grade | Location | Available (sum of available weight)
Wheat | Winter | Grade 1 | Hampshire | 100 tonnes
Wheat | Winter | Grade 1 | Devon | 125 tonnes
Wheat | Spring | Grade 2 | Hampshire | 80 tonnes

Hope thats clear!

Thanks

[ayende]

But that is pretty much the best case here for this.
Map/reduce on 12 fields to do the first line of aggregation, then facets to aggregate further.

Can you share some data, so we can provide an example solution?