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.

image

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.

image

The performance comparison

GetEmployeesWithImplicitConnection GetEmployeesWithExplicitConnection
10 000 entries 00:00:06.1619521 00:00:03.6564162
100 000 entries 00:00:43.2776995 00:00:33.8298577
1 000 000 entries 00:08:19.1952462 00:06:35.9054971

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

About the Author Anton Kalcik

I’m enthusiastic with a passion for working with and for people. I love what I do. Most of the time, I assist people in the creation of valuable software. I’m a software engineer and entrepreneur specializing in .NET  and Microsoft Azure. I offer Code Katas, Coding Dojos, workshops and talks about .NET, Microsoft Azure, DevOps, Agile Methodologies and Clean Code. I'm founder of CoderDojo Wien and president of digital.austria association.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.