merchHelper.Query.Product.GetFromCollection(collection.Key, 1, 1500).Items currently takes 3+ seconds when returning ~100 items. On some pages, we pull all of the child collections, and all child products of those collections, so we can display a grid of available options for the collection page. Depending on the number of collections within the parent collection and the number of products, this process can take 20+ seconds.

Example:

            var collections = MerchelloContext.Current.Services.EntityCollectionService.GetChildren(brand.Key).ToList();
            foreach (var collection in collections)
            {
                var products = merchHelper.Query.Product.GetFromCollection(collection.Key, 1, 1500).Items.ToList();
                // do stuff with product "detached data"
            }

Normally if I have a performance issue like this, I would write a view or a direct SQL query to get at the data I need, however because I need to group, sort, and select by the detached data, this is not an option.

Ultimately What I'm trying to do: Get specific detached data "columns" for all products underneath a given product collection.

How can I accomplish this without 20+ second load times (i.e. without pulling all of the data from the product records, and without the overhead of loading one object at a time into memory)?

When I stepped through the Merchello code, I noticed that it first pulls the keys for the products, and then, one at a time, loads a typed object from lucene (or cached lucene data?) via the key. It seems like there is a more performant way to do this if you know you are loading a collection.