Monday, May 28, 2012

LINQ to SQL Vs ADO.Net Performance Test - DELETE


Asp.Net provides a number of Data Access Technologies, like ADO.Net, LINQ-to-SQL, Entity Framework etc, each one of these technologies has its own advantages and disadvantages, while designing an application; we need to identify the appropriate data access technology to achieve maximum efficiency.


Here we shall evaluate the data access performance of ADO.net and LINQ-to-SQL to execute a DELETE query.
The configuration of the system used to perform the evaluation is as follows.

OS
Windows XP Professional 2002 SP3
Processor
Pentium® D 2.66 GHz
RAM
3 GB




























The performance test was carried out for 25 iterations with both ADO.net and LINQ-to-SQL, the results of the test are as follows.






















ADO.Net Average time:              568 Microseconds
LINQ-to-SQL Average time:       4184 Microseconds

The test results clearly indicate that ADO.Net technology has a distinct edge over LINQ-to-SQL, when it comes to DELETE operations.

In LINQ-to-SQL we can take advantage of the BULK operations, perform all the operations in the DataContext and finally call dbContext.SubmitChanges(), to update all the operations into the database at one shot.  We tried to execute the same delete operation with a different set of 25 records, this time by doing a BULK delete operation and found that the average time came down to 3308 Microseconds.

The code used to perform the test is as follows


ADO.Net

int[] empIDs = { 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136 };

SqlConnection objConn;
SqlCommand objCmd;
Stopwatch timer;
string strQuery = string.Empty;
string strConn = ConfigurationManager.ConnectionStrings["EmployeesConnectionString"].ToString();
objConn = new SqlConnection(strConn);
timer = new Stopwatch();

objConn.Open();
for (int i = 0; i < empIDs.Length; i++)
{
    timer.Start();

    strQuery = "DELETE FROM Employee WHERE ID = " + empIDs[i];
    objCmd = new SqlCommand(strQuery, objConn);
    objCmd.ExecuteNonQuery();

    timer.Stop();
    Response.Write("Execution Time (Micro Seconds):" + (1e6 * timer.ElapsedTicks / (double)Stopwatch.Frequency).ToString() + "
"
);
    timer.Reset();
}
objConn.Close();

LINQ to SQL

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();

int[] empIDs = { 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226, 227, 228, 229, 230, 231, 232, 233, 234, 235, 236 };

Stopwatch timer;
timer = new Stopwatch();

for (int i = 0; i < empIDs.Length; i++)
{
    Employee objEmp = (Employee)(from e in dbContext.Employees where e.ID.Equals(empIDs[i]) select e).First();
    timer.Start();
    dbContext.Employees.DeleteOnSubmit(objEmp);
    dbContext.SubmitChanges();
    timer.Stop();
    Response.Write("Execution Time (Micro Seconds):" + (1e6 * timer.ElapsedTicks / (double)Stopwatch.Frequency).ToString() + "
"
);
    timer.Reset();
}



Search Flipkart Products:
Flipkart.com

No comments: