Solved

best practices for multi-user environment & large data amounts

Posted on 2008-10-28
10
521 Views
Last Modified: 2012-05-05
A year ago I started working in a small company that develops and sells an MS-Access 2000 application. This product deals with accounting and bookkeeping, inventory management, CRM, sales analysis, balancing, expenses, and so on and on and on. It took 7 years to develop. The developers are gone and I'm in charge for improvements, bug fixing and developing new ideas.
Most of our Clients are small retail shops or self-employed people. But there are some that are small or large companies as well. That's where the problem begins.

Every new year our clients archive their data and start a new database. Despite this fact, One of them has a huge database of 200 Mb, with 290 tables  some of them contain 150000 records within a single year.

The problem is: slowness!!

We've done everything we know that might improve their application-performance: our app is well split into backend db & a few frontend apps, the design is professional, the right fields are indexed, where we need too complicated queries for reports we use local/temp tables, each user has his own copy of the application & but still it is slow!

This is a large application: for example, the business-module consists of 666 forms, 300 reports, 509 queries and 11 modules.

Some people said to me: SQL server is the solution! Is it really?
I know nearly nothing about SQL server. In the past 9 years I worked entirely with Jet access applications.

Is it realistic to convert that complicated 7-year-development-application to SQL server.
And in the end  is it really going to work faster?

If that's so  what's best? To link to SQL server tables or to create an ADP?
What are the best books in the world that teach SQL server for access developers?

If SQL server is not such a great solution, what are the best practices or even the hidden secrets of making Jet Access apps work fast in a multiuser-environment & huge amounts of data?

(some more details: OS is win XP sp2. We use access 2000 because the app uses conditional-formatting in forms extensively, and Access 2003 has a bug with cond. formatting, as you surely know. Access 2007 hangs when we try opening/converting our staff to ACC2007, for a still unknown reason. So here we are, still with ACC2000. Our clients use it with the Access-Runtime. Number of users: 15.Network: simple file sharing network, and 2 users are sitted far away, connecting to their own copies of the app through a terminal-server).

Thank you in advance for your attention to my question,
Noam
0
Comment
Question by:NNOAM1
10 Comments
 
LVL 3

Expert Comment

