I have an WebService coded in asp net mvc4 that runs in a cloud server. This cloud server have an IIS 8 and a mysql server. This server is dedicated to this application.
I have a growing number of request to this server. Each request requires a connection to the database (70% of the queries are simple 'select', 25% are joins with 2 or 3 tables, and 5% are inserts).
The server access the database and give as answer a simple xml with the data fetched (the xml is around 0.2 kb to 2kb)
The webservice is always running in the server.
With each request, my webserver opens a connection the database (stored in the same machine), operate, close the connection then respond with a xml.
Nowadays it only happens a few times per minute. My connector class is a Singleton.
In this scenario, should I make this connection persistent? Will this help to improve the scalability of my app? Any adivices on what should I do to make it more scalable?
A few points:
Remember that Premature Optimization is the Root of All Evil. If this isn't a performance bottleneck at the moment, I wouldn't spend too much time on it.
Singletons are very, very bad in a multithreaded application. They represent the antithesis of scalability. In a high-traffic application, you'll see race conditions, among other issues. Accessing a static object or method may involve a context switch/modification that can severely degrade performance. I've seen apps gain a 10x speedup simply by getting rid of singletons and other static objects and replacing them with non-singletons ("multi-tons?" :/ )
ADO.NET, the underlying database provider behind entity framework and most other db libraries on Windows, already performs connection pooling, which significantly reduces the overhead of connecting to the database. It is very unlikely that reimplementing that functionality in your application (which is effectively what you're doing by attempting to keep connections open at the application layer rather than the data layer) to your application will improve performance.
WCF inherently supports persistent connections, so you may want to consider implementing your web service in WCF rather than a RESTful or SOAP (asmx) service if you really need this functionality. However, I feel that WCF is a fairly heavy approach to web service implementation, and (again, in my opinion) leads to unnecessary complexity.