DataSnap server - share DB connection or new connection per client request?

Question

I have a Delphi XE2 DataSnap server (Windows service) connected to a backend MS SQL Server 2008 (same server box) serving REST client requests.
Everything has been working great for some time until recently, I had an issue where for some reason the DataSnap service lost connection to the SQL Server.

The service failed to re-establish a connection and I had to restart the DataSnap service to continue.
This got me thinking because currently the service only uses 1 SQL connection (TADOConnection) shared for all the client requests. I did this because I didn't want the overhead of instantiating a new SQL connection for every client request.

I'm considering whether it actually would be better to have a separate SQL connection for each request and if the overhead would be noticeable - can anybody comment/advise on this?

Answer 1

This is where having a well-constructed Data Access Layer that can be modified to try different approaches and isolates your db connection from the rest of your code is really useful.

The pooling approach (as suggested by mjn) is strongly recommended if you're using MIDAS (DataSnap) from your clients to your DataSnap server as I've found it has a large connection overhead.

I've built a few web servcies (fairly low traffic) that use a plain TADOConnection at run-time and have found the overhead of establishing a database connection to be negligible, certainly compared with the overall network latency from the device to the server and back.
If you found TADOConnection still gave too much overhead in a high-traffic environment you could easily add your own connection pooling as above to such a system.