疑难杂症——关于EntityFramework的SqlQuery方法的执行效率差异的探讨
前言：最近项目上面遇到一个问题，在Code First模式里面使用EntityFramework的SqlQuery()方法查询非常慢，一条数据查询出来需要10秒以上的时间，可是将sql语句放在plsql里面执行，查询时间基本可以忽略不计。折腾了半天时间，仍然找不到原因。最后通过对比和原始Ado的查询方式的差异找到原因，今天将此记录下。
本文原创地址：http://www.cnblogs.com/landeanfen/p/8392498.html
一、问题描述
其实问题很简单，上面前言已经描述过。就是一个多表的联合查询，没有什么特殊的语法。大致代码如下：（PS：不要问为什么用了EF还要使用sql语句，你就当这是个梗！）
通用的sql语句查询方法
public IEnumerable<TEntity> SqlQuery<TEntity>(string sql, params object[] parameters) { return this._dbContext.Database.SqlQuery<TEntity>(sql, parameters).ToArray(); }
然后在外层调用这个方法
var sql = @"select * from ( select ax.*,rownum as rnum from ( select o.* from order o left join ordertt a on o.id=a.orderid left join orderdd d on o.id=d.orderid left join orderstation ts on d.station = ts.id left join ordermodel m on o.materialid=m.id where 1=1 and o.status = 30 order by ts.no,tw.seq ) ax ) t where t.rnum>0 and t.rnum <=15"; var result = SqlQuery(sql);
然后查询，每次得到的结果都是一条记录，但是却需要10秒以上，可能你不相信，但这是真的！
问题就这么个问题，没办法，出现了问题就要想办法解决。
二、找到解决方案
我们将SqlQuery<T>()方法转到定义，最终发现它是EntityFramework.dll里面的方法。
既然转到定义已经找不到任何突破口，那我们常规的做法就只有对比了。然后博主在本地定义了一个对比的例子。
原始组：EF的SqlQuery()方法
//EF查询方法 private static IEnumerable<TestModel> EFSqlQuery(DbContext db, string sql) { return db.Database.SqlQuery<TestModel>(sql).ToArray(); }
对比照：Ado的查询方法
//ADO的查询方法 private static IEnumerable<TestModel> AdoSqlQuery(DbContext db, string sql) { DataTable dt = ExecuteDataTable(db, sql); var ms = GetListByDataTable<TestModel>(dt); return ms; }
//DataTable转List<T>公共的方法 private static DataTable ExecuteDataTable(DbContext db, string sql, params System.Data.Common.DbParameter[] parameters) { DataTable dt = new DataTable(); var conn = db.Database.Connection; conn.Open(); using (var cmd = db.Database.Connection.CreateCommand()) { foreach (var p in parameters) cmd.Parameters.Add(p); cmd.CommandText = sql; dt.Load(cmd.ExecuteReader()); } conn.Close(); return dt; } private static List<T> GetListByDataTable<T>(DataTable dt) where T : class, new()//这个意思是一个约束，约定泛型T必须要有一个无参数的构造函数 { List<T> lstResult = new List<T>(); if (dt.Rows.Count <= 0 || dt == null) { return lstResult; } //反射这个类得到类的类型。 Type t = typeof(T); //声明一个泛型类对象 T oT; //得到这个类的所有的属性 PropertyInfo[] lstPropertyInfo = t.GetProperties(); foreach (DataRow dr in dt.Rows) { //一个DataRow对应一个泛型对象 oT = new T(); //遍历所有的属性 for (var i = 0; i < lstPropertyInfo.Length; i++) { //得到属性名 string strPropertyValue = lstPropertyInfo[i].Name; //只有表格的列包含对应的属性 if (dt.Columns.Contains(strPropertyValue)) { object oValue = Convert.IsDBNull(dr[strPropertyValue]) ? default(T) : GetTypeDefaultValue(lstPropertyInfo[i].PropertyType.ToString(), dr[strPropertyValue]); //给对应的属性赋值 lstPropertyInfo[i].SetValue(oT, oValue, null); } } lstResult.Add(oT); } return lstResult; } private static object GetTypeDefaultValue(string strTypeName, object oValue) { switch (strTypeName) { case "System.String": return oValue.ToString(); case "System.Int16": case "System.Nullable`1[System.Int16]": return Convert.ToInt16(oValue); case "System.UInt16": case "System.Nullable`1[System.UInt16]": return Convert.ToUInt16(oValue); case "System.Int32": case "System.Nullable`1[System.Int32]": return Convert.ToInt32(oValue); case "System.UInt32": case "System.Nullable`1[System.UInt32]": return Convert.ToUInt32(oValue); case "System.Int64": case "System.Nullable`1[System.Int64]": return Convert.ToInt64(oValue); case "System.UInt64": case "System.Nullable`1[System.UInt64]": return Convert.ToUInt64(oValue); case "System.Single": case "System.Nullable`1[System.Single]": return Convert.ToSingle(oValue); case "System.Decimal": case "System.Nullable`1[System.Decimal]": return Convert.ToDecimal(oValue); case "System.Double": case "System.Nullable`1[System.Double]": return Convert.ToDouble(oValue); case "System.Boolean": case "System.Nullable`1[System.Boolean]": return Convert.ToBoolean(oValue); case "System.DateTime": case "System.Nullable`1[System.DateTime]": return Convert.ToDateTime(oValue); case "System.SByte": case "System.Nullable`1[System.SByte]": return Convert.ToSByte(oValue); case "System.Byte": case "System.Nullable`1[System.Byte]": return Convert.ToByte(oValue); case "System.Char": case "System.Nullable`1[System.Char]": return Convert.ToChar(oValue); case "System.Object": case "System.Nullable`1[System.Object]": return oValue; case "System.Array": return oValue as Array; default: return ""; } }
然后在控制台里面分别调用。
private static void Main(string[] args) { Stopwatch sw = new Stopwatch(); using (MesDbContext db = new MesDbContext()) { var sql = @"select * from ( select ax.*,rownum as rnum from ( select o.* from order o left join ordertt a on o.id=a.orderid left join orderdd d on o.id=d.orderid left join orderstation ts on d.station = ts.id left join ordermodel m on o.materialid=m.id where 1=1 and o.status = 30 order by ts.no,tw.seq ) ax ) t where t.rnum>0 and t.rnum <=15"; //“预热” EFSqlQuery(db, sql); sw.Start(); var result = EFSqlQuery(db, sql); sw.Stop(); Console.WriteLine("使用EF里面的SqlQuery方法查询得到" + result.Count() + "条记录。总耗时" + sw.Elapsed); //同样也预热一下 AdoSqlQuery(db, sql); sw.Restart(); var result2 = AdoSqlQuery(db, sql); sw.Stop(); Console.WriteLine("使用原始的Ado查询得到" + result.Count() + "条记录。总耗时" + sw.Elapsed); } }
得到结果：
差别有多大大家可以自行脑补。
原因分析
既然结果差别这么大，而sql语句在plsql里面执行又如此快，那么问题自然而然转到了对象序列化的身上了。也就是说这个SqlQuery()方法实际上可以分为两个步骤：第一步是查询得到DataTable之类的对象，然后第二步是将DataTable之类的对象转换为List<T>，既然我们第一步没有任何效率问题，那么问题肯定就在第二步上面了。
解决方案
既然初步判断是对象转换的问题，将TestModel这个对象转到定义一看，我地个乖乖，一百来个属性，于是更加坚信自己的分析是正确的。接下来，博主将这一百个字段减少到50个，再次执行发现效率提高了不少，基本在3秒左右，再减少到只剩20个字段，查询时间基本在毫秒级别。原来真是反射赋值的效率问题啊。至于项目为什么会有100个字段的对象，博主也没有想明白，或许真的需要吧！但是由此我们可以得出如下经验：
1、查询的sql语句里面尽量不要用select * 这种语法，尤其是连表的时候，如果你用了select * ，那有时真的就伤不起了！
2、如果确定实体里面真的有那么多字段有用（极端的情况），就不要用SqlQuery()了，而改用原生的Ado+DT转List<T>的方式。
三、意外的“环境问题”
本来以为找到了原因了，正要下结论的时候。听到另一个同事传来了不同的声音：骗纸，你的这个测试程序在我这里跑这么快，哪里有你说的10秒以上！去他那边瞅了一眼，吓我一跳，哪里有什么效率问题，简直快得不要不要的！这就尴尬了，同样的程序，在我这边怎么这么慢呢？
最后对比一圈下来，发现他那边用的Visual Studio的版本是2017，而我这边是2015。难道真是IDE的环境问题？博主不甘心，在本机装了一个2017，运行程序，依然快得吓人。而关掉2017，再用2015跑，同样需要10秒左右，而且找到其他装了VS 2013的电脑，用2013运行，依然需要10秒左右。好像2017以下的版本速度都会有一些影响。对比各个主要dll的版本，最终找不到任何不同。到这里，博主也没有办法了。如果哪位有解，感谢赐教！
那就用2017呗，这是最后得出的结论！！！
四、总结
以上针对最近遇到的一个问题做了一些记录。如果大家有不同的意见，欢迎交流和斧正！
本文原创出处：http://www.cnblogs.com/landeanfen/
欢迎各位转载，但是未经作者本人同意，转载文章之后必须在文章页面明显位置给出作者和原文连接，否则保留追究法律责任的权利
