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.
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.
Then I tried Ado.net. I put sql script in source code just for testing. calling 2 methods together to compare the performance.
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:
Inside RecordUsageEF:
Updated Inside RecordUsageEF -- static context
Updated Just realized the default Performance is CPU sampling, here is the result when choosing Instrumentation
It is not so bad but if CPU is bottle neck for a website/webservice. EF looks not good choice.
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.
Is there something I missed for EF? I can't use EF if it in this level of performance.
Here is source code.
EF version:
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:
Ado.net version:
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();
}
}
}
Performance: ADO.NET is much faster compared to the Entity Framework. Because ADO.NET always establishes the connection directly to the database. That's why it provides much better performance compared to the Entity Framework.
Entity Framework (EF) Core, Microsoft's object-to-database mapper library for . NET Framework, brings performance improvements for data updates in version 7, Microsoft claims. The performance of SaveChanges method in EF7 is up to 74% faster than in EF6, in some scenarios.
Entity Framework is a great tool, but in some cases its performance is slow. One such case arises when complex queries use “Contains”.
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)
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:
container.Configuration.AutoDetectChangesEnabled = false;
container.Configuration.ValidateOnSaveEnabled = false;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With