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 |
- The
INSERTtest is executed by adding to an Entity Framework entity Set all items with a singleSaveChangesinvocation at the operation ending, by using a single always-opened SQL Connection. - The
UPDATEtest is executed with a different connection or context for ADO or EF per item to update. - The
DELETEtest executed against a single row deletion. Although EF made aSELECTquery before aDELETEoperation, 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. - The
SELECTtest 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();
}
}
}