查看: 1806|回复: 0

[.NET源码] 程序部署到服务器后非常慢!!!

发表于 2017-12-6 08:00:04

结论:

1、EF 查询 比ADO慢,甚至直接报告超时错误,原因不详。

2、在原生ADO.Net中 使用 参数化查询 比 直接使用sql拼接 慢几十倍!!!

ADO.Net代码测试

  1. public List<v_yjdateggjgModel> SelectList(int yjxzqid, int ncpid, DateTime start, DateTime end)
  2. {
  3. List<v_yjdateggjgModel> list = new List<v_yjdateggjgModel>();<br>       //第一条sql,采用参数化查询 用时36秒 查询1100条数据
  4. // string sql = "select yjxzqid,ncpid,rq,sjttjg,sjpfjg,sjlsjg,ycttjg,ycpfjg,yclsjg from v_yjdateggjg where yjxzqid=@yjxzqid and ncpid=@ncpid and (rq>=@start and rq<=@end)";<br>       //第二条 sql,采用 sql拼接 用时1.5秒 查询 1100数据
  5. string sql = "select yjxzqid,ncpid,rq,sjttjg,sjpfjg,sjlsjg,ycttjg,ycpfjg,yclsjg from v_yjdateggjg where yjxzqid = "+ yjxzqid + " and ncpid = "+ ncpid +
  6. " and (rq >= '"+start.ToString("yyyy-MM-dd")+ "' and rq <= '" + end.ToString("yyyy-MM-dd") + "')";
  7. //SqlParameter[] sqlparms = new SqlParameter[]
  8. //{
  9. // new SqlParameter("@ncpid",ncpid) ,
  10. // new SqlParameter("@yjxzqid",yjxzqid),
  11. // new SqlParameter("@start",start),
  12. // new SqlParameter("@end",end),
  13. //};
  14. using (SqlDataReader reader = SqlHelper.ExecuteReader(sql))
  15. {
  16. if (reader.HasRows)
  17. {
  18. while (reader.Read())
  19. {
  20. v_yjdateggjgModel info = new v_yjdateggjgModel();
  21. info.yjxzqid = (int)SqlHelper.FromDbNull(reader["yjxzqid"]);
  22. info.ncpid = (int)SqlHelper.FromDbNull(reader["ncpid"]);
  23. info.rq = (DateTime)SqlHelper.FromDbNull(reader["rq"]);
  24. info.sjttjg = (decimal?)SqlHelper.FromDbNull(reader["sjttjg"]);
  25. info.sjpfjg = (decimal?)SqlHelper.FromDbNull(reader["sjpfjg"]);
  26. info.sjlsjg = (decimal?)SqlHelper.FromDbNull(reader["sjlsjg"]);
  27. info.ycttjg = (decimal?)SqlHelper.FromDbNull(reader["ycttjg"]);
  28. info.ycpfjg = (decimal?)SqlHelper.FromDbNull(reader["ycpfjg"]);
  29. info.yclsjg = (decimal?)SqlHelper.FromDbNull(reader["yclsjg"]);
  30. list.Add(info);
  31. }
  32. }
  33. }
  34. return list;
  35. }<br><br>
复制代码

EF代码(已舍弃)

  1. // EF 查询方式一
  2. // var data1 = db.v_yjdateggjg.Where(d => d.yjxzqid == yjxzqid && d.ncpid == ncpid && (d.rq >= start && d.rq <= end)).ToList();
  3. // EF 查询方式二
  4. // string sql = "select * from v_yjdateggjg where yjxzqid=@yjxzqid and ncpid=@ncpid and (rq>=@start and rq<=@end)";
  5. // var sqlparms = new SqlParameter[] {
  6. // new SqlParameter("@ncpid",ncpid),
  7. // new SqlParameter("@yjxzqid",yjxzqid),
  8. // new SqlParameter("@start",start),
  9. // new SqlParameter("@end",end),
  10. //};
  11. // var data1 = db.Database.SqlQuery<v_yjdateggjg>(sql, sqlparms).ToList();
复制代码

  

数据库内部测试

  1. //sql 参数化查询 1100条数据 3秒<br>declare @yjxzqid int =9;
  2. declare @ncpid int= 35;
  3. declare @start datetime = '2014-5-1';
  4. declare @end datetime='2017-5-1';
  5. select yjxzqid,ncpid,rq,sjttjg,sjpfjg,sjlsjg,ycttjg,ycpfjg,yclsjg
  6. from v_yjdateggjg
  7. where yjxzqid=@yjxzqid and ncpid=@ncpid and (rq>=@start and rq<=@end)
  8. // sql拼接 1100条 1秒
  9. select yjxzqid,ncpid,rq,sjttjg,sjpfjg,sjlsjg,ycttjg,ycpfjg,yclsjg from v_yjdateggjg where yjxzqid=9 and ncpid=35 and (rq>'2014-5-1' and rq<'2017-5-1')
