为报表模块优化LINQ TO SQL (目前需要2-3分钟)？

Question

我正在查询报表模块以显示数据透视表，其中一些列是动态的，所以我创建了一个字典对象来构造我的对象，所以假设我有一个包含大约40k条记录的user_applications表，并且这个表也被其他表引用。

我这样做：

//masters
            var master_genders = CTX.translate_value_ms.Where(a => a.PSF_type == "SEX").OrderBy(o => o.translate_value_id).ToList();
            var master_edus = CTX.education_lvl_ms.OrderBy(o => o.education_lvl_id).ToList();
            var master_stats = CTX.app_status_ms.OrderBy(o => o.status_order).ToList();
            var master_criteria = CTX.criteria_suggestion_templates.OrderBy(o => o.criteria_suggestion_template_id).ToList();
            //

            //load mapping
            Dictionary<int, int> Gender_Map = new Dictionary<int, int>();
            int start = 0;
            foreach (var m in master_genders)
            {
                Gender_Map.Add(m.translate_value_id, start);
                start++;
            }

            Dictionary<int, int> Edu_Map = new Dictionary<int, int>();
            start = 0;
            foreach (var e in master_edus)
            {
                Edu_Map.Add(e.education_lvl_id, start);
                start++;
            }

            Dictionary<string, int> Stat_Map = new Dictionary<string, int>();
            start = 0;

            var first = master_stats.GroupBy(g => g.status_web).OrderBy(o => o.FirstOrDefault().status_order);

            foreach (var s in first)
            {
                Stat_Map.Add(s.Key, start);
                start++;
            }

            Dictionary<int, int> Criteria_Map = new Dictionary<int, int>();
            start = 0;

            foreach (var m in master_criteria)
            {
                Criteria_Map.Add(m.criteria_suggestion_template_id, start);
                start++;
            }
            //

            var idx = 0;
            var cur_age = 0;
            int int_jobfair = 0;
            int total = 0;

            user_address address_dom = null;
            string key = "";
            bool check = false;
            ReportSubObject obj = null;
            user_edu user_last_edu = null;
            user_test last_test = null;
            criteria_suggestion checkcrit = null;

            var usertest = CTX.user_tests;
            var testcriteria = CTX.criteria_suggestions;

            double total_a = 0;
            double total_b = 0;
            double total_c = 0;
            double total_d = 0;
            double total_e = 0;
            double total_f = 0;

            var addresses = CTX.user_addresses.Where(a => a.address_type == 0).ToList();

            foreach (var itm in apps.ToList())
            {
                var a_begin = DateTime.Now;
                total = 0;
                address_dom = null;
                key = "";
                check = false;
                obj = null;
                user_last_edu = null;
                last_test = null;
                checkcrit = null;

                address_dom = addresses.Where(a => a.user_id == itm.user_id).FirstOrDefault();
                var a_end = DateTime.Now;

                DateTime b_begin = DateTime.Now;
                DateTime b_end = DateTime.Now;

                DateTime c_begin = DateTime.Now;
                DateTime c_end = DateTime.Now;

                DateTime d_begin = DateTime.Now;
                DateTime d_end = DateTime.Now;

                DateTime e_begin = DateTime.Now;
                DateTime e_end = DateTime.Now;

                DateTime f_begin = DateTime.Now;
                DateTime f_end = DateTime.Now;

                b_begin = DateTime.Now;
                int_jobfair = 0;

                if (itm.manager_id != null)
                    int_jobfair = 1;

                key = int_jobfair + " - " + itm.applied_date.Value.Month + " - " + itm.posting.job_id + " - " + itm.posting.location_id + " - " + itm.posting.job_cat_id + " - " + itm.posting.office_cat_id + " - " + (address_dom != null && address_dom.state_id != null ? address_dom.state_id.ToString() : "0");

                check = SubmissionObj.ContainsKey(key);

                if (!check)
                {
                    obj = new ReportSubObject();
                    obj.Source = (itm.manager_id != null ? "Job Fair" : "Website");
                    obj.Job = myTI.ToTitleCase(itm.posting.job_m.PSF_Desc.ToLower());
                    obj.Location = myTI.ToTitleCase(itm.posting.job_location_m.PSF_Desc.ToLower());
                    obj.JobCat = myTI.ToTitleCase(itm.posting.job_category_m.PSF_Desc.ToLower());
                    obj.OfficeCat = myTI.ToTitleCase(itm.posting.office_category_m.PSF_Desc.ToLower());
                    obj.ApplyProv = (address_dom != null && address_dom.state_id != null ? address_dom.state_m.state_desc : "");


                    obj.from = (DateTime)itm.applied_date;

                    obj.Total = total += 1;

                    #region gender

                    obj.KeyPairGender = new List<int>();
                    master_genders.ForEach(b => obj.KeyPairGender.Add(0));

                    if (itm.user_list.gender_id != null)
                    {
                        idx = Gender_Map[(int)itm.user_list.gender_id];

                        //obj.KeyPairGender[idx] = new KeyValuePair<string, int>(obj.KeyPairGender[idx].Key, obj.KeyPairGender[idx].Value + 1);
                        obj.KeyPairGender[idx] += 1;
                    }
                    #endregion

                    #region edu

                    obj.KeyPairEdu = new List<int>();
                    master_edus.ForEach(b => obj.KeyPairEdu.Add(0));

                    user_last_edu = itm.user_list.user_edus.OrderByDescending(o => o.edu_lvl_id).FirstOrDefault();
                    if (user_last_edu != null)
                    {
                        idx = Edu_Map[(int)user_last_edu.edu_lvl_id];
                        obj.KeyPairEdu[idx] += 1;
                    }
                    #endregion

                    b_end = DateTime.Now;

                    c_begin = DateTime.Now;
                    #region Age
                    obj.AgeRange = new int[5];
                    for (int i = 0; i < 5; i++)
                    {
                        obj.AgeRange[i] = 0;
                    }

                    cur_age = itm.applied_date.Value.Year - itm.user_list.birthday.Value.Year;
                    if (itm.user_list.birthday > ((DateTime)itm.applied_date).AddYears(-cur_age)) cur_age--;

                    if (cur_age >= 18 && cur_age <= 21)
                        obj.AgeRange[0] += 1;
                    else if (cur_age >= 22 && cur_age <= 24)
                        obj.AgeRange[1] += 1;
                    else if (cur_age >= 25 && cur_age <= 27)
                        obj.AgeRange[2] += 1;
                    else if (cur_age >= 28 && cur_age <= 30)
                        obj.AgeRange[3] += 1;
                    else if (cur_age > 30)
                        obj.AgeRange[4] += 1;

                    #endregion

                    #region appstatus

                    obj.KeyPairAppStat = new List<int>();
                    foreach (var loop in first)
                    {
                        obj.KeyPairAppStat.Add(0);
                    }

                    if (itm.app_status_id != null)
                    {
                        idx = Stat_Map[itm.app_status_m.status_web];

                        obj.KeyPairAppStat[idx] += 1;
                    }
                    #endregion

                    #region criteria

                    obj.KeyPairCriteria = new List<int>();
                    master_criteria.ForEach(b => obj.KeyPairCriteria.Add(0));

                    if (itm.online_test_id != null)
                    {
                        last_test = usertest.Where(a => a.user_id == itm.user_id && a.package_id == itm.online_test.package_id).OrderByDescending(o => o.date_score).FirstOrDefault();
                        if (last_test != null)
                        {
                            if (last_test.total_score != null)
                            {
                                checkcrit = testcriteria.Where(a => a.package_id == last_test.package_id &&
                                                                                    (last_test.total_score >= a.score_from && last_test.total_score <= a.score_to)
                                                                              ).FirstOrDefault();
                                if (checkcrit != null)
                                {
                                    idx = Criteria_Map[(int)checkcrit.criteria_opt_id];

                                    obj.KeyPairCriteria[idx] += 1;
                                }
                            }
                        }
                    }
                    #endregion

                    SubmissionObj.Add(key, obj);

                    c_end = DateTime.Now;
                }
                else
                {
                    d_begin = DateTime.Now;

                    var tmp = SubmissionObj[key];

                    tmp.Total++;

                    if (tmp.to < itm.applied_date)
                        tmp.to = (DateTime)itm.applied_date;

                    #region gender
                    if (itm.user_list.gender_id != null)
                    {
                        idx = Gender_Map[(int)itm.user_list.gender_id];
                        tmp.KeyPairGender[idx] += 1;
                    }
                    #endregion
                    d_end = DateTime.Now;

                    e_begin = DateTime.Now;
                    #region edu
                    user_last_edu = itm.user_list.user_edus.OrderByDescending(o => o.edu_lvl_id).FirstOrDefault();
                    if (user_last_edu != null)
                    {
                        idx = Edu_Map[(int)user_last_edu.edu_lvl_id];
                        tmp.KeyPairEdu[idx] += 1;
                    }
                    #endregion




                    #region age
                    cur_age = itm.applied_date.Value.Year - itm.user_list.birthday.Value.Year;
                    if (itm.user_list.birthday > ((DateTime)itm.applied_date).AddYears(-cur_age)) cur_age--;

                    if (cur_age >= 18 && cur_age <= 21)
                        tmp.AgeRange[0] += 1;
                    else if (cur_age >= 22 && cur_age <= 24)
                        tmp.AgeRange[1] += 1;
                    else if (cur_age >= 25 && cur_age <= 27)
                        tmp.AgeRange[2] += 1;
                    else if (cur_age >= 28 && cur_age <= 30)
                        tmp.AgeRange[3] += 1;
                    else if (cur_age > 30)
                        tmp.AgeRange[4] += 1;
                    #endregion

                    #region appstatus
                    if (itm.app_status_id != null)
                    {
                        idx = Stat_Map[itm.app_status_m.status_web];

                        tmp.KeyPairAppStat[idx] += 1;
                    }
                    #endregion

                    e_end = DateTime.Now;

                    f_begin = DateTime.Now;

                    #region criteria
                    if (itm.online_test_id != null)
                    {
                        last_test = usertest.Where(a => a.user_id == itm.user_id && a.package_id == itm.online_test.package_id).OrderByDescending(o => o.date_score).FirstOrDefault();
                        if (last_test != null)
                        {
                            if (last_test.date_score != null)
                            {
                                checkcrit = testcriteria.Where(a => a.package_id == last_test.package_id &&
                                                                                    (last_test.total_score >= a.score_from && last_test.total_score <= a.score_to)
                                                                              ).FirstOrDefault();
                                if (checkcrit != null)
                                {
                                    idx = Criteria_Map[(int)checkcrit.criteria_opt_id];

                                    tmp.KeyPairCriteria[idx] += 1;
                                }
                            }
                        }
                    }
                    #endregion
                    f_end = DateTime.Now;

                }

                total_a += (a_end - a_begin).TotalSeconds;
                total_b += (b_end - b_begin).TotalSeconds;
                total_c += (c_end - c_begin).TotalSeconds;
                total_d += (d_end - d_begin).TotalSeconds;
                total_e += (e_end - e_begin).TotalSeconds;
                total_f += (f_end - f_begin).TotalSeconds;
            }

正如你在上面的代码中注意到的，我在foreach循环中有几个子查询，我放了几个变量来计算哪个部分花费了最多的时间，这是事实，子查询部分花费了最长的时间，总体上这些代码大约需要2分钟才能执行，所以我尝试将所有子查询移到foreach循环之外，我这样做：

     var customapp = apps.Select(x => new
            {
                dom_address = x.user_list.user_addresses.Where(a => a.address_type == 0).FirstOrDefault(),
                manager_id = x.manager_id,
                applied_date = x.applied_date,

                job_id = x.posting.job_id,
                job_desc = x.posting.job_m.PSF_Desc,

                location_id = x.posting.location_id,
                loc_desc = x.posting.job_location_m.PSF_Desc,

                job_cat_id = x.posting.job_cat_id,
                job_cat_desc = x.posting.job_category_m.PSF_Desc,

                office_cat_id = x.posting.office_cat_id,
                office_cat_desc = x.posting.office_category_m.PSF_Desc,

                gender_id = x.user_list.gender_id,
                birthday = x.user_list.birthday,

                edu = x.user_list.user_edus.OrderByDescending(o => o.edu_lvl_id).FirstOrDefault(),

                status_PSF_id = x.app_status_id,
                status_from_PSF = x.app_status_m.status_web,

                online_test_id = x.online_test_id,

                last_test = x.user_tests.Where(a => a.user_id == x.user_id && a.package_id == x.online_test.package_id).OrderByDescending(o => o.date_score).FirstOrDefault()
            });


            DateTime end_z = DateTime.Now;

            double total_z = (end_z - begin_z).TotalSeconds;

            foreach (var itm in customapp.ToList())
            {
                //logic to construct my dictionary as before, but without sub queries.
             }

