Optimizing querying performance

4 posts, 1 answers
  1. Abdel
    Abdel avatar
    4 posts
    Member since:
    Sep 2012

    Posted 02 Jan 2013 Link to this post

    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?



  2. Answer
    Boris Georgiev
    Admin
    Boris Georgiev avatar
    190 posts

    Posted 04 Jan 2013 Link to this post

    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
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
  3. DevCraft banner
  4. Abdel
    Abdel avatar
    4 posts
    Member since:
    Sep 2012

    Posted 05 Jan 2013 Link to this post

    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?

  5. Boris Georgiev
    Admin
    Boris Georgiev avatar
    190 posts

    Posted 07 Jan 2013 Link to this post

    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
    Q3'12 SP1 of OpenAccess ORM packs Multi-Table Entities mapping support. Check it out.
Back to Top