c# - C#试图加快数据库查询
问题描述
我试图让这段代码更快，它查询一个包含 200,000 多条记录的数据库。
这段代码执行大约需要 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 框架部分需要另外 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);
谁能看到我可以减少这个的方法？谢谢
解决方案
推荐阅读
- javascript - 循环内的 JS 循环
- php - 致命错误的替代方法是什么：未捕获的错误：调用未定义的方法 MongoDB\Driver\Cursor::hasNext？
- whatsapp - 在 WhatsApp Business API 上跟踪归因？
- excel - 使 VBA 循环更快的想法？
- python - 如何使用 Beautiful soup 从 URL 页面抓取 HTML 表格并将其写入 CSV
- java - 如何使用 Spring Cloud Stream 将 Spring Boot 应用程序集成到 Bluemix Cloud 上的 IBM 事件流
- python - 在 OpenCV 中从 ROI 裁剪矩形扩展
- c++ - 添加单元格后更新 UnstructredGrid (VTK C++)
- sql-server - 如何将 debezium 与视图一起使用，而不是与 sql server 连接的表
- python - matplotlib 图中缺少 x 轴标签