CODEWITHSUNDEEP
c#sql-server
Cihan Kalmaz
Cihan Kalmaz

Reputation: 959

C# & SQL Server : query takes long time

I have an ASP.NET MVC project and getting categories list and adding subcategories list to each category. My query takes long time. Thanks for answers. 

// Create Sub Cat List 
List<CategoryVM> catlist = (List<CategoryVM>)catServices.GetAll();

for (int i = 0; i < catlist.Count(); i++)
{
    List<SubCategoryVM> subCatById = subCatServices.GetAll().Where(x => x.Category_Id == catlist[i].Id).ToList();

    foreach (SubCategoryVM item in subCatById)
    {
        catlist[i].SubCategoryVM.Add(item);
    }
}

ViewData["CatAndSubcatList"] = catlist;

And my service code is: 

public IEnumerable<CategoryVM> GetAll()
{
    var data = ProjectMapper.ConvertToVMList<IEnumerable<CategoryVM>>(_CategoryRepository.GetAll());
    return (IEnumerable<CategoryVM>)data;
}

Upvotes: 0

Views: 1552

Answers (3)

Cetin Basoz
Cetin Basoz

Reputation: 23797

Instead of getting Categories and then adding the subcategories in a loop, use LINQ and get them in a single call. We don't have your model, so this sample is by guess:

var catlist = dbContext.CategoryVM.Include("SubCategoryVM").ToList();

This is your code edited. Instead of getting all Categories and then getting all subcategories again and again in a loop, it gets the lists from database just once and does the rest locally:

//Create Sub Cat List 
List<CategoryVM> catlist = (List<CategoryVM>)catServices.GetAll();
List<SubCategoryVM> subCats = subCatServices.GetAll();

for (int i = 0; i < catlist.Count(); i++)
{
    foreach (SubCategoryVM item in subCatById)
    {
        catlist[i].SubCategoryVM.AddRange(subCats.Where(x => x.Category_Id == catlist[i].Id));
    }
}

Upvotes: 1

Somendra Tiwari
Somendra Tiwari

Reputation: 58

This is loop issue. GetAll(): i think this method is getting all record then you filter from In-memory collection. Create generic method to execute query on server side.

Upvotes: -1

Sergei Zinovyev
Sergei Zinovyev

Reputation: 1286

You run sql inside loop so it may run 1000 times. That is why it is slow. I call it 1+N issue. Network connection (read Input/Output (IO) operations) is usually slow.

You need to change your code to get what you want from SQL Server in a 1 or 2 query (not N). Then you can have a loop to process your in memory data.

Upvotes: 1

Related Questions