Comparing two different frameworks for database interaction is actually difficult. An O/RM that provides increased simplicity in database querying/persisting and extreme team working improvements from the intrinsic object-oriented programming makes EF the best choice in several scenarios. This, along with the knowledge of performance bottlenecks that occurs in such a framework, will help in avoiding dangerous issues when in the production stage, when the application load increases more than expected.
The following is a comparison of all performance results of connected ADO.NET with SQLClient data provider and EF using the same data provider. Absolute values are always useless, while relative ones give us significant information about the performance results:
SqlClient |
EF |
EF + Workaround | |
---|---|---|---|
INSERT (1000 items) |
215ms |
1716ms (+ 698 %) |
none |
UPDATE (1000 items) |
268ms |
1672ms (+ 524 %) |
914ms (+ 241 %) |
DELETE |
1ms |
7ms (+ 600 %) |
40ms (+ 3900 %) |
SELECT |
1ms |
1ms |
INSERT
test is executed by adding to an Entity Framework entity Set all items with a single SaveChanges
invocation at the operation ending, by using a single always-opened SQL Connection.UPDATE
test is executed with a different connection or context for ADO or EF per item to update.DELETE
test executed against a single row deletion. Although EF made a SELECT
query before a DELETE
operation, the workaround with the disconnected item attachment adds significant execution time, making its usage valid only to avoid item materialization for complex (or big) entities.SELECT
test is executed with a top 1000 against the only invoice table.As evident in the preceding table, EF actually performs fast in data reading along with the ability to produce complex queries with multiple execution scenarios. In contrast, when dealing with data manipulation, connected ADO.NET classes perform much better.
It is not about how much memory we have, it is about conserving it. If we have to execute a custom ETL (Extract, Transform, Load) workflow application or any other application that needs accessing, and usually persists the data, it doesn't mean that we have to keep all the data in the application state. A sliding approach becomes mandatory anytime we deal with huge datasets. However, when the dataset is not so huge, reducing resource usage without sacrificing any other performance aspect is important.
This design is available as with Entity Framework classes as with connected ADO.NET classes. By using EF we have the ability to manipulate data items in object-oriented way, and while using connected ADO.NET classes we don't. Here is an example of the stream-like data manipulation using the two frameworks (EF and ADO.NET):
//a source and target context using (var sourceContext = new InvoicingDBContainer()) using (var targetContext = new InvoicingDBContainer()) //iterate a query without a collection materialization foreach (var invoice in sourceContext.Invoice.Take(100000).AsNoTracking()) //a simple client-side logic if (invoice.Number.EndsWith("0")) { targetContext.Invoice.Add(new Invoice { Date = invoice.Date.AddDays(4), Number = string.Format("{0}/BIS", invoice.Number), CustomerCustomerID = invoice.CustomerCustomerID, }); targetContext.SaveChanges(); }
The following is the same example with connected ADO.NET:
//a couple of connections for data copying using (var sourceConnection = new SqlConnection("data source=(local);initial catalog=TestDB;integrated security=true;")) using (var targetConnection = new SqlConnection("data source=(local);initial catalog=TestDB;integrated security=true;")) { sourceConnection.Open(); targetConnection.Open(); //a source command and result-set using (var sourceCommand = new SqlCommand("SELECT TOP 100000 * FROM Invoice", sourceConnection)) using (var sourceReader = sourceCommand.ExecuteReader()) while (sourceReader.Read()) { //by scrolling a data reader we scroll a client cursor //this cursor works on a light row batch produced //by the data provider itself //we don't have all data materialized here var InvoiceID = (int)sourceReader["InvoiceID"]; var CustomerCustomerID = (int)sourceReader["CustomerCustomerID"]; var Number = (string)sourceReader["Number"]; var Date = (DateTime)sourceReader["Date"]; //some sliding client-side logic if (Number.EndsWith("0")) //the target insert using (var targetCommand = new SqlCommand("INSERT INTO [Invoice](CustomerCustomerID,Number,Date) SELECT @customerid, @number, @date", targetConnection)) { targetCommand.Parameters.AddWithValue("customerid", CustomerCustomerID); targetCommand.Parameters.AddWithValue("number", string.Format("{0}/BIS", Number)); targetCommand.Parameters.AddWithValue("date", Date.AddDays(4)); targetCommand.ExecuteNonQuery(); } } }
3.129.22.164