Datasnap Connection Too Slow When No Other Software is Connected to the Database on the Server

[MikeMon 12]

I have a very strange situation. I have developed an online ordering app connecting to a Datasnap server. This is the strange part: purely by luck, I found out that when I have a database connection active (by another software) on the server where the datasnap server resides, the app runs very fast; when I don't it is way too slow(er). I use Interbase and Firedac. Does this make sense? Has anyone come across an issue like this? It's like when the database has no connections active, the first connection is taking way too long.

[mjustin 23]

Do you have a test system? If yes, can you reproduce the slowness there?

And is it taking a constant amount of time to establish the first connection, ir is it varying?

(I remember a similar issue some years ago which only occured in the production server, and was resolved by a new installation of the InterBase server)

[MikeMon 12]

2 minutes ago, mjustin said:

Do you have a test system? If yes, can you reproduce the slowness there?

And is it taking a constant amount of time to establish the first connection, ir is it varying?

(I remember a similar issue some years ago which only occured in the production server, and was resolved by a new installation of the InterBase server)

Yes. I checked on 2 different servers. On both servers, if another software is running that is connected to the same database, the Datasnap connections from clients are much faster. Very strange.

[Guest]

Also try to add this to your application 

uses
  .., MMSystem;
  
var
  uPeriod:Cardinal = 1; // also you might want to try 0
  
procedure TForm9.FormCreate(Sender: TObject);
begin
  timeBeginPeriod(uPeriod);
..

procedure TForm9.FormDestroy(Sender: TObject);
begin
  timeEndPeriod(uPeriod);
..

If that did change your application behaviour or speed then you are depending on timers or using Sleep(n) where n<16, that is a waste of time and inefficiency by design and can produce such inconsistency in performance. 

In other words if that helped then i suggest to rethink your timers usage, try different approach to remove the dependency on all timers (or Sleep usage) or at least redesign it with that in mind.

[MikeMon 12]

My application as in the client (app)?

i don’t use any timers. Every single call to the datasnap server is much slower except if there is already a connection to the database on the server side.

How does this code help exactly?

[Lars Fosdal 1792]

It may be because the datasnap server tears down the connection when nobody is using it.

It may be configurable.  What is the setting of Server.KeepAlive ?

[MikeMon 12]

18 minutes ago, Lars Fosdal said:

It may be because the datasnap server tears down the connection when nobody is using it.

It may be configurable.  What is the setting of Server.KeepAlive ?

TDSRestConnection.Connection is Keep-Alive.

But like I said earlier, when I have a connection to the database by another software, there is no speed issue on the client. That's the strange part.

[Lars Fosdal 1792]

I am talking about the server-side KeepAlive setting?

[MikeMon 12]

1 minute ago, Lars Fosdal said:

I am talking about the server-side KeepAlive setting?

There is no KeepAlive setting on the server. I am using Datasnap, not RAD Server.

[Lars Fosdal 1792]

Surely there must be some sort of mechanism that governs the lifetime of a DS session?

[MikeMon 12]

1 minute ago, Lars Fosdal said:

Surely there must be some sort of mechanism that governs the lifetime of a DS session?

Yes. It's the SessionTimeout of the TDSHTTPWebDispatcher. Currently it's 20 minutes. But again, if that was the problem, I would ALWAYS have the problem; regardless if there is another connection active or not.

[Lars Fosdal 1792]

I'd try to find the classes that handles DB connectivity and see where in the codepath you are when the connection is dropped. Somewhere in that callstack there must be a logical explanation.

 

Otherwise, add a watchdog connection that keeps things alive?  Brute force, I know... but...

[MikeMon 12]

1 minute ago, Lars Fosdal said:

I'd try to find the classes that handles DB connectivity and see where in the codepath you are when the connection is dropped. Somewhere in that callstack there must be a logical explanation.

 

Otherwise, add a watchdog connection that keeps things alive?  Brute force, I know... but...

OK Lars. Thank you!!

[Lars Fosdal 1792]

BTW: What is the setting of this one?
https://docwiki.embarcadero.com/RADStudio/Sydney/en/Server_Class_LifeCycle

[MikeMon 12]

2 minutes ago, Lars Fosdal said:

BTW: What is the setting of this one?
https://docwiki.embarcadero.com/RADStudio/Sydney/en/Server_Class_LifeCycle

Session. And it's a rest connection.

Edited September 6, 2021 by MikeMon

[Guest]

18 hours ago, MikeMon said:

This is the strange part: purely by luck, I found out that when I have a database connection active (by another software) on the server where the datasnap server resides, the app runs very fast; when I don't it is way too slow(er).

It comes to mind that Firebird (i know) have/had different architectures. I do not know about interbase these days.

The classic would be faster to connect but would create a new process.

The superserver (?) would be one process with shares cache.

So it is conceivable that the first connection to a database would take longer even though another application/process is the active connection.

But it's pure speculation as i have no knowledge of modern Interbase nor Datasnap.