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
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();
}
That’s it we have evaluated the performance
of ADO.net and LINQ-to-SQL in executing an DELETE Query.
RELATED POST
RELATED POST
No comments:
Post a Comment