第二个代码没有提高速度，相反，它比前一个代码花了更长的时间，你能告诉我我可以做些什么来优化我的查询吗？

Answer 1

问题是您正在为每个应用程序的每个子查询执行额外的db调用。

这是你可以避免它的方法：

// Plain query, not doing subqueries. Unfortunately brings extra rows in memory.
var q =
    from app in ctx.Applications
    join s in ctx.SubObjectsOne on app.Id equals s.AppId into joinedSubObjectOne
    from subObjectOne in joinedSubObjectOne.DefaultIfEmpty()
    where subObjectOne.SomeProperty == 0 // additional sub object criteria
    select new { app, subObjectOne };

var l = q.ToList();

// From now on objects are materialized, doing the rest in memory:
var translated =
    from i in l
    group i by i.app.Id into g
    select new
    {
        app = g.First().app,

        // This is expensive too, but not doing separate db requests.
        SubObjectOneByMaxProperty = g.Select(i => i.subObjectOne).OrderByDescending(s => s.SomeOrder).FirstOrDefault()
    };

var translatedList = translated.ToList();

如果您的子对象计数不是很高，则可以使用此方法。

如果您能弄清楚如何使用sql聚合计算所需的值，那就更好了。例如，subObjects.Where(..).OrderByDescencing(..).First(..)如果您只需要一个最大值，而不是整个对象，则会转换为subObjects.Max(s=>s.SomeProp)。它将直接在db中计算，而不需要额外的db调用。

更好的方法是避免在生成报表时进行计算。您可以稍微反规范化您的数据，例如-将所需对象的ids存储在聚合表中，并在相关数据发生变化时更新表：

table aggregated_data(app_id, address_id, max_edu_lvl_id, max_score_test_id)