I have to record web service calling in database. At beginning, I used code first EF to define Entity class and generate database script. The database part is very simple, just only one table. There is a primary key: Id, and other columns are string ,datetime and float. 16 columns totally.
我必须在数据库中记录web服务调用。首先，我使用代码first EF定义实体类并生成数据库脚本。数据库部分非常简单，只有一个表。有一个主键:Id，其他列是字符串、datetime和float。16列完全。
Then I ran the performance analysis of VS2012. the report shows RecordUsageEF consume half time of whole calling, that is ridiculous. I tried MergeOption.NoTracking option and Pre-Generate Views(How to: Pre-Generate Views to Improve Query Performance). But they didn't help to much.
然后我进行了VS2012的性能分析。报告显示RecordUsageEF花了整个调用的一半时间，这是荒谬的。我试着MergeOption。NoTracking选项和预生成视图(如何:预生成视图以提高查询性能)。但它们并没有多大帮助。
Then I tried Ado.net. I put sql script in source code just for testing. calling 2 methods together to compare the performance.
然后我尝试Ado.net。我把sql脚本放在源代码中只是为了测试。调用两个方法来比较性能。
public static void RecordUsage(HttpContext httpContext, XmlWDCRecipe processedRecipe, string orgRecipe, string userName, ActionEnum action, bool trueview, string pageId)
{
RecordUsageEF(httpContext, processedRecipe, orgRecipe, userName, action, trueview, pageId);
RecordUsageADO(httpContext, processedRecipe, orgRecipe, userName, action, trueview, pageId);
}
The result surprised me:
结果让我很吃惊:
Updated using static EF context improves some:
使用静态EF上下文进行更新改进:
Inside RecordUsageEF:
在RecordUsageEF:
Updated Inside RecordUsageEF -- static context
更新在RecordUsageEF——静态上下文。
Updated Just realized the default Performance is CPU sampling, here is the result when choosing Instrumentation
更新刚刚实现的默认性能是CPU采样，这里是选择仪器的结果。
It is not so bad but if CPU is bottle neck for a website/webservice. EF looks not good choice.
它不是那么糟糕，但如果CPU是瓶颈的网站/网站服务。EF看起来不是一个好选择。
I checked the script generated by EF in sql profiler. it is a very simple insert sql statement and it ran faster then Ado.net one.
我检查了EF在sql profiler中生成的脚本。它是一个非常简单的插入sql语句，运行速度比Ado.net快。
Is there something I missed for EF? I can't use EF if it in this level of performance.
有什么东西是我错过的吗?我不能用EF如果它在这个性能级别上。
Here is source code.
EF version:
这是源代码。EF版本:
public static readonly LogContainer container = new LogContainer();
private static void RecordUsageEF(HttpContext httpContext, XmlWDCRecipe processedRecipe, string orgRecipe, string userName, ActionEnum action, bool trueview, string pageId)
{
{
container.Usages.MergeOption = System.Data.Objects.MergeOption.NoTracking;
using (LookupService ls = new LookupService(httpContext.Server.MapPath(geoDBLocation), LookupService.GEOIP_MEMORY_CACHE))
{
//get country of the ip address
Location location = s.getLocation(httpContext.Request.UserHostAddress);
Usage usage = new Usage()
{
Brand = brand,
Action = action.ToString(),
ExecuteDate = DateTime.Now,
OutputFormat = trueview ? Path.GetExtension(processedRecipe.Output.trueview_file) : Path.GetExtension(processedRecipe.Output.design_file),
Recipe = orgRecipe,
SessionId = pageId,
Username = userName,
ClientIP = httpContext.Request.UserHostAddress,
ClientCountry = location == null ? null : location.countryName,
ClientState = location == null ? null : location.regionName,
ClientCity = location == null ? null : location.city,
ClientPostcode = location == null ? null : location.postalCode,
ClientLatitude = location == null ? null : (double?)location.latitude,
ClientLongitude = location == null ? null : (double?)location.longitude,
UserAgent = httpContext.Request.UserAgent
};
//container.AddToUsages(usage);
container.Usages.AddObject(usage);
container.SaveChanges(System.Data.Objects.SaveOptions.None);
}
}
}
EF setting is default:
EF设置默认值:
Ado.net version:
Ado.net版本:
private static void RecordUsageADO(HttpContext httpContext, XmlWDCRecipe processedRecipe, string orgRecipe, string userName, ActionEnum action, bool trueview, string pageId)
{
using (SqlConnection conn = new SqlConnection("data source=pgo_swsvr;initial catalog=OESDWebSizer;user id=sa;password=sa;MultipleActiveResultSets=True;"))
{
using (LookupService ls = new LookupService(httpContext.Server.MapPath(geoDBLocation), LookupService.GEOIP_MEMORY_CACHE))
{
//get country of the ip address
//test using "203.110.131.5" "58.63.236.236"
//httpContext.Request.UserHostAddress
Location location = ls.getLocation(httpContext.Request.UserHostAddress);
SqlCommand command = new SqlCommand();
conn.Open();
command.Connection = conn;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = @"insert into Usages ([Brand],[Username],[SessionId],[Action],[Recipe],[ExecuteDate]
,[OutputFormat],[ClientIP],[ClientCountry],[ClientState],[ClientCity],[ClientPostcode]
,[ClientLatitude],[ClientLongitude],[UserAgent])
Values ('" + brand + "',"
+ (string.IsNullOrEmpty(userName) ? "NULL" : "'" + userName + "'") + ", '" + pageId + "', '" + action.ToString() + "', '" + orgRecipe + "', '" + DateTime.Now.ToString("yyyyMMdd") + "', '"
+ (trueview ? Path.GetExtension(processedRecipe.Output.trueview_file) : Path.GetExtension(processedRecipe.Output.design_file)) + "', '"
+ httpContext.Request.UserHostAddress + "', '"
+ (location == null ? string.Empty : location.countryName) + "', '"
+ (location == null ? string.Empty : location.regionName) + "', '"
+ (location == null ? string.Empty : location.city) + "', '"
+ (location == null ? string.Empty : location.postalCode) + "', "
+ (location == null ? 0 : (double?)location.latitude) + ", "
+ (location == null ? 0 : (double?)location.longitude) + ", '"
+ httpContext.Request.UserAgent + "')";
command.ExecuteNonQuery();
}
}
}
2 个解决方案
#1
2
Entity Framework is abstracting a lot of details as the cost of (some) CPU performance. Microsoft has an article detailing performance considerations: Performance Considerations (Entity Framework)
实体框架将大量细节抽象为(某些)CPU性能的成本。微软有一篇详细介绍性能考虑的文章:性能考虑因素(实体框架)
#2
1
I'm mostly familiar with the code-first way of doing things, but if your container object is a DbContext, the following should increase insert performance dramatically:
我非常熟悉代码优先的方法，但是如果您的容器对象是一个DbContext，那么下面应该会显著地增加插入性能:
container.Configuration.AutoDetectChangesEnabled = false;
container.Configuration.ValidateOnSaveEnabled = false;
#1
2
Entity Framework is abstracting a lot of details as the cost of (some) CPU performance. Microsoft has an article detailing performance considerations: Performance Considerations (Entity Framework)
实体框架将大量细节抽象为(某些)CPU性能的成本。微软有一篇详细介绍性能考虑的文章:性能考虑因素(实体框架)
#2
1
I'm mostly familiar with the code-first way of doing things, but if your container object is a DbContext, the following should increase insert performance dramatically:
我非常熟悉代码优先的方法，但是如果您的容器对象是一个DbContext，那么下面应该会显著地增加插入性能:
container.Configuration.AutoDetectChangesEnabled = false;
container.Configuration.ValidateOnSaveEnabled = false;