Foreach and querys performance improvement issue in C#

Question

I'm having a problem with performance in my code.

The method below is used to create a comparative score of companies from the whole country based some rules:

public List<object> GetCNAEBRCycleChart(int VisitId)
    {
        List<object> result = new List<object>();


        Visit visit = Context.Visit.Find(VisitId);
        Company company = visit.Company;
        var CNAE = company.MainEconomicCNAE.IdentifyCNAE;

        string[] Themes = new string[5];

        Themes[0] = "Finance";
        Themes[1] = "Market";
        Themes[2] = "Organization";
        Themes[3] = "Planning";
        Themes[4] = "People";

        int count = 0;

        List<Visit> listVisitCNAECountry = (from vis in Context.Visit
                                            where vis.Company.MainEconomicCNAE.IdentifyCNAE.StartsWith(CNAE)
                                                && vis.Order == 1
                                             select vis
                                                ).ToList();

        double[] Values = new double[5];

        Values[0] = 0;
        Values[1] = 0;
        Values[2] = 0;
        Values[3] = 0;
        Values[4] = 0;

        foreach (var vis in listVisitCNAECountry)
        {
            count = 0;
            var visitIdCompany = vis.Id;

            var diagnostic = Context.Visit.Find(visitIdCompany).Diagnostic;

            if (diagnostic != null)
            {
                foreach (var itemTheme in Themes)
                {
                    var TemaAux = itemTema;
                    int QtQuestion = (from itemForm in Context.FormItem
                                        join tipo in Context.FormItemType on itemForm.FormItemTypeId equals tipo.Id
                                        join itemForm2 in Context.FormItem on itemForm.FormItemParentId equals itemForm2.Id
                                        join itemForm3 in Context.FormItem on itemForm2.FormItemParentId equals itemForm3.Id
                                        where itemForm3.Name == TemaAux && tipo.Name == "Pergunta"
                                        select itemForm
                                              ).Count();

                    var sumAnswerCompany = (from alter in Context.Alternative
                                            join itemForm in Context.FormItem on alter.FormItemId equals itemForm.Id
                                            join itemForm2 in Context.FormItem on itemForm.FormItemParentId equals itemForm2.Id
                                            join itemForm3 in Context.FormItem on itemForm2.FormItemParentId equals itemForm3.Id
                                            join answer in Context.Answer on itemForm.Id equals answer.FormItemId
                                            where answer.AlternativeId == alter.Id &&
                                            answer.DiagnosticId == diagnostico.Id && itemForm3.Name == TemaAux

                                            select alter.Value
                                    ).AsEnumerable().Sum();

                    double scoreCompany = //Some calculations

                    Values[count] += scoreCompany;
                    count++;
                }
            }
        }

        count = 0;

        foreach (var val in Values)
        {
            Values[count] = //more calculations
            count++;
        }

        var model = new { NameCategory = "CNAE in Country", Value1 = Values[0], Value2 = Values[1], Value3 = Values[2], Value4 = Values[3], Value5 = Values[4] };

        result.Add(model);

        return result;
    }

The problem is that, with the actual CNAE, the list listVisitCNAECountry gets 16000+ elements, which make for terrible performance.

In my localhost environment it's taking 30min+ and I don't even know where to begin to actually improve the performance.

The biggest problem is that I really need all those iterations to make the calculations right.

If anyone has any ideas, please, help me.

Answer 1

The first thing to change is:

                var sumAnswerCompany = ( /* complex query */
                                ).AsEnumerable().Sum();

This is bad; instead of issuing select sum(...) as a database query, it instead will have to select the column(s) and return all the rows required, which may be a huge amount of bandwidth.

Instead, do the sum at the database and just bring back one number:

                var sumAnswerCompany = ( /* complex query */
                                ).Sum();

However, frankly I'd suggest writing the entire thing in raw SQL using joins and grouping from the original data. Sometimes LINQ isn't your best tool.