What causes EF insert to be much slower than plain ADO.NET?

Question

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:
enter image description here

Updated using static EF context improves some:
enter image description here

Inside RecordUsageEF:
enter image description here

Updated Inside RecordUsageEF -- static context
enter image description here

Updated Just realized the default Performance is CPU sampling, here is the result when choosing Instrumentation
enter image description here

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:
enter image description here

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();

            }
        }
    }

You may also interesting for this one: http://stackoverflow.com/questions/2363735/benchmark-linq2sql-subsonic2-subsonic3-any-other-ideas-to-make-them-faster — Aristos, Aug 01 '12 at 00:40
Thanks, Aristos, I read similar article before. ObjectTrackingEnabled doesn't exist in ObjectContext. I tried use Model and Database First with DbContext and set ObjectTrackingEnabled as false but it doesn't help. CompiledQuery is mentioned in a lot of articles too. but I need insert one, I don't how to use it. — findcaiyzh, Aug 01 '12 at 00:49
Ive also done a heap of research into EF performance and in particular how it compares to other frameworks, see: http://blog.staticvoid.co.nz/2012/03/entity-framework-comparative.html I haven't explicitly listed tests directly on ADO.net but they are similar to the dapper case. What i found was that EF (when used correctly) has very similar performance to Dapper (and hence ado.net) especially in small batches. — undefined, Aug 01 '12 at 00:57
ild also recommend looking at my use cases here (https://github.com/lukemcgregor/StaticVoid.OrmPerformance) and in particular for insert here: https://github.com/lukemcgregor/StaticVoid.OrmPerformance/blob/master/Harness.EntityFramework5-Beta2/TunedConfiguration.cs — undefined, Aug 01 '12 at 00:59
EF does not claim to be the fastest but I has a purpose. For large databases with complex queries I will have TSQL with hints and #temp tables... For inserts I will might have dictionaries to presort and insert via TVP. EF is a great tool but it is not going to have the best performance. My experience is Drapper has better speed. For inserting a single record is 30% that big a deal. I bet that is a 40 MS insert. — paparazzo, Aug 01 '12 at 01:04
You are not comparing apples to apples. In ADO.Net, you basically send a string over a wire to insert your records. That does not take much resources or time. In EF, you create the same LogContainer for every call. Now that requires substantial effort (the bigger the model, the bigger the effort). You could use a Singleton pattern here. Or maybe this [article](http://www.asp.net/web-forms/tutorials/continuing-with-ef/maximizing-performance-with-the-entity-framework-in-an-asp-net-web-application) will give you some ideas. — Darek, Aug 01 '12 at 01:07
@Blam, I thought so, 2 or 3 times slower is expected. but for my cause -- 100 times. So I think there is something wrong somewhere. — findcaiyzh, Aug 01 '12 at 01:07
@Blam From my testing this depends largely on your use case, for small datasets (under 100 items changed or selected at a time) EF5 outperforms dapper, for larger datasets dapper is faster. However in most cases the differences are small. The execptions to these rules (and the places where dapper can be substantially faster) are queries which can be reduced to an update where or delete where — undefined, Aug 01 '12 at 01:10
@findcaiyzh how many times are you calling this method? also from my tests you should expect EF to be within about 50% of the performance of raw ADO.Net — undefined, Aug 01 '12 at 01:12
@blam not as much as you would think there is about 40-50% difference at 10k items between dapper and EF which is still well under double. — undefined, Aug 01 '12 at 01:17
@findcaiyzh are you newing the context each time you call the function? — undefined, Aug 01 '12 at 01:18
I tried ODATA to EF and it got absolutely killed on a simple join to two mid sized tables. When I traced down the TSQL it was terrible but logical. ODATA and EF are still valid technologies. Just don't expect them to be the fastest. — paparazzo, Aug 01 '12 at 01:18
@LukeMcGregor. Yes I did. Change to Use static context. it helps but not resolve the problem. I updated the question. — findcaiyzh, Aug 01 '12 at 01:20
@findcaiyzh a static context is likely to make it worse the longer it runs, see http://blog.staticvoid.co.nz/2012/05/entityframework-performance-and.html, time to add an item (when detect changes is on) is proportional to the number of items in the context. What version of EF are you using? — undefined, Aug 01 '12 at 01:22
@LukeMcGregor, EF I used is default with vs2012. I think version should be 4.0 or 4.1 — findcaiyzh, Aug 01 '12 at 01:26
@findcaiyzh are you using .net 4.5 and nuget or referencing a GAC version? — undefined, Aug 01 '12 at 01:28
If the hit is 100:1 you are probably not going to fix EF. The remedy is TSQL. — paparazzo, Aug 01 '12 at 01:28
@findcaiyzh I highly doubt it will give you any significant improvement (ive only ever seen people use it with selects) but heres the tutorial on it http://msdn.microsoft.com/en-us/library/bb896297.aspx — undefined, Aug 01 '12 at 01:37
I know there were some performance issues with TPT in EF 4.0 (and i think 4.1) you could try upgrading to 4.3.1 (from nuget) — undefined, Aug 01 '12 at 01:43
My only other sugguestion would be to grab EFProfiler and take a look at the queries that are being generated — undefined, Aug 01 '12 at 01:46
@LukeMcGregor, Thanks I just realize I used wrong tool. the screenshot in question is CPU sample report. I tried instrument instead which is for time. the result is 11.5%(EF) vs 4.3%(ado.net). So if CPU is bottle neck of website, EF is not good choice. I will try EF4.3.1. — findcaiyzh, Aug 01 '12 at 01:56
yeah EF detect changes does use a bit of CPU (and memory), you can reduce this (somewhat) by using proxy entities, but ADO will always win here as EF is just a wrapper. BTW those numbers seem much more reasonable (Especially if you are CPU limited) :) Just as a comparison I ran my tests on a quad 2.66 with 6gb ram — undefined, Aug 01 '12 at 02:02
Also try to disable LazyLoading and Tracking (samples in the link I provided before) — Darek, Aug 01 '12 at 03:25
How do you run your tests? Is it single run or multiple runs in loop on single context? Did you check your database after the test? — Ladislav Mrnka, Aug 01 '12 at 08:18

score 2 · Answer 1 · answered Aug 03 '12 at 16:04

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)

answered Aug 03 '12 at 16:04

Christopher Stevenson

2,843
20
25

score 1 · Answer 2 · answered Aug 01 '12 at 02:04

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:

container.Configuration.AutoDetectChangesEnabled = false;
container.Configuration.ValidateOnSaveEnabled = false;

answered Aug 01 '12 at 02:04

w.brian

16,296
14
69
118

Hi, I am using ObjectContext. I will try DbContext – findcaiyzh Aug 01 '12 at 02:12
1

Yeah this will absolutely improve performance when inserting large numbers of items. However when inserting only a few items at a time this provides no benefit. see http://blog.staticvoid.co.nz/2012/05/entityframework-performance-and.html – undefined Aug 01 '12 at 02:18
That post is specific to turning off change tracking. ValidateOnSave is more significant in terms of performance -- It involves a lot of reflection if I recall correctly. – w.brian Aug 01 '12 at 02:30

What causes EF insert to be much slower than plain ADO.NET?

2 Answers2