Categories
ado datasnap delphi rest sql

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

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?