Why Access reports are slow on some RD but fast on others

[TSiqi]

Using server 2008 r2 with Remote Desktop Hosting;
With five users connected, each having their own frontend and sharing a backend.

Well, I have run extensive testing regarding this issue and have found the following:
The slower sessions of the database compared to the faster sessions have these characteristics . ((A slower session or faster session has nothing to do with the general response, but as indicated below primarily just with generating reports))
1. After login to the database, the faster session will bring the dashboard up about 4 times faster than the slow session
2. The reports will display about 4 times faster than the slower session
3. Opening various forms though, all come up about the same speed, regardless whether I am in a slow session or fast session
4. After a reboot or deleting the cache, the faster session randomly happens on the second, third or fourth remote desktop session.
5. After I have five sessions running, usually there are only one or two that are fast.
6. If I close the database in a faster session and then reopen it, it becomes slow.
7. I have not been able to get more than one or two sessions into this fast mode, and again if I close the database and reopen within a given session it becomes slow.
8. Once they are all opening slow again, it seems I have to reboot or empty the cache in order to get to another fast session, though only after the second, third or fourth remote desktop session.
9. Looking at the memory using RamMap, a portion of the database resides in memory as well as a portion of Microsoft access. Also each of the frontends for each remote desktop session remains in memory.
10. Since when I have all five remote desktop sessions open with each running the database and only one or two of them fast, I think the issue lies more with each individual session’s use of Microsoft Access specifically regarding the generation of reports. In other words each session is looking at the same database in memory cache but only a couple are able to produce the reports fast.

I have done some research into this but have not found anything about this scenario and do not currently have the skill set in Server 2008 r2, to know where to go from here.
Any suggestions or ideas would be most helpful!!

[twinnyfo]

TSiqi,

Is this an Access Question or a Server 2008 Question?

From the Access side, whenever you have the DB FEs connected to a network, you will experience degradation in speed, and I think it is simply the nature of the MS Access DB system.

One thing that I have done with my DB, is to copy the FE to the User's system every time they open the DB (they don't click a shortcut to the DB, but a shortcut to a Command Script. The command Script copies the DB to the user's local folders, then opens it from there.

There is still some degradation in speed, but the network connection now only has to work with the data from the BE. This also helps me, because as I make updates to the FE, I know that hte user will always have the latest version.

Here is a sample of the Command Script, which is saved as [FileName].cmd:

@ECHO OFF
IF NOT EXIST %userprofile%\Documents\[DB Folder\DBName].accde MKDIR %userprofile%\Documents\[DBFolder] COPY [\\NetworkPath\DBPath\DBName].accde %userprofile%\Documents\[DBFolder\DBName].accde /Y
START /I "MSAccess.exe" %userprofile%\Documents\[DBFolder\DBName].accde

Hope this hepps!

[TSiqi]

Thanks for responding Twinnyfo!

I agree and always have a fresh end of the Front-End copied to the user's computer each time they start the application.

The situation I am testing here has to do with Remote Desktop Connections to a 2008 r2 server that is hosting Remote Desktop Services. Each user has a Virtual Desktop with their own documents folder where the front-end gets copied to and the backend is in a shared folder. So there is no degregation due to network speed.

As summarized in 10. above, each user's instance of the database works the same except that two of them have very fast generation of reports. SO, I think the question has to do with both MS Access and Server 2008 r2 running Remote Desktop Services. Apparently a couple instances are somehow started differently; not sure if it has to do with Jet/Ace or just what?

Thanks!

[twinnyfo]

Unfortunately, that is a bit outside my realm of expertise. I know there are some 100 pound brains on this forum who should eventually find a solution for you....

[TSiqi]

Well it turns out the issue has to due with having the Printer checked as a local resource in the Remote Desktop Connection properties.
Unchecking it allowed that session to display reports fast.
It may be that the latency involved in accessing the internet thru a satellite service was also part of the problem.
Still not sure why the problem was not happening with every session with Printer checked. But just happy to see what the issue was!!
Thanks everyone for those who read and twinnyfo for your reply!!

[twinnyfo]

TSiqi,

Glad you eventually found a solution. It's hard to understand why some things get affected by something so small and simple.

Hope to see you on here again, some time!

[zmbd]

Reset the best answer based on the following:

TSiqi:
(...)Still not sure why the problem was not happening with every session with Printer checked.(...)

which indicates that there is still an underlying issue that someone may yet find an answer for...

[TSiqi]

Thanks ZMBD!

I did verify that the issue was primarily related to accessing the server thru a satellite internet service probably due to the latency.

When I was connected thru another non-satellite internet provider, having the printer checked for local resources was now just as fast as having it unchecked.

[zmbd]

When you're using those satellite services we'll have to play Quake/Doom... I might stand a chance.

The latency would make sense if what you are seeing is something like a longish delay and then presto-report-out-of-hat magic trick. I'm not an IT-infrastructure type so I don't know how to help you there.... but it is sounding to be hardware and not Access related. NTL-I keep an eye out for anything.

[TSiqi]

Thanks Zmbd!

Likewise, if I come across anything else that would address this issue further, will do so!