How can I increase the number of users of my system?

Question

I have a client/server application that depends on MS SQL database for backend storage, the server application is a WCF TCP service that handles the clients requests by selecting from the database set of information.

The service is configured as PerSession and support 500 sessions. In the client side the user can open different views each view has an object of my service (proxy object) so each view is a session; each view has a Threading timer that requests the data from the server using the proxy object every second.

The application is running good for small number of users with 8 views but when I increase the number a Time out exception occured in the SqlConnection.Open as there is no avilable connection in the connection pool.

There is session/object for each view as the WCF service is not thread safe; it depends on single threaded database access layer that uses SqlDataReaders and SqlAdapters.

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?

Shall I change the DB layer and make it multithreaded and make the WCF service single? I think this is the bottleneck in the design (can you recommend an article for multithreading in ADO.net)

P.S:

can I replace the database with in memory database or any faster storage?

According to answers:

I changed the backend service to close the connection per method call, but I am facing the same problem as the number of users increases the latency increases (more than one second) , does this mean I have to change the database solution? and what is the alternative solution?.

After that:

I tried two solutions

1. Open and close the sql connection per method call
2. Increase the size of the connection pool in the connection string

But both gives me latency if I increase the number of users, at the same time I monitor the queries in the sql porfiler it seems each query does not take more than 20 milliseconds.

Answer 1

One of the best, free online multi-threading books is Joe Albahari's Threading in C#

Jon Skeet's Multi-threading in .NET: Introduction and suggestions is excellent also

Answer 2

Instead of 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 in the pool, so reconnecting it when it's reused is fast.)

That way each session will only use a connection when it really needs it. You should see a dramatic decrease in number of connections needed for a specific number of sessions, and your application should be able to handle a lot more sessions.

Also, as the sessions doesn't hold on to the connections, you will not have an absolute limit on the number of sessions. If there isn't a connection available when a session wants one, it will retry for a while until there is one available. Only if the server is truly overloaded (i.e. requests come in faster than the server can handle them) for a long time, you will get timeout errors.

Answer 3

Aggressively close connections. Double check your wrapping all ADO.NET components that implement IDisposible with using blocks or try/finally blocks that call IDisposable and .Close on the connections, commands, datareaders, etc.

800 open connections is a connection leak, unless you someone with like VISA's or google's number of simultaneous transactions.

Answer 4

I have to agree with earlier posters, you should look into closing connections. In the mean time you can specify a connection pool size in your connectionstring: Max Pool Size

data source=RemoteHostName;initial catalog=myDb;password=sa;user id=sa;
Max pool size = 200;

Answer 5

Found an article on SQL Server Connection Pooling (ADO.NET) which you might find useful.

SQL Server Connection Pooling (ADO.NET)

Answer 6

After set of experiments I increased the number fo users for my system 800% through set of changes

1. Maximize the connection pool size
2. Create single proxy object for the client and send his requests in serial not parallel
3. optimize the sql queries and use some caching on the service side