复制代码

  

  1. v_yjdateggjg 视图内部sql语句
复制代码
  1. SELECT dbo.v_yjdatejg.yjxzqid, dbo.datalocation.cjdd AS yjxzqname, dbo.v_yjdatejg.ncpid, dbo.products.ncpmc,
  2. dbo.v_yjdatejg.rq, dbo.v_yjdatealljg.ttjg AS sjttjg, dbo.v_yjdatealljg.pfjg AS sjpfjg, dbo.v_yjdatealljg.lsjg AS sjlsjg,
  3. dbo.v_yjdateycjg.ttjg AS ycttjg, dbo.v_yjdateycjg.pfjg AS ycpfjg, dbo.v_yjdateycjg.lsjg AS yclsjg
  4. FROM dbo.v_yjdatejg INNER JOIN
  5. dbo.datalocation ON dbo.v_yjdatejg.yjxzqid = dbo.datalocation.id INNER JOIN
  6. dbo.products ON dbo.v_yjdatejg.ncpid = dbo.products.id LEFT OUTER JOIN
  7. dbo.v_yjdateycjg ON dbo.v_yjdatejg.yjxzqid = dbo.v_yjdateycjg.yjxzqid AND
  8. dbo.v_yjdatejg.ncpid = dbo.v_yjdateycjg.ncpid AND dbo.v_yjdatejg.rq = dbo.v_yjdateycjg.ycrq LEFT OUTER JOIN
  9. dbo.v_yjdatealljg ON dbo.v_yjdatejg.yjxzqid = dbo.v_yjdatealljg.yjxzqid AND
  10. dbo.v_yjdatejg.ncpid = dbo.v_yjdatealljg.ncpid AND dbo.v_yjdatejg.rq = dbo.v_yjdatealljg.cjrq
复制代码

 

sqlHelper类

  1. public static class SqlHelper
  2. {
  3. private static readonly string conStr = ConfigurationManager.ConnectionStrings["lyc2ConnString"].ConnectionString;
  4. //insert delete update
  5. public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
  6. {
  7. using (SqlConnection con = new SqlConnection(conStr))
  8. {
  9. using (SqlCommand cmd = new SqlCommand(sql, con))
  10. {
  11. if (pms != null)
  12. {
  13. cmd.Parameters.AddRange(pms);
  14. }
  15. con.Open();
  16. return cmd.ExecuteNonQuery();
  17. }
  18. }
  19. }
  20. //返回单个值
  21. public static object ExecuteScalar(string sql, params SqlParameter[] pms)
  22. {
  23. using (SqlConnection con = new SqlConnection(conStr))
  24. {
  25. using (SqlCommand cmd = new SqlCommand(sql, con))
  26. {
  27. if (pms != null)
  28. {
  29. cmd.Parameters.AddRange(pms);
  30. }
  31. con.Open();
  32. return cmd.ExecuteScalar();
  33. }
  34. }
  35. }
  36. //执行返回DataReader
  37. public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
  38. {
  39. SqlConnection con = new SqlConnection(conStr);
  40. using (SqlCommand cmd = new SqlCommand(sql, con))
  41. {
  42. if (pms != null)
  43. {
  44. cmd.Parameters.AddRange(pms);
  45. }
  46. //con.Open();
  47. try
  48. {
  49. if (con.State == ConnectionState.Closed)
  50. {
  51. con.Open();
  52. }
  53. return cmd.ExecuteReader(CommandBehavior.CloseConnection);
  54. }
  55. catch
  56. {
  57. con.Close();
  58. con.Dispose();
  59. throw;
  60. }
  61. }
  62. }
  63. //查询多行
  64. public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
  65. {
  66. DataTable dt = new DataTable();
  67. using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
  68. {
  69. if (pms != null)
  70. {
  71. adapter.SelectCommand.Parameters.AddRange(pms);
  72. }
  73. adapter.Fill(dt);
  74. }
  75. return dt;
  76. }
  77. /// <summary>
  78. /// 将DbNull转换成null
  79. /// </summary>
  80. /// <param name="obj"></param>
  81. /// <returns></returns>
  82. public static object FromDbNull(object obj)
  83. {
  84. if (obj == DBNull.Value)
  85. {
  86. return null;
  87. }
  88. else
  89. {
  90. return obj;
  91. }
  92. }
  93. /// <summary>
  94. /// 将null转换成DbNull
  95. /// </summary>
  96. /// <param name="obj"></param>
  97. /// <returns></returns>
  98. public static object ToDbNull(object obj)
  99. {
  100. if (obj == null)
  101. {
  102. return DBNull.Value;
  103. }
  104. else
  105. {
  106. return obj;
  107. }
  108. }
  109. }
  110. }
复制代码

博客园非常蛋疼的说:字数少有150字不能发布的首页。

答:亲,代码不算数吗?看来博客园的管理已经渐渐思维固话了,越来越像写中学生作文规范。



回复

使用道具 举报