Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

These support forums are now closed for new topics and comments.
Please head on over to for support.

  • Andrei 68 posts 130 karma points
    Nov 17, 2010 @ 21:24

    Faster query to get products by category

    Hi Soren,

    We were using the CommerceLibrary:GetCategory function to get all products from a category, but performance was quite bad when calling this function (over 10 s). And I noticed that this function is getting products with variants and all inner product information, for each product and the result xml is quite large with lot of unnecesary info in this case. So I am trying to build a query that will give me only very light data for products in one shot from sql. Here is my code, from which I build after a very simple xml and return it to xslt:

            public static XPathNodeIterator GetSimpleProductsList(string catalogName, string category1)


                ProductCatalog catalog = ProductCatalog.SingleOrDefault(x => x.Name == catalogName);

                Category cat1 = Category.SingleOrDefault(x => x.Name == category1);


                var q = from prod in Product.All()

                        join cpr1 in CategoryProductRelation.All() on prod.ProductId equals cpr1.ProductId

                        join pd in ProductDescription.All() on prod.ProductId equals pd.ProductId

                        join price in PriceGroupPrice.All() on prod.ProductId equals price.ProductId

                        where cpr1.CategoryId == cat1.CategoryId && prod.ParentProductId == null

                        && pd.CultureCode == UCommerce.Infrastructure.Globalization.Globalization.CultureCode

                        && price.PriceGroupId == catalog.PriceGroupId

                        select new { Sku = prod.Sku, Name = prod.Name, DisplayName = pd.DisplayName, ShortDescription = pd.ShortDescription, Price = price.Price };


      ...// build small xml here and return



    My questions here: 

    1. this generates an inner join with ProductDescription, how can I simulate the left join and get the product description only if there is one?

    2. is this way of retrieving product price safe enough? because I've been looking in ProductRenderer from V2 and the way price is retrieved there is rather complex.

  • Søren Spelling Lund 1797 posts 2786 karma points
    Nov 18, 2010 @ 09:02
    Søren Spelling Lund

    I'm surprised that you're not getting better performance from GetCategory. Typically you can load up a category with many thousands of products without performance suffering.  What does your load out look like?

  • Søren Spelling Lund 1797 posts 2786 karma points
    Nov 18, 2010 @ 09:40
    Søren Spelling Lund

    Hi Andrei,

    I discovered an issue which causes uCommerce to hit the database more than required. I'm working on a fix for it right now.

  • Søren Spelling Lund 1797 posts 2786 karma points
    Nov 18, 2010 @ 14:18
    Søren Spelling Lund

    Shot you a mail with a prerelease version to test.

Please Sign in or register to post replies

Write your reply to: