[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 294
  • Last Modified:

Microsoft access too slow - update to what? Using Access FE/BE

Hi all,

We currently have a front end/back end Microsoft Access application. I have recently become IT amanager and have been looking into upgrading our system.

I am after some advice if possible...

The main back-end database is currently around 50 Meg, and is situated on our server, which runs on a 100Mn/s Ethernet network with switches.

The database has 52 tables, which i believe is too many for an access database?

Accessing the database using the front end, (which before i arrived was being run from the server as well via links from user desktops) is done from users PC's, with links to the tables in the backend.

The database is VERY VERY slow.

The main reason for looking into otherwise of providing this application to the clients.

1.   I am pretty sure that several of the tables, such as 'constants', 'Days' and certain codes are just being used for list boxes - is this bad practice - wouldn't it be better just listing them in the required field?

2.     Surely linking things just to display data is going to make obtaining information from queries that much faster?

3.     I am looking at upgrading - which direction should i go? I was thinking of developing a web-based application, with client side files and access server database   would that be faster than using the access forms to look at/change data?

4.   I was thinking of using ASP.NET and VB.NET as an application base. would this be faster than other options such as PHP?

5.    If we upgrade to a web app, would MS Access be a slow database to use - or should i go for MySQL (we can't afford SQL server) for a speed increase?

look forward to hearing your respnses,

thanks,

Will
0
willbdman
Asked:
willbdman
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
shanesuebsahakarnCommented:
52? Our current application uses 140 tables. 50MB is not excessive by any means.

The most important thing to check is if your tables are correctly indexed, whether your queries are WELL written (i.e., using criteria where necessary, avoiding cartesian joins, avoiding VBA functions unless absolutely necessary) etc. etc. The other important factor is how many concurrent users you have.

From what you describe, your database may not be efficiently written or well optimised, but that is a snap judgement. I don't know what it does, but in a like-for-like situation, Access can OUTPERFORM many database systems - so long as it is used in an appropriate manner. Its main limitation comes in a heavy multiuser environment. Once you start exceeding 15-20 SIMULTANEOUS users, you should consider alternatives.
0
 
SidFishesCommented:
I'd agree with Shane...the size of your db is not an issue unless

1) your app is poorly written
2) your have alot of users

you can use the Optimizer wizard in Access...this will help somewhat...(and maybe alot) with performance increase due to indexing and will give some other options

there is a version of SQL server called MSDE and it performs very well in multi user situations...it is free...but throttled...that is as users hits to the db go up, performance slows down...That sounds bad but in practice, you can get very nice performance out of it for at least 25 users...especially if you use stroed procedures and proper indexing and such...this is NOT a simple fix as it requires a lot of rewrite but it's what I did about a year ago and have been very happy...

the other benefit of msde is that there is 100% compatibilit with SQL server...so when/if you do need to upgrade  it's very simple...


As for a Web app...be forewarned...if your organization has anything but the most basic reporting needs...a web app is a HUGE pain...
0
 
willbdmanAuthor Commented:
You have both made valid points - and what i feared.

We currently have only around 5 or 6 concurrent users.

I will look at just sorting out this mess of a database.

I don't really understand what or how joins work but we have a lot of queries that use them. Also a lot of update queries and link queries?

Do you think if i try and sort the database out it will solve a lot of our problems?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
shanesuebsahakarnCommented:
I would imagine so. Access is an application that is easy to use badly, and design poor systems in. Something that often looks like it works in a small test environment may not scale up properly with large amounts of data. An excellent example of this is the IN SQL clause - works great with a few hundred records but very poorly for a few thousand unless written with a criterion.

That's the difference between getting your app written by a professional Access db designer and someone who just "knows" Access, I'm afraid. Poor understanding of Access application design is probably responsible for most of the misconceptions about Access' performance.
0
 
Mighty_SillyCommented:
I have nothing to add about planning & designing, nor the number of tables in your apps.

My only comment is does your Access db get compacted often?  This normally shrinks down the file size.  I do that to both my FE & BE on a regular basis.  Since I distribute a new FE pretty frequently to my users, I don't really care if they compact their FE, I only care about MY Speed.  Compact the BE when NO ONE is connected to it.

-Silly-
ps:  Hey Shane!
0
 
shanesuebsahakarnCommented:
Hey Silly :-)
0
 
willbdmanAuthor Commented:
Thanks a lot for your responses.

Can joins affect performance?

What exactly do joins do? - Just 'join' two tables together that aren't related?

Yes i compact regularly
0
 
shanesuebsahakarnCommented:
Joins are (in general) the most efficient way to relate data in two tables together. There are other ways, but the order of evaluation means that joins are more efficient than using WHERE clauses - although their performance can be reduced in certain circumstances, such as using LIKE operators in them. The most important thing is to index any and all fields on which joins are performed - not doing so (and not indexing criteria fields) can lead to a *significant* reduction in speed - I've seen a query drop to a few (5-10) seconds from ten minutes after indexes were applied - admittedly, the original table was creating with a make-table query, had hundreds of thousands of records on it and had NO indexes at all!
0
 
SidFishesCommented:
if you use the

Tools>Analyze> Performance wizard...you will get a general idea if you have some big issues..it isn't perfect but it's pretty good.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now