Speed Over Remote Desktop and SQL (1 Viewer)

Djblois

Registered User.
Local time
Today, 14:35
Joined
Jan 26, 2009
Messages
598
I have an Access Database that I created for users to do special orders. Inside the office it is quite fast but when users go out of the office, on their laptops and using a remote desktop connection, the speed is super slow. I already gave them a copy of the front end on their machines to reduce that load that needs to go over the network - it is faster but still very slow. Now, I was wondering if I switch the backend to SQL server (which we have a license) if that would be a significant difference? This is a big job that if not worth it would be a waste. There are not gigabyte's of data. Should it make it quicker?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:35
Joined
Oct 29, 2018
Messages
21,358
Hi. Unfortunately, simply upgrading the BE to SQL Server doesn't automatically means "better." There are other factors involved. Your FE might be fine with using SQL Server, or it might need to be "optimized" to use SQL Server because it's pretty much stuck with the Access way of doing things. It's had to tell without seeing or knowing anything about the database and how it's used. Just my 2 cents...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:35
Joined
Feb 19, 2013
Messages
16,553
I agree with DBG - Access as a db is as 'fast' as SQL server. What matters is the way your db is designed and the speed of the connection.

See this link about optimising the design of your app - all things you would need to do if you moved to SQL server https://www.access-programmers.co.uk/forums/showthread.php?t=291269

With regards the connection, investigate what bandwidth your remote desktop has available to it - it may be the bandwidth can be improved - at both ends
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:35
Joined
Feb 28, 2001
Messages
27,001
I already gave them a copy of the front end on their machines to reduce that load that needs to go over the network

Not sure how you meant this, but there are interpretations which could indicate that you are setting yourself up for corruption. Can you be a little bit more specific?

As to "speed" questions - the first bit of advice is to look at your tables used for searching and verify that you have indexes on the most commonly used search fields. I.e. if you often do a search on last name, then that field should be indexed. If you often do a search on postal codes, THAT should be indexed.

You don't bother to index things like "gender" (M/F) because that index doesn't filter out much and involves a lot of overhead for little returned value.
 

Djblois

Registered User.
Local time
Today, 14:35
Joined
Jan 26, 2009
Messages
598
What do you mean corruptions? I am loading the front end on to their machines while the back end sits on the server. I have already indexed, and gone through 99% of this list http://www.fmsinc.com/tpapers/faster/. I know the issue is mostly the network and speed of the device because the database is quite fast in the office with the Wifi or the wired connection.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:35
Joined
Sep 12, 2006
Messages
15,614
If you are connecting to the data from out of the office, but running the database on a remote PC (ie not on the same LAN) then the performance is unlikely to be acceptable, although it depends to some degree on the functions you are using, and the dbs design - which is what you are seeing, I think.

to get optimum performance, you need to connect by remote desktop to a terminal server, which will be on the same LAN as the data.

Alternatively you could use a SQL Server backend database and host that in the cloud, then connect to that data, which should be usable as long as you have decent speed internet, but that isn't a "free" option.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:35
Joined
Feb 19, 2002
Messages
42,976
When using Remote Desktop, each user should be starting a private session on the SERVER. NOTHING should be running on the local PC. You should be getting faster response using RD provided your BE database is on the same server as the RD session than you would get for local users on your LAN.

If you are running a VPN, then responsiveness will be poor with an Access BE but better with SQL Server but ONLY if your Access FE is properly designed to act as a client server FE. Old style Access apps that link to tables and use form filters will be exceptionally slow over a VPN even with SQL Server BE's. The whole point of using SQL Server is to have the server do the heavy lifting and you are not taking advantage of that unless ALL forms are bound to queries that include selection criteria that severely limits the number of rows returned by a form.
 

sonic8

AWF VIP
Local time
Today, 22:35
Joined
Oct 27, 2015
Messages
998
Inside the office it is quite fast but when users go out of the office, on their laptops and using a remote desktop connection, the speed is super slow. I already gave them a copy of the front end on their machines to reduce that load that needs to go over the network - it is faster but still very slow.
Please analyze/clarify:


When using remote desktop, is the application slow or the update of the local screen on the user's computer? - The former would be a problem inside your company network, the latter (most likely) a problem of the external connection.


The frontend on the local machine is on their external computer and connecting to the Access backend on the company's network?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:35
Joined
Feb 28, 2001
Messages
27,001
Djblois - as you can see, there is some "wiggle room" in your description of how you are set up, and therein lie the questions that we have asked. It could be that you are set up quite well, but the description was ambiguous. Don't take it as criticism of your work; take it as interest in understanding what you did so we can better advise you.
 

Users who are viewing this thread

Top Bottom