Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

            }
        }
    }
like image 711
findcaiyzh Avatar asked Aug 01 '12 00:08

findcaiyzh


People also ask

Which is faster ADO.NET or EF?

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.

Is EF core faster than EF6?

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.

Is Entity Framework slow?

Entity Framework is a great tool, but in some cases its performance is slow. One such case arises when complex queries use “Contains”.


2 Answers

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)

like image 130
Christopher Stevenson Avatar answered Oct 24 '22 20:10

Christopher Stevenson


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;
like image 36
w.brian Avatar answered Oct 24 '22 18:10

w.brian