Will upsizing back-end to SQL Server speed performance?

[RSGB]

Hi

I have a front end/back end Access 97 database with c70 users (up to 15 concurrent ATM) which is working fine for users on our site.

However we have now got some users at another site using it too and it's very slow for them such that it's becoming a barrier to data entry.

Access times to the server where the back-end lies in Windows Explorer don't seem too bad so I'm thinking it's not so much the speed of the network connection but a bandwidth issue for their connection.

As I understand it, if I upsize the back-end to SQL Server then the network traffic will be much reduced and therefore, if my diagnosis is correct, their performance should be nearly as good as it is on our site.

Does anyone have any views as to wheher this would be the case?

Cheers

RSGB

 

[MontyBurns]

RSGB,

Access Client-Server apps don't work very well over a slow connection as you've discovered.

If you have an Intranet, i'd really look into designing the app in SQL Server and ASP if I were you. This has many benefits, including ease of access, licensing costs, ease of upgrade to the application as it's all held centrally etc.

Upgrading to SQL Server for the BE and keeping your Access FE's will result in a performance improvement, but not drastically I would think.

There are techniques you can use to improve this in your code, but it's a big area in which i'm not all that experienced in.

HTH,
Burns

 

[Ed2020]

There are no hard and fast answers to this.

Jet favours networks with good bandwidth but do not require particularly fast servers as data is pulled back to the user's machine and queries are run there.

SQL server backends do not require such high bandwidth networks but do require reasonable servers as queries are run at the server and then only the results are sent back to the user's machine.

Even on a low bandwidth network you may end up with a slower system with an SQL Server backend if you have crappy servers.

The only way you can get a firm answer is to try it and see.

Ed Metcalfe.

 

[RSGB]

Hi

Thanks for your prompt & helpful replies.

Sounds like it's worth trying upsizing the back-end to SQL Server (it would be easier to get a good enough server than improve the bandwidth between the sites!) and giving it a go. If it works then it would be a quicker fix.

Regarding taking an ASP route I'd rather avoid that if possible as it's a more long-term process.

I've tried to ensure the queries are as quick as they can be though I'm not an expert at that but I will have another look at the ones they will run most frequently.

Thanx

RSGB

 

[WildHare]

Just to play devil's advocate here, I want to point out that the upsizing, while offering some 'improvements', comes at a cost like everything else. Depending on how much the talent and hardware costs, it may be more cost-effective to let lower-paid employees wait a few seconds than to spend a fair amount of $$$ on HIGHer paid employees to work for 3 weeks to save clerks 30 seconds of wait time a day. The ROI on some projects make take a long time to justify.

I tried pointing this out to someone once, and the light finally dawned on them - they wanted to pay me about a full day's time (at about $50 an hour) to save a data entry clerk about 5 total minutes a day (shave off 10 second wait X 30 transactions a day). At the clerks' pay rate, the 5 minutes cost about 60 cents. The work would pay off in 800 days. He decided the 4+ year payoff wasn't worth it, and told the clerk to get used to it.

Just a thought....





Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[demoman]

We have a WAN with 4 locations, connected with T1 (18 data channels). We run 100 BaseT, but also have layer 3 switching. We have a non-integrated collection of applications using SQL Server, Access and Pervasive SQL (btrieve).

SQL Server supports document imaging, runs on 900 Mhz Xeon 'duelies' and is very robust accros the entire WAN.

Access apps also run on 'duelies'. One is an 866 Xeon, but most are P3 733's. Access has a serious performance drop.

There are acceptable levels of poor Access performance we accept. We run the other Access apps in 'Terminal Mode', or Terminal Services. Performance is very good! You may wish to explore this?

 

[jimmythegeek]

I am in the process of completing an Access/SQL Server app this week. The SQL Server box is in Seattle, WA, and the front end is in Richmond, VA. If the hardware is good, and the app is designed correctly (COMPLETELY UNBOUND, NO LINKED TABLES), the performance can be great.

I tested it today, and the speed for our Richmond users is great. Most forms open in less than one second.

This is using mostly stored procedures on SQL Server, and the rest pass through queries on the front end.

Don't however thing that if you upsize, and link the tables, that the performance will necessarily increase.

I am a STRONG believer in totally unbound apps. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?