Optimizing querying performance

[Abdel]

Hi all,

I have a working code, but I its extremely slow and needs to be optimized. But Im not sure where to do this optimization. I have three tables: 

1. Products -> Holding general information
2. PriceList -> Holding details of price for each configuration of a product
3. ProductGallery -> Holding urls of images of a product

When I display my product grid, i need data from all these tables.. here is what I do:

var plist = dbContext.Products.Where(x => x.Category == cid && x.To_display == true);
               foreach (Product prod in plist)
               {
                   ProductDM temp = new ProductDM();

                   temp.ProductName = prod.Name_en;
                   temp.ProductID = prod.Product_id;
                   temp.Detail = prod.Detail_en;

                   var prlist = dbContext.PriceLists.Where(x => x.Product_id == prod.Product_id);

                   temp.PriceList = new Hashtable();
                   foreach (PriceList pl in prlist)
                   {
                       temp.PriceList.Add(pl.Specification, pl.Unit_price);
                   }

                  var pimgs = dbContext.ProductGalleries.FirstOrDefault(x => x.Product_id == prod.Product_id);
                  temp.SmallImage = pimgs.Summary;
                  temp.BigImage = pimgs.Image1;

                   productList.Add(temp);
               }

This seems to take about 10 seconds for displaying 8 products. If I comment out the query for pricelist and gallery, its fast. 
One solution would be to use Joins. But I would like to know how to do it in OpenAccess.
Another solution would be to remove the Gallery table and bring smallImage and bigImage to Products table.

Can anybody guide me to the right direction?

[Boris Georgiev]

Hello Abdel,

Now for every entity, a separate query is fired to the SQL Server. If you have associations between this tables, for optimization of queues you should use Fetch Plans API provided by Telerik OpenAccess ORM. Using Fetch Strategy class and LoadWith<T> method will provide you with immediate loading of related data to the main target in one queue. 

I have modified your original code assuming there are associations between Product and PriceLists and Product and ProductGalleries. See the code how the navigation properties that implement these associations are used. I have also defined a fetch strategy that instructs OpenAccess to load all required data in just two queries and that will improve the performance of your code tremendously.
Here is the modified code that uses Fetch Strategy and navigation properties:

01.FetchStrategy fetchStrategy = new FetchStrategy();
02.fetchStrategy.LoadWith<Product>(c => c.Prices); //refers to the data found in dbContext.PriceLists
03.fetchStrategy.LoadWith<Product>(c => c.Gallery); //refers to the data found in dbContext.ProductGalleries
04.dbContext.FetchStrategy = fetchStrategy;
05.
06.var plist = dbContext.Products.Where(x => x.Category == cid && x.To_display == true);
07.
08.foreach (Product prod in plist)
09.{
10.    ProductDM temp = new ProductDM();
11.    temp.ProductName = prod.Name_en;
12.    temp.ProductID = prod.Product_id;
13.    temp.Detail = prod.Detail_en;
14.
15.    // original code
16.    //var prlist = dbContext.PriceLists.Where(x => x.Product_id == prod.Product_id);
17.    // prices are already loaded using the fetch strategy
18.    // and they are associated with the right Product instance
19.    var prlist = product.Prices;
20.    temp.PriceList = new Hashtable();
21.    foreach (PriceList pl in prlist)
22.    {
23.        temp.PriceList.Add(pl.Specification, pl.Unit_price);
24.    }
25.
26.    // original code
27.    //var pimgs = dbContext.ProductGalleries.FirstOrDefault(x => x.Product_id == prod.Product_id);
28.    // gallery is already loaded using the fetch strategy
29.    // and it is associated with the right Product instance
30.    var pimgs = product.Gallery;
31.    //should we check if pimgs == null?
32.    temp.SmallImage = pimgs.Summary;
33.    temp.BigImage = pimgs.Image1;
34.
35.    productList.Add(temp);
36.}

If you don't have associations between tables, you can not use Fetch Plans. First you should create associations, and after that use Fetch Strategy class.

If you need any further assistance do not hesitate to contact us.
 
Kind regards,
Boris Georgiev
the Telerik team

[Abdel]

Thanks Boris, I am sure this will reduce this time. I have associations already in place.
I would like to know if I can put filter into the fetch strategy. for example can I write:

fetchStrategy.LoadWith<Product>(c => c.Prices.Where(x=> x.specification == 'X'));

Is this valid?

[Boris Georgiev]

Hello Abdel,

I am glad that I have helped you. Unfortunately you can not use lambda expressions into the fetch strategy. This is a design decision that we have taken. The argument in LoadWith<T>() and LoadWith() should be а member, rather than code for execution or filtering. 

If any other questions arise, do not hesitate to contact us.
  
Kind regards,
Boris Georgiev
the Telerik team