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:
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.
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?
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.
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?
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.
Shot you a mail with a prerelease version to test.
is working on a reply...