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

2018-06-23 13:24:30来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

 

结论:

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字不能发布的首页。

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

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:FSLIB.NETWORK 简易使用指南

下一篇:将ABP的数据库从SQLSERVER迁移到MySql