疑难杂症——关于EntityFramework的SqlQuery方法的执行效率差异的探讨

2018-02-03 10:19:45来源:http://www.cnblogs.com/landeanfen/p/8392498.html作者:懒得安分人点击

分享

前言:最近项目上面遇到一个问题,在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=1and 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 "";
}
}
GetListByDataTable

然后在控制台里面分别调用。



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=1and 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/


欢迎各位转载,但是未经作者本人同意,转载文章之后 必须在文章页面明显位置给出作者和原文连接 ,否则保留追究法律责任的权利


最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台