結論： 1、EF 查詢 比ADO慢，甚至直接報告超時錯誤，原因不詳。 2、在原生ADO.Net中 使用 參數化查詢 比 直接使用sql拼接 慢幾十倍！！！ ADO.Net代碼測試 EF代碼（已捨棄） 資料庫內部測試 sqlHelper類 博客園非常蛋疼的說：字數少有150字不能發佈的首頁。 答：親， ...
結論：
1、EF 查詢 比ADO慢，甚至直接報告超時錯誤，原因不詳。
2、在原生ADO.Net中 使用 參數化查詢 比 直接使用sql拼接 慢幾十倍！！！
ADO.Net代碼測試
public List<v_yjdateggjgModel> SelectList(int yjxzqid, int ncpid, DateTime start, DateTime end)
{
List<v_yjdateggjgModel> list = new List<v_yjdateggjgModel>();
//第一條sql，採用參數化查詢 用時36秒 查詢1100條數據
// 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)";
//第二條 sql，採用 sql拼接 用時1.5秒 查詢 1100數據
string sql = "select yjxzqid,ncpid,rq,sjttjg,sjpfjg,sjlsjg,ycttjg,ycpfjg,yclsjg from v_yjdateggjg where yjxzqid = "+ yjxzqid + " and ncpid = "+ ncpid +
" and (rq >= '"+start.ToString("yyyy-MM-dd")+ "' and rq <= '" + end.ToString("yyyy-MM-dd") + "')";
//SqlParameter[] sqlparms = new SqlParameter[]
//{
// new SqlParameter("@ncpid",ncpid) ,
// new SqlParameter("@yjxzqid",yjxzqid),
// new SqlParameter("@start",start),
// new SqlParameter("@end",end),
//};
using (SqlDataReader reader = SqlHelper.ExecuteReader(sql))
{
if (reader.HasRows)
{
while (reader.Read())
{
v_yjdateggjgModel info = new v_yjdateggjgModel();
info.yjxzqid = (int)SqlHelper.FromDbNull(reader["yjxzqid"]);
info.ncpid = (int)SqlHelper.FromDbNull(reader["ncpid"]);
info.rq = (DateTime)SqlHelper.FromDbNull(reader["rq"]);
info.sjttjg = (decimal?)SqlHelper.FromDbNull(reader["sjttjg"]);
info.sjpfjg = (decimal?)SqlHelper.FromDbNull(reader["sjpfjg"]);
info.sjlsjg = (decimal?)SqlHelper.FromDbNull(reader["sjlsjg"]);
info.ycttjg = (decimal?)SqlHelper.FromDbNull(reader["ycttjg"]);
info.ycpfjg = (decimal?)SqlHelper.FromDbNull(reader["ycpfjg"]);
info.yclsjg = (decimal?)SqlHelper.FromDbNull(reader["yclsjg"]);
list.Add(info);
}
}
}
return list;
}
EF代碼（已捨棄）
// EF 查詢方式一
// var data1 = db.v_yjdateggjg.Where(d => d.yjxzqid == yjxzqid && d.ncpid == ncpid && (d.rq >= start && d.rq <= end)).ToList();
// EF 查詢方式二
// string sql = "select * from v_yjdateggjg where yjxzqid=@yjxzqid and ncpid=@ncpid and (rq>=@start and rq<=@end)";
// var sqlparms = new SqlParameter[] {
// new SqlParameter("@ncpid",ncpid),
// new SqlParameter("@yjxzqid",yjxzqid),
// new SqlParameter("@start",start),
// new SqlParameter("@end",end),
//};
// var data1 = db.Database.SqlQuery<v_yjdateggjg>(sql, sqlparms).ToList();
資料庫內部測試
//sql 參數化查詢 1100條數據 3秒
declare @yjxzqid int =9; declare @ncpid int= 35; declare @start datetime = '2014-5-1'; declare @end datetime='2017-5-1'; 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) // sql拼接 1100條 1秒 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')
v_yjdateggjg 視圖內部sql語句
SELECT dbo.v_yjdatejg.yjxzqid, dbo.datalocation.cjdd AS yjxzqname, dbo.v_yjdatejg.ncpid, dbo.products.ncpmc,
dbo.v_yjdatejg.rq, dbo.v_yjdatealljg.ttjg AS sjttjg, dbo.v_yjdatealljg.pfjg AS sjpfjg, dbo.v_yjdatealljg.lsjg AS sjlsjg,
dbo.v_yjdateycjg.ttjg AS ycttjg, dbo.v_yjdateycjg.pfjg AS ycpfjg, dbo.v_yjdateycjg.lsjg AS yclsjg
FROM dbo.v_yjdatejg INNER JOIN
dbo.datalocation ON dbo.v_yjdatejg.yjxzqid = dbo.datalocation.id INNER JOIN
dbo.products ON dbo.v_yjdatejg.ncpid = dbo.products.id LEFT OUTER JOIN
dbo.v_yjdateycjg ON dbo.v_yjdatejg.yjxzqid = dbo.v_yjdateycjg.yjxzqid AND
dbo.v_yjdatejg.ncpid = dbo.v_yjdateycjg.ncpid AND dbo.v_yjdatejg.rq = dbo.v_yjdateycjg.ycrq LEFT OUTER JOIN
dbo.v_yjdatealljg ON dbo.v_yjdatejg.yjxzqid = dbo.v_yjdatealljg.yjxzqid AND
dbo.v_yjdatejg.ncpid = dbo.v_yjdatealljg.ncpid AND dbo.v_yjdatejg.rq = dbo.v_yjdatealljg.cjrq
sqlHelper類
public static class SqlHelper { private static readonly string conStr = ConfigurationManager.ConnectionStrings["lyc2ConnString"].ConnectionString; //insert delete update public static int ExecuteNonQuery(string sql, params SqlParameter[] pms) { using (SqlConnection con = new SqlConnection(conStr)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { if (pms != null) { cmd.Parameters.AddRange(pms); } con.Open(); return cmd.ExecuteNonQuery(); } } } //返回單個值 public static object ExecuteScalar(string sql, params SqlParameter[] pms) { using (SqlConnection con = new SqlConnection(conStr)) { using (SqlCommand cmd = new SqlCommand(sql, con)) { if (pms != null) { cmd.Parameters.AddRange(pms); } con.Open(); return cmd.ExecuteScalar(); } } } //執行返回DataReader public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms) { SqlConnection con = new SqlConnection(conStr); using (SqlCommand cmd = new SqlCommand(sql, con)) { if (pms != null) { cmd.Parameters.AddRange(pms); } //con.Open(); try { if (con.State == ConnectionState.Closed) { con.Open(); } return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch { con.Close(); con.Dispose(); throw; } } } //查詢多行 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms) { DataTable dt = new DataTable(); using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr)) { if (pms != null) { adapter.SelectCommand.Parameters.AddRange(pms); } adapter.Fill(dt); } return dt; } /// <summary> /// 將DbNull轉換成null /// </summary> /// <param name="obj"></param> /// <returns></returns> public static object FromDbNull(object obj) { if (obj == DBNull.Value) { return null; } else { return obj; } } /// <summary> /// 將null轉換成DbNull /// </summary> /// <param name="obj"></param> /// <returns></returns> public static object ToDbNull(object obj) { if (obj == null) { return DBNull.Value; } else { return obj; } } } }
博客園非常蛋疼的說：字數少有150字不能發佈的首頁。
答：親，代碼不算數嗎？看來博客園的管理已經漸漸思維固話了，越來越像寫中學生作文規範。
