0

I have written an application which uses a Datasnap Server over TCP installed as a windows service, and a VCL Winform client using Delphi 2010. The server application is connecting to a SQL Server 2008 instance on the same box. Everything works fine and dandy when I have one user logged in, as soon as a second user connects they both start to get program not responding. I thought that the problem may be a bottle neck in some queries in SQL but I have run a trace all day long and do not see any problems. I subsequently found that an older version of the midas.dll was installed, so I updated that to a later version. But still no joy. I am not sure where to start looking for the problem. I used Bob Swart's Data how you want it white paper as my guide to producing a Datasnap Server and Client, and on a smaller implementation it seemed to work fine. Any pointers on where to start looking would be very much appreciated, before I revert the application back to a thick client and rewriting the server as a .net WCF service using Entity Framework etc etc.

Mattgb
  • 408
  • 3
  • 9
  • Is there a particular call which hangs, or is the connection itself? What did you found while debugging your server? – jachguate Dec 12 '12 at 20:10
  • No particular I am told. I am going to create a client app which I will be able to run multiple instances of via different VMs. This will make calls to the larger datasets and hopefully this will allow me to replicate the problem in house. – Mattgb Dec 13 '12 at 09:10
  • It looks like the problem was being caused by the trace file that I had left in the server code, and it was causing an i/o error 32. Schoolboy error I guess. However, I think I may still have a situation where I could potentially get a bottle neck connecting to the SQL Server, I would have preferred a seperate connection per session but that does not seem to be the case, or should I set the db Objects to ASync? – Mattgb Dec 13 '12 at 12:17
  • What you mean by _a separate connection per session_? Usually I do a different database connection per DataSnap client, if it is what you mean, it is the natural way to go to me. – jachguate Dec 13 '12 at 18:03
  • Are your users connecting to different databases on the server? If not, why would you want each user to have a different database connection? Isn't it more efficient to use the same `TSQLConnection` for all users and let the `TSQLConnection` spawn new connections as needed to service all of the database requests (e.g., spawn a new connection if the existing connection is busy running another query)? – James L. Dec 13 '12 at 19:12
  • If DataSnap Server is installed as Windows service, then better to change DSServer.AutoStart property to False is my experience. Default value is true. – Erwin Dec 13 '12 at 21:46
  • @jachguate yes that is what I mean, does datasnap do this by default or do I need to apply some code or change settings? – Mattgb Dec 14 '12 at 14:20
  • @JamesL. If that is the way that it works then I am more than happy with that, and I agree I would not need to do anything else. – Mattgb Dec 14 '12 at 14:21
  • @Erwin I do have AutoStart disabled and check in ServiceStart event whether it has started and then start it if requried, I did this so that I could dynamically set the Port. – Mattgb Dec 14 '12 at 14:23
  • @JamesL. how do you do that: _let TSQLConnection spawn new connections as needed_?. – jachguate Dec 14 '12 at 19:02
  • @Mattgb it depends on your ServerClass LifeCycle and where your SQLConnection object lives. A Invocation or Session LifeCycle plus your SQLConnection inside your server method class gives you a per invocation or per session connection. Depending on your server load, you may want to implement a connection pool to improve the efficiency. – jachguate Dec 14 '12 at 19:17
  • @jachguate - See `TSQLConnection.AutoClone` (http://docwiki.embarcadero.com/Libraries/XE2/en/Data.SqlExpr.TSQLConnection.AutoClone). – James L. Dec 14 '12 at 20:56

1 Answers1

1

It could be the same problem with multi-threading as described here http://blog.marcocantu.com/blog/datasnap_webinar_bedelphi_material.html It's describing how to patch DataSnap in Delphi XE3 but the same error I found in previous version like XE.

user1976837
  • 21
  • 3