Performance Issue

7 years ago
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.

  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
She talks about perf most notably the caching of queries.