我试图让这段代码更快，它查询的数据库中有200000多条记录。
这段代码大约需要10秒来执行，我正在寻找任何帮助试图削减它，看看我哪里出错了。
我已经尝试了很多方法，现在我将展示这些方法。
我试过mysql命令
using (MySqlConnection con = new MySqlConnection(connectionstring))
{
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = con;
con.Open();
var daysago = DateTime.Now.AddDays(-7);
cmd.CommandText = @"SELECT job_ref,UniqNo,volumes_env,postout_deadline from jobs where recieved_data >= """ + daysago.ToString("yyyy-MM-dd HH:mm:ss") + @""" and lsm_status is null and despatched_time is null";
var reader = cmd.ExecuteReader();
int x = 0;
while (reader.Read())
{
jobrefss.Add(reader["job_ref"].ToString());
Tester.Add(new SpecialClassTest { job_ref = reader["job_ref"].ToString(),
UniqNo = reader["UniqNo"].ToString(),
volumes_env = (int)reader["volumes_env"],
postout_deadline = (DateTime)reader["postout_deadline"] });
}
jobrefss = jobrefss.Distinct().ToList();
jobrefss = jobrefss.Where(z => !z.ToUpper().Contains("C4") &&
!z.ToUpper().Contains("ARC") &&
!z.ToUpper().Contains("EMAIL") &&
!z.ToUpper().Contains("EBILL") &&
!z.ToUpper().Contains("POD") &&
!z.ToUpper().Contains("GALLAHER") &&
!z.ToUpper().Contains("SCAN") &&
!z.ToUpper().Contains("ARCHIVE") &&
!z.ToUpper().Contains("SHELL") &&
!z.ToUpper().Contains("IRISH") &&
!z.ToUpper().Contains("INTER") &&
!z.ToUpper().Contains("BREAK") &&
!z.ToUpper().Contains("1ST") &&
!z.ToUpper().Contains("HAND") &&
z != "AAH_Monthly_StatColDup_Cut" &&
z != "APH_Inv_Cut" &&
z != "APH_Stat_Cut" &&
z != "BSS_BuckHickConsInv_Cut" &&
z != "BSS_BuckHickInv_Cut" &&
z != "BSS_BuckHickStat_Cut" &&
z != "Marstons_Inv_Cut" &&
z != "RexelGroup_M3Inv_Cut" &&
z != "RexelGroup_M3Stat_Cut" &&
z != "Schneider_Inv_Cut" &&
z != "Schneider_Stats_Cut" &&
z != "XLN_InvC3_ColDup_Cut" &&
z != "XLN_ManualCOPYInvoices_Cut" &&
z != "XLN_ManCOPYInv_ColDup_Cut" &&
z != "AAH_Mon_EntStColDup_Cut_HAN" &&
z != "OBT_Inv_Cut" &&
z != "XLN_InvC7_ColDup_Cut" &&
z != "Marstons_MBCStat_Cut" &&
z != "XLN_GiroC7_ColDup_Cut"
).ToList();
}我试过使用实体框架
var jobRefs = context.jobs.Where(j => j.LSM_Status == null &&
j.despatched_time == null &&
!j.job_ref.Contains("C4") &&
!j.job_ref.Contains("Arc") &&
!j.job_ref.Contains("Email") &&
!j.job_ref.Contains("Ebill") &&
!j.job_ref.Contains("POD") &&
!j.job_ref.Contains("Gallaher") &&
!j.job_ref.Contains("scan") &&
!j.job_ref.Contains("Archive") &&
!j.job_ref.Contains("shell") &&
!j.job_ref.Contains("irish") &&
!j.job_ref.Contains("inter") &&
!j.job_ref.Contains("break") &&
!j.job_ref.Contains("1st") &&
!j.job_ref.Contains("hand") &&
!j.job_ref.Contains("Hand") &&
j.job_ref != "AAH_Monthly_StatColDup_Cut" &&
j.job_ref != "APH_Inv_Cut" &&
j.job_ref != "APH_Stat_Cut" &&
j.job_ref != "BSS_BuckHickConsInv_Cut" &&
j.job_ref != "BSS_BuckHickInv_Cut" &&
j.job_ref != "BSS_BuckHickStat_Cut" &&
j.job_ref != "Marstons_Inv_Cut" &&
j.job_ref != "RexelGroup_M3Inv_Cut" &&
j.job_ref != "RexelGroup_M3Stat_Cut" &&
j.job_ref != "Schneider_Inv_Cut" &&
j.job_ref != "Schneider_Stats_Cut" &&
j.job_ref != "XLN_InvC3_ColDup_Cut" &&
j.job_ref != "XLN_ManualCOPYInvoices_Cut" &&
j.job_ref != "XLN_ManCOPYInv_ColDup_Cut" &&
j.job_ref != "AAH_Mon_EntStColDup_Cut_HAN" &&
j.job_ref != "OBT_Inv_Cut" &&
j.job_ref != "XLN_InvC7_ColDup_Cut" &&
j.job_ref != "Marstons_MBCStat_Cut" &&
j.job_ref != "XLN_GiroC7_ColDup_Cut"
);
var jobrefss = jobRefs.Select(z => z.job_ref);但这仍然需要很长时间。
（以下代码在之后执行）
var UpdatedRefs = context.customerslas.Where(c => jobrefss.Contains(c.job_ref) &&
(c.invoiced == 1 || c.invoiced == 2) &&
c.active == 1)
.Select(c => c.job_ref)
.ToList();
var FinalRefs = Tester.Where(f => UpdatedRefs.Contains(f.job_ref))
.OrderBy(f => f.job_ref)
.ToList();有人能看到我可以在哪里修剪一些脂肪，节省一些时间吗？
编辑
我在第一个sql查询中做了大量的工作，从而节省了一点时间。
cmd.CommandText = @"SELECT job_ref,UniqNo,volumes_env,postout_deadline from jobs where recieved_data >= """ + daysago.ToString("yyyy-MM-dd HH:mm:ss") + @""" and lsm_status is null and despatched_time is null and job_ref not like '%c4%' and job_ref not like '%Arc%' and job_ref not like '%email%' and job_ref not like '%ebill%' and job_ref not like '%pod%' and job_ref not like '%gallaher%' and job_ref not like '%Scan%' and job_ref not like '%Archive%' and job_ref not like '%shell%' and job_ref not like '%irish%' and job_ref not like '%inter%' and job_ref not like '%break%' and job_ref not like '%1st%' and job_ref not like '%hand%' and job_ref != 'AAH_Monthly_StatColDup_Cut' and job_ref != 'APH_Inv_Cut' and job_ref != 'APH_Stat_Cut' and job_ref != 'BSS_BuckHickConsInv_Cut' and job_ref != 'BSS_BuckHickInv_Cut' and job_ref != 'BSS_BuckHickStat_Cut' and job_ref != 'Marstons_Inv_Cut' and job_ref != 'RexelGroup_M3Inv_Cut' and job_ref != 'RexelGroup_M3Stat_Cut' and job_ref != 'Schneider_Inv_Cut' and job_ref != 'Schneider_Stats_Cut' and job_ref != 'XLN_InvC3_ColDup_Cut' and job_ref != 'XLN_ManualCOPYInvoices_Cut' and job_ref != 'XLN_ManCOPYInv_ColDup_Cut' and job_ref != 'AAH_Mon_EntStColDup_Cut_HAN' and job_ref != 'OBT_Inv_Cut' and job_ref != 'XLN_InvC7_ColDup_Cut' and job_ref != 'Marstons_MBCStat_Cut' and job_ref != 'XLN_GiroC7_ColDup_Cut'";这个大命令现在只需要一秒钟，而不是几秒钟，现在的问题是我的linq/entity framework部分还需要4-5秒钟才能运行
var UpdatedRefs = context.customerslas.Where(c => jobrefs2s.Contains(c.job_ref) &&
(c.invoiced == 1 ||
c.invoiced == 2) &&
c.active == 1)
.Select(c => c.job_ref);
var FinalRefs = Tester.Where(f => UpdatedRefs.Contains(f.job_ref))
.OrderBy(f => f.job_ref);有人能想出办法让我把它删掉吗？谢谢
暂无答案！
目前还没有任何答案，快来回答吧！