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

5
votes

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?

1
sqldelphirestadodatasnap
A very popular technique in multi-threaded server apps is to use a database connection pool, which provides a number of pre-configured, ready-to-use connections. Advanced implementations also can verify that the database server is still reachable, ususally with a ping SQL statement. – mjn
It all depends how many client requests you have of course. And are you just reading or also update database? If not many requests I would go for each client has own SQL connection instantiated at runtime. In that case you are not bothered with lost connection to database for whatever reason. Database connection is over tcp I presume? But REST is stateless. How do you share database connection for all client requests in that case? Are you using critical section for that? Maybe your issue is related to problem with multi-threading. – Erwin

1 Answers

3
votes

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.