Desktop C# SQL Server (LocalDB) database access patterns

Question

I'm coming from a Native C++ / PHP / MySQL / SQLite background.

Spending this weekend learning C# / WinForms / SQL Server / ASP.NET. And it all seems to work differently. Especially considering I no longer know exactly what happens under the hood, where I can optimize things and so on.

Needing to work with SQL Server (LocalDB) I think I noticed a weird database access pattern in most of the online examples I read + video tutorials (I got 2 books from Amazon but they arrive next week so currently, to my shame, learning basics online).

Every time they access the Database in those examples, they open and close a SqlConnection for each query.

using(var sql = new SqlConnection())
{
    sql.Open();
    // do Sql stuff here
}

For a C++ guy, this is making me very nervous:

What's the overhead of open/close connections all the time when I need to do a query?
Why not open an object and reuse it when required?

Can anyone tell me if this a performance-friendly DB access pattern in Desktop C# or go with Plan B? The end-result will be a C# Windows Service featuring an IOCP Server (which I figured out already) that should deal with up to 1,000 connections. It won't be very data intensive. But even with 100 clients, Sql Open/Close operations overhead, if any, can add up quickly.

I also noticed MultipleActiveResultSets=True; that should make this especially friendly for multiple-reads. So, I would imagine a single connection for the entire application's read-access & short-write with MARS should do the trick?! And dedicated connections for larger INSERT/UPDATE.

Plan B: I've initially thought about creating a connection pool for short reading / writing operations. And another one for longer read/write operations. And looping through it myself... Or maybe one connection per client but I'm not sure that won't be quite abusive.

You should open and close your connection each time. this is a much better coding practice than having a global connection variable. It allows for transience in your connection. You should also consider entity framework for your database access layer. It will handle most of this for you and work with all the technologies you've listed — crthompson
– crthompson, Commented Jul 26, 2014 at 23:43
@paqogomez Will look into EF. Noticed it popping here and there but it's hard to get past the optimize SQL queries by hand mentality. — CodeAngry
– CodeAngry, Commented Jul 27, 2014 at 0:29
Just as @erik suggests, be wary of optimizing something before you know its a problem. EF does a pretty good job handling that, and if it fails there are ways to over ride it. — crthompson
– crthompson, Commented Jul 27, 2014 at 13:34

CodeAngry · Accepted Answer · 2014-07-27 00:26:04Z

3

Actually, there is very little performance issue here, and the small amount of overhead is made up for by a huge increase in maintainability.

First, SqlConnection uses ADO.NET connection pooling by default. So connections are not actually opened and closed to the server. Instead, internally ADO.NET has a pool of connections that it reuses when appropriate, grouping them by ConnectionString. It's very good at managing these resources, so long as you are good about cleaning up your objects when you are done with them.

This is part of what makes this work. By closing the connection, you are telling the connection pool that the connection can be reused by a different SqlConnection, so in effect, what you view as a performance problem is actually a performance optimization.

Coming from native programming, the first thing you have to learn about writing code in a managed world is that you HAVE to release your resources, because otherwise the garbage collector won't be able to efficiently clean them up. I know your first impulse is to try and manage the lifetimes yourself, but you should only do this when it is absolutely necessary.

The second thing to learn is to stop "getting nervous" about things you view as potential performance issues... Only optimize when you KNOW them to be a problem (ie, you have used a profiler and found that the normal way isn't as efficient as you would like it to.

As always, read the documentation:

http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

edited Jul 27, 2014 at 0:26

CodeAngry

13.1k3 gold badges54 silver badges66 bronze badges

answered Jul 27, 2014 at 0:13

Erik Funkenbusch

93.7k29 gold badges201 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

CodeAngry Over a year ago

The referenced page is great. I initially thought the pooling of connection is an ASP.NET exclusive thing. Didn't think it's desktop too. Thanks!

CodeAngry Over a year ago

One tiny question: This part the first thing you have to learn about writing code in a managed world is that you HAVE to release your resources is quite redundant. --- Because, coming from a native world I live and die by releasing resources & memory.

Erik Funkenbusch Over a year ago

@CodeAngry - You misunderstand. My point was that your impulse is to manage resources yourself, rather than letting the runtime do it for you. That means releasing resources, rather than trying to create your own caches or pools...

Collectives™ on Stack Overflow

Desktop C# SQL Server (LocalDB) database access patterns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related