best practices for multi-user environment & large data amounts
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-applica
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
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-applica
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
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
ASKER
What are a server's OPLOCKS?
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.
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.
ASKER
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.
" - ?
" - ?
<<
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.
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.
Just my point of view.