by:Blackninja2007
ID: 22820876
My recommendation is that you have probably pushed Access about as far as it can go, It would be well worth you looking to move up to SQL server (especially is the DB's are going to grow bigger over coming years). The problem is in the way access works (not being a full client server type application). If you have the budget it would probably pay you to buy in some consultancy to assist in the conversion. But when it comes to medium/large scale multi-user Access is really not the right engine.

Just my point of view.
0
 
LVL 17

Expert Comment

by:Natchiket
ID: 22821220
I'd say the 'secret' to making Access run fast in a multi user environment is to be as frugal as possible with the amount of data that has to be transferred at any point.  Using filtered or unbound forms, minimising the use of combo boxes/list boxes that require indiscriminate population on form load.  But there are many other factors which can affect performance such as the network infrastructure, right down to the network cards the PCs use.

I'd suggest that you at least try SQL server.  You can get a free version e.g. SQL Server 2005 Express and providing the data volume doesn't exceed 4GB you should be ok.  I guess an ADP would be the 'ideal' way to go, but that might be onerous to redevelop.  A simpler first cut would be link directly to the SQL server tables, you can do this using DSN-less connections to minimise the administrative overhead.

I too have a client with an Access 2000 set-up.  For various reasons we're investigating a migrate to MSSQL. After having installed MSSQL 2005 express on their server, I migrated the back end data using the Microsoft SQL Server Migration Assistant tool (also free).   A few tweaks to the table connection procedure to enable DSN-less connections and it was up and running, and believe me, the general performance improvement was meteoric.  One or two of the queries required a re-write.

I'm sure the experts here will give you loads of tips on how to make improvements and the list of things you can do to improve Access performance is pretty long.  But for my money, in your situation, if you can spend a day, or maybe less and just 'test the concept'  you'll get an idea of the sort of perfomance gain you can expect for zero outlay.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 22821236
SQL Server might help if the bottleneck is getting data.
But with size of your front-end app I'm not initially inclined to see the backend as the problem.
It would probably be worth testing the app with almost empty data tables to see if it runs noticeably faster.

Bear in mind that the performance benefit of SQL server applications arises as much from client-server design as it does from the power of the database engine. Forms are often unbound, or use recordsources which return individual records,  not tables.  Stored procedures are used instead of local procesiing in queries and vba procedure.

Given the size of your app,  though, the only short term possibility would be to change the backend and you could see how far you can get with the upsizing wizard as a first step.  But the prospect of testing such an application against a new backend would be mind-boggling.

I have to say that rejecting an upgrade to a later version of Access because of CF problems but being prepared to consider moving to an ADP sounds a bit out-of-balance to me. (Not that I think that a move to a later version would give you any performance benefit).
But with MS's current attitude towards ADPs i would have said it is not a good idea, anyway.

If the backend appears to be the issue, I think my own approach would be to see if there was a small set of key tables that could be migrated to sql server express using linked tables and see if the app (a) still works at all and (b) if there is any signifcant performance gain.


0
 
LVL 57
ID: 22821249

    Your question is pretty wide open, but I'll mention a couple of quick things you can do:

1. Always make sure your apps are compiled.

2. Check you indexing again.  Keep in mind that indexing is a double edged sword; too little can be just as bad as too much.

  The more indexes you add, the faster searching goes, but then the longer adds, updates, and delete operations take and can lead to concurrency issues.

3. Of course it goes without saying that you need to make sure that the BE database is compacted on a regular basis.

4. Make sure none of the DB's (FE or BE) are being virused scanned.

5. The BE should be on a server that has OPLOCKS turned off.

6.  Make sure you have a connection to the BE from the FE for the life of the app.  This avoids the BE being repeatedly opened and closed.

  Long term, there are some things you could do in VBA to speed up code.  Also look at some of your data search operations.  Any place where you can open a table as a table and seek on an index (which is extremely fast)?

  As far as moving to SQL server (or another BE RDBMS other then JET), yes it's something you should move to.  SQL Server and it's likes are far more robust then JET.  They also offer server side processing.

  Without too much trouble, you could move data to SQL Server, replace your current table links with ODBC connections, and be off and running.  Most will see a performance improvement, but there are those that will not without doing some work.

  Where the real performance gains come in is with the server side processing.  The first step is to change queries where possible to pass-through queries.  This is what it sounds like; you write SQL that gets passed through to the server and the data get's passed back or the action carried out server side.  The SQL is the servers SQL dialect, not Access's, so there are some differences.  

  Beyond that is stored procedures.  This is where you write a procedure server side and then simpley call it from Access.  

HTH,
JimD.

 
0
 
LVL 57
ID: 22821257

  I should have added that out of the list of things that you can do quickly, #6 is the most critical and will yield the biggest performance gain depending on the app itself and how it's used.

JimD.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:NNOAM1
ID: 22824226
Well people, thank you so much for your comments. I'm beginning to feel I know what to do next. I guess the best thing is to try it with SQL server and see what happens. Thank you very much.
0
 

Author Comment

by:NNOAM1
ID: 22824269
What are a server's OPLOCKS?
0
 
LVL 57
ID: 22824368
OPLOCKS or opportunistic locking, is a client side caching system built into Windows.  Since JET has it's own caching already, OPLOCKs on a server simply get in the way and you may wish to turn them off.  You can read about them here:

How to keep a Jet 4.0 database in top working condition in Access 2000
http://support.microsoft.com/kb/300216

  along with other things that might be useful to you.

JimD.
0
 

Author Comment

by:NNOAM1
ID: 22876205
Well, my boss isn't going to consider using SQL-server for the time being. So we I have to provide some kind of improvement for our Acc2000 app. what does it mean "Make sure you have a connection to the BE from the FE for the life of the app.  This avoids the BE being repeatedly opened and closed.
" - ?
0
 
LVL 57
ID: 22885146
<<
Is this what you were looking for?Yes No
NNOAM1:
Well, my boss isn't going to consider using SQL-server for the time being. So we I have to provide some kind of improvement for our Acc2000 app. what does it mean "Make sure you have a connection to the BE from the FE for the life of the app.  This avoids the BE being repeatedly opened and closed.
" - ?>>

  There's quite a bit of overhead that Acces/JET goes through to login a user to a database.  If your FE was developed in such a way or used that it closes all it references to a BE, then the user is logged out of the BE.

  To avoid this, in your startup code for the app, open a table on a global variable.  Then no matter what happens within the app, the user is always connected to the BE and the logout/login does not occur.   Close the connection before you quit the app.

  Depending on the app, this can speed things up quite a bit.

JimD.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now