File Server vs. Client Server

[AccessUser22]

I'm in the process of designing several databases. Currently I have two of the databases created in Access 2000, and plans for a third application are underway. The first application was small, only had two users. This second application has about 15 users and for this one I am currently in the process of splitting the database into a front end/ back end setup. However, this seems to run extremly slow even when I take into account some of the tips I've read from other users (such as turning off the name autocorrect, etc.).

My question is, would the network be entirely to blame for the slowup? Currently it's running at 10Mbps. Would upgrading the network offer a marked improvement in speed of the application. Or, is the design of the application the flaw. Should I really be running this with SQL Server as the backend. What kind of performance improvement could I expect with that? I've read many different posts and been paging through several books on the topic and any professional input I could receive would be greatly appreciated. Thanks.

 

[Norris68]

Upgrading your network would speed it up dramatically. Access is quite a network-intensive application. One point to note is that Microsoft do not recommend using Access for more than about 5 concurrent users. A lot of people are happily running databases with a lot more than that (some not so happily), but the official line is that you should look towards SQL Server - not cheap though.

 

[CaptainD]

for 15 users you should look into MSDE

http://www.microsoft.com/sql/msde/productinfo/overview.asp

 

[lameid]

I aggree, upgrading the network is the first place to start... Hopefully all your systems already have 100mbs NICS in them and a few cheap hubs will get you a huge performance increase. A smart switch may help too. I'd recommend upgrading the network first because it is the easiest thing to do (unless you have to pull new cable <cringe>) and will benefit more than just Access. Using SQL server as a backend would also help by reducing network traffic IF YOU WRITE YOUR APPLICATION TO USE IT PROPERLY. If you do not know how to backend to SQL server properly for perfomance issues, don't touch until you do. And as far a cost goes, MSDE is viable for up to 5 concurrent users and will help with the network traffic.

 

[AccessUser22]

Thanks for the input thus far.

Question - what's the difference between MSDE and using the upsizing wizard to turn my application into an ADP file? Would both achieve the same result or is one easier than the other?

Thanks again.

 

[CaptainD]

FYI: there are different versions of MSDE, you may have to use an older version to use with Access 2000 to stay complient with the license agreement

 

[lameid]

There isn't really any difference... Both the full version of SQL Server and MSDE can be used as a backend... Thus far I really have only worked seriously with Access 97 so I can't be much more help.

 

[CaptainD]

MSDE can handle more then 5 users without any problems. (Max recommended for MS Access)and handles data transactions much better. There is a lot of information out there. There use to be only two options with Microsoft, MS Access (thus Jet) or SQL Server (which was to expensive for most small businesses) so they created a limited version of SQL Server (MSDE) to fill the gap.

http://www.microsoft.com/presspass/features/1998/10-21msde.asp

 

[fullkeel]

Personally I dont think you can make a broad assuption that you need to move from Access because you have more than 15 users. Yes, lets face it, anytime you upgrade your network, for the most part anyway, you are going to improve the throughput of your database. 10mbps to 100mbps is a factor of 10 so you will no doubt see an improvement equal to somewhere in between 1 and 10. But before I would change programs I would test your front end in realtime with your possible 25 concurrent users.

 

[Hap007]

Whoa,

Hold on there. If you have the knowledge and money, SQL server would be your best bet, but isn't the more expensive product also the best, of any thing.

Here are a couple of points of interest:
1) I have many companies running an Access 97 accounting application with well over 25 users, and if the data structure is defined properly, it runs fine.
2) Likewise, I have the same software running under Access 2000, with similar number of users and this also runs fine.

So, if properly designed and programmed, 25 or more users will work fine on an access backend if properly designed.

Now, if you place SQL as a backend, and do not design the backend correctly, it will run better, but will still be slow.

The point, do not always look at the tool as the problem. Perhaps the trouble with a poorly performing database is hardware, memory, disk speed, net work speed and design rather than the access product itself. SQL Is better, but there is nothing wrong with access and many users. I again repeat, I have had a 550 meg backend database with 25 users updating and 25 more users reporting on the data, all at once, and it performed great.

Just Wanted to Share my experience,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[DaemonSaDiablo]

I have applications written using full SQL server (130 users), MSDE (50 users) and Access 2000 (Largest is 70 but displays reports more than anything, 40 largest real time update/refreshing).

If you have the time and knowledge, MSDE is usually the way to go when upsizing your Access database on the cheap.

While the network would be partially responsible always, make sure your relationships are well defined and you are not accessing the database without having too - a couple of months streamlining some of mine gave me almost 5 times the speed in the past. Depending on how critical the need is, spend the time in development in what you have that works, even if slowly, than take everything offline and having nothing.

 

[AccessUser22]

Interesting input. I now feel that the 15 users shouldn't be a huge problem for my application after reading all of your ideas. Thanks a lot for the reassurance. And again, more knowledge is always appreciated.

 

[Hap007]

To All who would like to read a little more about selecting the correct database and some of the pros/cons, please check out this link:

http://techrepublic.com.com/5100-6329-5056993.html

Enjoy,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer

 

[psarros]

I think you must have a design problem. I have a database with about 10 users, connected at 10mips and it works fine.

Before doinf any costly upgrades, make sure your databse is running correctly. Make sure you split the data and forms...

don't see why the database should be too slow at 10mips

 

[grnzbra]

We have an access system linked to tables in a sql server, with all the report querying being done on the Access(client)side, so I believe it is behaving the same as a system with access at both ends. Anyway, the boss had people complaining about one report the was driven by a query that used 10 tables and took 15 minutes to run. The query had 4 fields with fixed criteria and 1 field with a criteria that was determined at run time. Also, the query had a lot of calculated fields (Multi level IIf functions). I created a sql server view that contained the 4 fields with the fixed criteria (ie SaleStatus = 'Closed') and all the rest of the fields that the original query used or were needed for the calculations.

At the Access end, I linked to the view just like linking to a SQL server table, and replaced the 10 tables in the query with the view. Some of the table references in the query had to be tweeked to refer to the correct field in the view. When we ran the report, it ran in about 30 seconds.

 

[Hap007]

Dear grnzbra,

And that proves my point, (Earlier stated in this thread).

Designed poorly(Access backend), it will run bad.
Spend $5000 for SQL, and just upsize, it will run better.
Learn how to program SQL, and it will run great.

So,
Programmed well, access will run ok.
Toss in SQL, that is better, but if you can do in access and it runs ok, well save $5000.
If you use SQL, and then take the time to learn SQL Server, then performance will be much improved. But, when you do this step (Learn SQL), you could have made the Access version run better in the first place.

[red] Nothing beats experience and knowledge! [black]

Oh well, good computing,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

-
Specializing in Access based Add-on Solutions for the Developer