1

I have developed a Windows service that uses database connections.

I have created the following field:

private MyDBEntities _db;

and in OnStart I have:

_db = new MyDBEntities();

Then the service does its work.

In OnStop method I have:

_db.Dispose();
_db = null;

Is there a disadvantage with this approach? For performance reasons, I need the database (which is SQL Server) to be opened all the time, while the service is running.

Thanks Jaime

jstuardo
  • 3,901
  • 14
  • 61
  • 136
  • Can you give us a reason as to why it should alwasy be connected? Why can't it be connected only when you use it? This might give us better incite on what to do. – Train Dec 23 '19 at 21:37
  • This is a very critical application with respect to performance. Some external device generates an event that is captured by my service. In that event, I need to run some queries to database. It is not acceptable that every time the event is received, the database is opened, it does the task and then it is closed. – jstuardo Dec 23 '19 at 21:40
  • 1
    This should not be an issue. Read about connection pooling https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling – William Xifaras Dec 23 '19 at 21:45
  • 1. If that's the case, remove all that code and make it a static class. If you're going to keep it open all the time why are you recreating an instance of all the entities and calling dispose? 2. The performance overhead of opening a connection is minimal to the point you won't ever notice. Myabe you could give us some more context and code? As of now the approach doesn't make sense. – Train Dec 23 '19 at 21:46
  • You can't profile guesses and we can't review code you won't show us. As @William says, Entity Framework implements connection pooling so it won't “open then close” the connection for every request. Consult [Entity Framework and Connection Pooling](https://stackoverflow.com/questions/3653009/) and start writing test code. – Dour High Arch Dec 23 '19 at 21:47
  • @Train I wouldn't make it `static` because 1) OP is only creating the connection once per process run so `static` would have no net benefit 2) the general problem of making connections or client proxies static is that it makes it difficult to recreate them should they fault from the perspective of client code where they are used (particularly if they are injected). However in saying that, its less of a problem in OPs case –  Dec 23 '19 at 21:53
  • @MickyD Yeah you're right. After reading your comment I misunderstood the process in which the context was being run. – Train Dec 30 '19 at 16:39
  • @Train not a problem good sir. Happy New Year! –  Dec 31 '19 at 01:00
  • @MickyD Likewise! – Train Dec 31 '19 at 16:25

2 Answers2

1

If your service is the only app that accesses this database, it shouldn't have any performance decrease. However, in my opinion, it is not the best approach to have a long-lived connection to the database. Imagine a situation where you don't keep your database on your server, but you use some cloud provider (Google, AWS, Azure). With cloud solutions, the address of your server may not be fixed, and it may vary over time. It may happen that IP address will change during the execution of one query (most likely, you'll get SqlTransientException or similar, I don't remember).

If your service will be the only one app that accesses the database and you will have only the one instance of it - then this approach might be beneficial in terms of performance - as you don't have to open and close connection always. However, you have to remember that with this approach, many other issues may appear (you may have to reconnect from stale connection, connect to other replica instances, or destroy existing connection because of something I don't think about at the moment). Moreover, remember about multithreading issues that most likely will arise with this approach if you won't develop it correctly.

IMHO - you should open a connection to the database always when it is needed, and close just after using it. You'll avoid most of the issues I've mentioned earlier.

Rafal Kozlowski
  • 224
  • 2
  • 2
  • Yes.. you are right... I have not thought about that.... and this is not the only application what uses the connection. There is a Winforms app in the same PC and in one other PC that accesses the same database. Maybe I could have issues in the future. – jstuardo Dec 23 '19 at 22:16
0

Having a Singleton context will cause threads to lock on SaveChanges() (slowing performance).

Also each event (which i presume run asynchronously) could possibly save some other event information causing unexpected behavior.

As someone already pointed out you can use connection pooling to avoid connection issue and dispose the context on each request/event fired.

VeNoMiS
  • 330
  • 5
  • 15
  • 1) I'm not sure anyone mentioned _writing_ to a database 2) no-one mentioned threads 3) you [shouldn't be sharing](https://stackoverflow.com/a/6126693/585968) an ORM context between threads anyway 4) why do you think there are async events? –  Dec 23 '19 at 22:11
  • @MickyD just my assumption – VeNoMiS Dec 23 '19 at 22:16