Performance Issue

7 years ago
Hi
I'm developing in NopCommerce. I have notice of a prformance issue in quering database. I produced a simple test with isolation of services layer on Nop.Services.

[Test]
  public void Should_Get_All_Categories()
  {
            _engine = new NopEngine();
            var categoryService = _engine.Resolve<ICategoryService>();
            var allCategory = categoryService.GetAllCategories();
            Assert.That(allCategory.Count > 0);
   }

all works, but the call of  categoryService.GetAllCategories() at the first time is very very slow. In particular the bottleneck is :

public virtual IList<Category> GetAllCategories(string categoryName, bool showHidden = false)
        {
            var query = _categoryRepository.Table;
            if (!showHidden)
                query = query.Where(c => c.Published); ------> This is very slow
            if (!String.IsNullOrWhiteSpace(categoryName))
                query = query.Where(c => c.Name.Contains(categoryName));
            query = query.Where(c => !c.Deleted);
            query = query.OrderBy(c => c.ParentCategoryId).ThenBy(c => c.DisplayOrder);
            var unsortedCategories = query.ToList();

            //sort categories
            var sortedCategories = unsortedCategories.SortCategoriesForTree();
            return sortedCategories;
        }

I think problem is EntityFramework. Any Solutions ?
Thanks in advance
7 years ago
If the performance bottleneck is indeed EF you can consider updating to EF 5. See article by Julie Lerman
http://msdn.microsoft.com/en-us/magazine/jj618295.aspx
She talks about perf most notably the caching of queries.