SQL responds quickly for first data request, then sluggish for any others (1 Viewer)

Auntiejack56

Registered User.
Local time
Today, 21:10
Joined
Aug 7, 2017
Messages
175
Hi,
I have an Access 365 FE with a SQL Server Express backend. It works fine in test on my machine (which has both FE and BE).
FE is 4mb, backend has half a doz fact tables with 2-300 records in each.

I've migrated the SQL to the client's (cloud) server, and connected the FE to it from the client site - FE is on one user's C drive.

If I open a parent form containing a subform set to a datasheet, bam - records return instantly. Change the sourceobject of the subform to a different datasheet, bam - shows the new records.
But if I leave the datasheet open and attempt to open a second form of any size, there is a 10 second delay. If the form is complex, then the delay is longer. When I say complex, I mean dynamically assigning a SQL String as a rowsource for a listbox. (That's not complex!).

Similarly, if I open a table, records show instantly. If I open a second table, even one containing no records at all, there is a 10 second delay.

Lastly, if I create a datasheet and then set it to Snapshot (or even if I set the recordsource to a passthru query), then open it, records show instantly. Then open a second form, 10 second min delay.

Anybody have any ideas that might help? Is it some form of dynamic allocation of the number of connections? It doesn't seem to relate to locking because I've used readonly datasheets to avoid that, and the problem persists.

Jack
 

Minty

AWF VIP
Local time
Today, 10:10
Joined
Jul 26, 2013
Messages
10,353
This sounds like a connection throttle somewhere, which seems odd.
What type of cloud server is it installed on?
 

Auntiejack56

Registered User.
Local time
Today, 21:10
Joined
Aug 7, 2017
Messages
175
As far as I know, it's in an Azure environment.
 

Minty

AWF VIP
Local time
Today, 10:10
Joined
Jul 26, 2013
Messages
10,353
I'm wondering how they have created it - it sounds like they are possibly using an Azure VM with SQL Server Express loaded on it. That may not be optimal.
I'm afraid my main experience is with an Azure SQL Servers which is quite a different setup.

You could try loading up an Azure Dev SQL server (free for the first month) and see if that performs in a similar fashion.
 

Auntiejack56

Registered User.
Local time
Today, 21:10
Joined
Aug 7, 2017
Messages
175
Thanks Minty, I in fact took your advice, got myself an Azure database, bustled through all the connection stuff, did an hours ETL to fill with data and hey presto, the goldarn application runs like a dream. So armed with that information I've fronted up to the DBA and did a 'ploise exlpine'. Still awaiting their explanation.
 

Minty

AWF VIP
Local time
Today, 10:10
Joined
Jul 26, 2013
Messages
10,353
Good luck, be interesting to see how that pans out and what the root cause is.
 

Users who are viewing this thread

Top Bottom