Increase Performance With the Database Connections

Maybe you are surprised, maybe not, but fact is that you can increase the performance of reading and writing data with the explicit database connections.

The simple test method GetEmployees is reading the employees and projecting them by Id.

private static IEnumerable GetEmployees(IEnumerable ids, CompanyDbContext context)
{
	IList employees = new List(ids.Count());
	foreach (var idToSelect in ids)
	{
		Employee foundEmployee = context.Employees.Single(employee => employee.Id == idToSelect);
		employees.Add(foundEmployee);
	}

	return employees;
}

The GetEmployeesWithImplicitConnection is calling the method above with the implicit connections managed by Entity Framework.

private static IEnumerable GetEmployeesWithImplicitConnection(IEnumerable ids)
{
	using (var context = new CompanyDbContext())
	{
		return GetEmployees(ids, context);
	}
}

The method GetEmployeesWithExplicitConnection is calling the method GetEmployees by setting the explicit connection.

private static IEnumerable GetEmployeesWithExplicitConnection(IEnumerable ids)
{
	string connectionString = ConfigurationManager.ConnectionStrings["CompanyDb"].ConnectionString;
	using (var connection = new SqlConnection(connectionString))
	{
		connection.Open();
		using (var context = new CompanyDbContext(connection, false))
		{
			return GetEmployees(ids, context);
		}
	}
}

This is only small detail which makes huge difference for the purpose of performance. Why? The Entity Framework create for each command own connection.

Audit Login means the connection was open and the Audit Logout stays for the connection closed. Calling the method GetEmployeesWithImplicitConnection with the list of ten thousand ids, is opening and closing the connection ten thousand times!

Calling the method GetEmployeesWithExplicitConnection is opening and closing the connection only one time because the one connection is shared for all commands.

The performance comparison

The performance improvement isn’t huge but opening and closing the connections consume SQL Server resources as well.