How can I increase the number of users on my system?
I have a client / server application that depends on an MS SQL database for backend memory, the server application is a WCF TCP service that handles client requests by picking from a database set.
The service is configured as PerSession and supports 500 sessions. On the client side, the user can open different views, each view has my service object (proxy object), so each view is a session; each view has a Threading timer that requests data from the server using a proxy object every second.
The app works well for a small number of users with 8 views, but when I increase the number, a Time out exception happened in SqlConnection.Open because there is no avilable connection in the connection pool.
There is a session / object for each view, since the WCF service is not thread safe; it depends on the single-threaded database access layer that the SqlDataReaders and SqlAdapters use.
Is there a way to increase the number of database connections? it seems 800 is the maximum! From your point of view, how can I increase the number of users?
Should I change the DB layer and make it multithreaded and make the WCF service one? I think this is a design bottleneck (can you recommend an article for multithreading at ADO.net)
PS:
Is it possible to replace the database in memory database or any faster storage?
According to the answers:
I changed the backend service to close the connection for the method call, but I faced the same problem as the number of users increasing the delay (more than one second), does this mean I need to change my database solution? and what is the alternative solution?
Thereafter:
I tried two solutions
- Open and close sql connection to call the method.
- Increase the size of the connection pool in the connection string
But both give me latency if I increase the number of users, at the same time I track the queries in the sql porfiler, it seems like each query takes no more than 20 milliseconds.
a source to share
Rather than keeping the database connection open for each session, close it at the end of each client call so that the connection is returned to the connection pool. (The connection will not be completely closed when pooled, so reconnecting to it when reused is fast.)
This way, each session will only use the connection when it really needs it. You should see a dramatic reduction in the number of connections required for a given number of sessions, and your application should be able to handle many more sessions.
Also, since sessions do not support connections, you will not have an absolute limit on the number of sessions. If the connection is not available when the session wants it, it will retry for a while until one access appears. Only if the server is really overloaded (i.e. requests are faster than the server can handle them) for a long time, will you get timeout errors.
a source to share
One of the best free online multi-threaded books - Joe Albahari Threading in C #
Jon Skeet Multithreading in .NET: An introduction and suggestions are great too
a source to share
Aggressively close connections. Double check your packaging for all ADO.NET components that implement IDisposible using try / finally blocks or blocks that call IDisposable and .Close on connections, commands, datareaders, etc.
800 open connections is a connection leak unless you have someone like VISA or Google concurrent transactions.
a source to share
I have to agree with earlier posters, you have to look into closing connections. At the same time, you can specify the size of the connection pool in the connection string: Max. Pool size
data source=RemoteHostName;initial catalog=myDb;password=sa;user id=sa;
Max pool size = 200;
a source to share