程序布置到服务器上速度慢_程序部署到服务器后非常慢！！！

结论：

1、导致慢的主要原因是：索引 ！此处where条件时间没有加索引。如果唯一则添加唯一索引，若不唯一则添加组合唯一索引。

1、EF 查询 比ADO稍微慢一点点，原因不详。

2、在原生ADO.Net中 使用 参数化查询 比 使用非参数化sql拼接  慢几十倍！！原因不详。

ADO.Net代码测试

public List SelectList(int yjxzqid, int ncpid, DateTime start, DateTime end)

{

List list = new List();

//第一条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(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

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 classSqlHelper

{private static readonly string conStr = ConfigurationManager.ConnectionStrings["lyc2ConnString"].ConnectionString;//insert delete update

public static int ExecuteNonQuery(string sql, paramsSqlParameter[] pms)

{using (SqlConnection con = newSqlConnection(conStr))

{using (SqlCommand cmd = newSqlCommand(sql, con))

{if (pms != null)

{

cmd.Parameters.AddRange(pms);

}

con.Open();returncmd.ExecuteNonQuery();

}

}

}//返回单个值

public static object ExecuteScalar(string sql, paramsSqlParameter[] pms)

{using (SqlConnection con = newSqlConnection(conStr))

{using (SqlCommand cmd = newSqlCommand(sql, con))

{if (pms != null)

{

cmd.Parameters.AddRange(pms);

}

con.Open();returncmd.ExecuteScalar();

}

}

}//执行返回DataReader

public static SqlDataReader ExecuteReader(string sql, paramsSqlParameter[] pms)

{

SqlConnection con= newSqlConnection(conStr);using (SqlCommand cmd = newSqlCommand(sql, con))

{if (pms != null)

{

cmd.Parameters.AddRange(pms);

}//con.Open();

try{if (con.State ==ConnectionState.Closed)

{

con.Open();

}returncmd.ExecuteReader(CommandBehavior.CloseConnection);

}catch{

con.Close();

con.Dispose();throw;

}

}

}//查询多行

public static DataTable ExecuteDataTable(string sql, paramsSqlParameter[] pms)

{

DataTable dt= newDataTable();using (SqlDataAdapter adapter = newSqlDataAdapter(sql, conStr))

{if (pms != null)

{

adapter.SelectCommand.Parameters.AddRange(pms);

}

adapter.Fill(dt);

}returndt;

}///

///将DbNull转换成null///

///

///

public static object FromDbNull(objectobj)

{if (obj ==DBNull.Value)

{return null;

}else{returnobj;

}

}///

///将null转换成DbNull///

///

///

public static object ToDbNull(objectobj)

{if (obj == null)

{returnDBNull.Value;

}else{returnobj;

}

}

}

}

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

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