none
Entity Framework performance vs sqlclient RRS feed

  • Question

  • I tried the following article http://blogs.msdn.com/b/adonet/archive/2008/03/27/ado-net-entity-framework-performance-comparison.aspx

    and make my own test, which is entity is faster than sqlclient. I don't know this is right to test entity framework vs sqlclient.

    // entity framework test using northwind database 
    
    using (NorthwindEntities ne = new NorthwindEntities())
                {
                    ObjectQuery<Order> ordersQuery =  ne.CreateQuery<Order>("SELECT VALUE o FROM Orders as o");                
    
                    Stopwatch sw = Stopwatch.StartNew();
    
                    for (int i = 1; i <= 101; i++)
                    {
                                           ordersQuery.Execute(MergeOption.NoTracking);
    
                        Console.WriteLine("Process select process - {0}; Elapsed time in milli seconds {1}", i, sw.ElapsedMilliseconds);
                    }
                    sw.Stop();
                    Console.WriteLine("Process select {0} rows; Total Elapsed time in milli seconds {1}", 100, sw.ElapsedMilliseconds);
    
                }

    vs

     String sql = "select CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate, ShipAddress, ShipCity, ShipCountry, ShipName, ShippedDate, ShipPostalCode, ShipRegion, ShipVia from Orders";
    
                using (SqlConnection con = new SqlConnection(Constant.NorthwindSqlConst))
                {
                    using (SqlCommand com = new SqlCommand(sql, con))
                    {
                        Stopwatch sw = Stopwatch.StartNew();
    
                        if (con.State !=ConnectionState.Open)  
    con.Open();
    
                        for (int i = 1; i <= 101; i++)
                        {
                            using (SqlDataReader dr = com.ExecuteReader())
                            {
                                while (dr.Read())
                                {   // using entity class
                                       Order o = new Order();
    
        if (!dr.IsDBNull(0)) o.CustomerID = dr.GetString(0);
        if (!dr.IsDBNull(1)) o.EmployeeID = dr.GetInt32(1);
        if (!dr.IsDBNull(2)) o.Freight = dr.GetDecimal(2);
        if (!dr.IsDBNull(3)) o.OrderDate = dr.GetDateTime(3);
        if (!dr.IsDBNull(4)) o.OrderID = dr.GetInt32(4);
        if (!dr.IsDBNull(5)) o.RequiredDate =dr.GetDateTime(5);
        if (!dr.IsDBNull(6)) o.ShipAddress = dr.GetString(6);
        if (!dr.IsDBNull(7)) o.ShipCity = dr.GetString(7);
        if (!dr.IsDBNull(8)) o.ShipCountry = dr.GetString(8);
        if (!dr.IsDBNull(9)) o.ShipName = dr.GetString(9);
        if (!dr.IsDBNull(10)) o.ShippedDate=dr.GetDateTime(10);
        if (!dr.IsDBNull(11)) o.ShipPostalCode = dr.GetString(11);
        if (!dr.IsDBNull(12)) o.ShipRegion = dr.GetString(12);
        if (!dr.IsDBNull(13)) o.ShipVia = dr.GetInt32(13);
                                }
                                Console.WriteLine("Process select process - {0}; Elapsed time in milli seconds {1}", i, sw.ElapsedMilliseconds);
                                
                            }
                        }
    
                        sw.Stop();
                        Console.WriteLine("Process select {0} rows; Total Elapsed time in milli seconds {1}", 100, sw.ElapsedMilliseconds);
                    }
                }


    It's hard to be advanced programmer

    Wednesday, April 17, 2013 3:50 AM

Answers

  • Hi IRW7,

    Generally speaking, I think sqlclient should be faster since LINQ to Entities or Entity Sql will be then converted to sql commands.

    I think you should also try to loop the ObjectResult returned by orderQuery.Execute method since you have looped the result after SqlCommand.ExecuteReader method, like:

    foreach (Order or in ordersQuery.Execute(MergeOption.NoTracking))
    {
        int i = or.OrderID;
        count++;
    }
    

    As mentioned in that blog, you may also try to throw out the first execution result using SqlClient.

    "I threw out the first run because of the one-time costs associated with connecting to the database and generating an execution plan."

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, April 22, 2013 2:02 AM
    Moderator