程序部署到服务器后非常慢！！！

结论：

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

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>();<br>　　　　　　　//第一条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)";<br>　　　　　　　//第二条 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;
}<br><br>

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秒<br>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')

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;
            }
        }
    }

}