首页 > > 网络编程 > Mysql >

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

2018-06-17 22:28:12来源：未知阅读 ()

结论：

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

}