Revision Date: 2005-09-07
This article applies to SQCpack for Windows release 4.6 and above.
I recently spoke with an SQCpack customer managing a large deployment of SQCpack. Their original question was related to performance - the speed at which SQCpack was loading and accessing various databases. The conversation lead to this article which provides general guidance for deploying and managing SQCpack databases in a large multi-user network setting.
The situation
The customer has between 100 and 150 SQCpack databases. Most of these contain less than 15 groups. These databases are being stored on a shared network disk drive and being accessed by 8 to 10 computers throughout their facility. On most of the computers, SQCpack starts with 2 or 3 databases open. Some of the computers used by the quality department start up with 11 or 12 databases open - all at the same time. The customer expressed concern at the time it takes SQCpack to boot up and the time it takes to open certain groups for data entry and charting work.
I'll discuss each of these in more detail, but here are some of the options for improving performance in situations like this:
1) Reduce the total number of SQCpack databases.
2) Reduce the number of simultaneously open databases.
3) Archive older, less used, data sets.
4) Store data on different physical network disk drives.
5) Consider adding a network server for SQCpack databases.
6) Implement a system of regular database backups and compacts.
7) Assign ownership of SQCpack database administration.
8) Take advantage of the user security options of SQCpack.
Reduce the total number of SQCpack databases
Have you ever heard the saying that "less is more?" With each new SQCpack database you create the amount of complexity in your life increases. You have to think about backups, archiving, network storage locations, user access, etc. for each database. If you can do something with a single database that you were doing with two (or more) databases - you should consider using a single database. There are times when you absolutely need multiple databases and that is why SQCpack has this feature. However, if you can get by with one database you should do so. We do not recommend taking this to the extreme and putting everything into s single database. As a rule of thumb, once you get 50 or more groups into a single database you might want to consider using additional databases. Each group may have a name and a description to precisely identify the data contained within the group. SQCpack databases are really Microsoft Access databases with an .sqd file extension. An expensive operation (in terms of computer performance) is opening a database; the more databases you have, the more times SQCpack will have to do this open.
Reduce the number of simultaneously open databases
SQCpack allows you to open multiple databases at the same time. This allows you to easily switch between different databases. In reality, you can only do work like data entry or charting in a single database at any given time. If your SQCpack starts up and automatically opens say 5 different databases, but you spend most of your day using only one of these - you should consider not keeping the others open all the time. Instead, you can open them when you need them and then close them when you are finished. This will reduce network traffic and reduce the time it takes for your SQCpack to start up. An alternative is to use the command line options (see the on-line help on this topic) to setup icons on your desktop for different databases. For example, instead of having 3 databases opened each time you start SQCpack, put three different icons on your desktop - one for each database. Click the icon you need and SQCpack starts with only that specific database open.
Archive older, less used, data sets
Many of our customer track important quality measures across multiple years. If you have a large amount of historical data in an SQCpack database consider the possibility of archiving the older data into offline storage. One way to do this is to make a copy of the entire database and give it a meaningful name such as MyData2000to2003.sqd. Once you secure the copy of the database - open the original database. Open each group and delete all the records prior to the current year. Once this is finished, use the File/Utilities/Compact and Repair option. This will reclaim the database space used by all the records you've just deleted. This can speed up the time it takes to open the group for data entry and the time it takes to render certain charts.
Store data on different physical network disk drives
Often, the quality department will be allocated a network disk drive for storing their shared data. Let's say this is mapped as drive Q: for SQCpack users. If multiple workstations all use drive Q: for SQCpack databases and if drive Q: is the same physical device for each user this can create a bottleneck. Computers get faster all the time but if you put enough people in line requesting services - even the fastest computers will slow down. Talk to your network infrastructure people. Would it be possible to put some of your SQCpack databases on one physical disk drive and some on a different disk drive? If so, this can reduce the performance bottleneck of multiple workstations lining up for service from a single disk drive.
Consider adding a network server for SQCpack databases
Often a server pc is deployed to solve a single problem. Over time, additional responsibilities are heaped on the server. You might start out with a sever dedicated to document control. Later, a portion of this servers disk is allocated to the quality department. Along the way others may use this for off line email storage. Eventually, the server becomes overworked and a bottleneck is born. Consider the price of a server PC in relation to the importance of your quality data. Generally, the cost of a server is easily justified and this can improve performance dramatically.
Implement a system of regular database backups and compacts
SQCpack has a nifty feature for automatically backing up a database upon exit. Unfortunately, if you are operating around the clock and sharing databases among multiple users/workstations this feature must be turned off. It is primarily designed for single user/local database situations. The problem is that a reliable backup is difficult to make when another user has the database open. Interestingly - the other user may not even be using the database - they just have it open on their main screen. (see previous topic on this) You need to think about and implement a plan for regular backups of your SQCpack databases. If you run 24 hours a day - you need to select a time where everyone agrees to exit from SQCpack. At this time, a batch file needs to run that makes a backup copy of each SQCpack database on some different disk drive or media. You must be religious about this if your quality data is critical. Viruses, unexpected power failures, malicious users - all of these can be thwarted by a good system of backups. It is always a dreadful phone call when we have to ask a caller "do you have a backup copy of the database?" and they answer "no." It is also a good idea to use the File/Utilities/Compact and Repair database option on a regular basis. If you typically add and delete groups and charts from your database over time the database file will become disorganized and take up more space than needed. Running the Compact and Repair utility solves this problem.
Assign ownership of SQCpack database administration
Someone should be designated as the person responsible for SQCpack databases. This will be a fairly easy job. The idea is to have someone thinking about the issues discussed in this article. Where should we store the databases? How should we organize groups within the databases? What is the daily and/or weekly backup procedure? How often do we archive old data? How should we implement the optional user security features within SQCpack? The goal is to have a central source and a consistent answer to these types of questions.
Take advantage of the user security options of SQCpack
SQCpack has several options for managing users. These can be used to give each user a set of privileges within the software to match their work responsibilities. For example, I mentioned earlier that the auto-backup feature should be disabled in a multi-user network deployment. What if your users accidentally turn this feature back on after you disable it? This can affect performance for all users. With user security and privileges you can prevent this type of change. Please read the on-line help and user guide topics related to users and privileges for more information.
