Production Database Performance and Design (1 Viewer)

[Bobby1st]

Hello All,
I have a production database on our G: drive which features employees various reports up to the creation of a scorecard. Since most of my users are not computer/application/database savy, to protect my database from deletion, I keep important tables in separate database. The Data (stats) table, rating table and password table is on one database I will call Scorecard_Data. The Employee tables and other tables is on another database called let's Scorecard_Table. The Main database contains all the queries, forms, reports, module (scripts). The Scorecard_Data and Scorecard_Table tables are link to the Main database. The other two databases are secured in another location folder. This is how I design the relationships of various tables.

The users of this Production Scorecard database are located in two different states. We are on the same domain, their main folder is located on another server but could be access using the Windows Explorer (Drive G). We belong to the same G drive but separated from the second level.

When I run to extract various reports, it will takes around 30 sec to a minute to generate a report, while the other users from the other state will take them 3 - 4 minutes, which they are complaining. I am lost for answer how to fix it, I tried different set ups.

One report option is to generate all direct reports for a team manager. Once the report is up, when you go to the next employee, it takes almost the same time generating the first. Is this setup causing the slowness?

I will try any suggestion to improve the speed. There is little query stacking, one or two sub-reports.

Additional information. The main database contain one for every month of the year, 12 of each kind. Example: 1 main_data_table; 12 tblEmployee, 12tblManager, 12tblAdmin (passwords); 12tblPositionAll, 12tblPosition, 12TMP_Rating, 12tblNonProd, and few more (All Links)

Form: two with 12 forms each
Report: has 12 rptPreliminarySumm EE, rptCalcsheet EE, rptFinal EE and subProdTime EE. The same number for Team Managers identifies as TM.

Because the tblPositionALL table (function standard) changes from month to month (not all at the same time) and Employee changes for hired/terminations or move to another Team manager, when you regenerate a past month reports you get the same result. I read in one thread that there's a limited to the number of tables, forms, reports, etc that can be had in Access, don't know if it's true.

This is long already, but I am trying my hardest to give a better picture that might result in a good advise.

Thanks you.

 

[Moniker]

You aren't running up against limits for forms, queries, and such. Instead, you're running up against bandwidth limits and what Access was meant to handle. While Access is fine in a BE/FE situation, it's not the best server, so to speak.

The cleanest answer is to use something designed to be a server, like SQL Server, especially when you're trying to run across a few different states. That will allow very easy integration with Access (both are MS products).

A cheaper, but more complex answer, is to develop a routine where each user downloads updates from the server and updates their own local versions of it. This is essentially DB synchronization, which can be automated to a certain degree. If your users can alter the data in the tables (and not just run reports), you'll want to investigate the synchronization route. If, on the other hand, they are just using the data to produce various reports and there's no updating, then making a quick routine to get the latest version of the tables off of your server DB will take about five minutes when the DB first starts (presumably in the morning), and after that it will run as if it's local, like it does for you.

 

[Bobby1st]

Hello Moniker

Thank you for your analysis. I can't introduce SQL Server application on our server and I think there's a learning curve here. My first setup, just picture three databases I will call them: Main, Data, Tables. All three database is on my side of the G drive, different folder location and the Main is link to the two database. The Data and Tables db are updated month. The Main is running by sub routines. The Main is accessable by the other state and this is my mandate, one database for all performance report but because of speed issue,
I made a copy of Main_db on the other side, I will call Main2. Still link on Data and Tables db, that didn't help. So I created Data2 and Tables2 to partner with Main2. This helps because it's now local to them but maintainance it horrible and the project didn't meet the requirement of one combine database for uniformity.

The end users don't do much but extract employee performance summary; calc sheet; input evaluation and rating; and print the final scorecard. There are people on both side that take care of downloading data from different system; convert and analyze the stats and upload to the Main db.

You mentioned DB synchronization, I know the word but I don't know how it works or applies to this situation yet. I wonder if I touch on the synchronization in a crude way. You also mentioned running against bandwidth, I guess there's no other way I can meet the required one combined database in one location.

Can you comment on how I setup my database, which I split up to gain speed, probably you might have few tricks that can help?

 

[Moniker]

Look into replication, which is built-in to Access. With that, the Synchronization can be automated, as in your "Data" and "Tables" tables can be maintained locally by you, and you can then click on "Synchronize Now" to update the remote versions of those tables. Search both this forum and Google for the exact steps to take as it varies depending on specific situations. Again, your keywords here are replication and synchronization. (In Google, use "MS Access replication" and "MS Access synchronization" for your search.)

To help wrap your head around what it does, think of a PDA like a Palm Pilot or a Treo. You can "dock" those to a local computer and any changes made to the PDA get reflected in the local calendar/contact list, etc., and vice-versa. Access will do this as well with other, remote Access DBs.

 

[The_Doc_Man]

The biggest problem with a long-distance deployment is that you are only as fast as your slowest link. Your network admin can probably advise you of the best speed you can expect by doing PING operations from end-to-end inside your network. The admin might even be able to suggest explicit routing that avoids slow links. Heck, your admin might even thank you for pointing out the speed problem; you never know about that...

The best way to speed up "pure" Access databases is to shrink table sizes as much as possible. For example, if you have the choice of expressing something as a string or a number, pick the numeric format for storage and do any format conversions on reports only. Make every field as short as possible to get the job done. Be sure you have good indexes on any tables that will be queried (or joined) regularly.

Front End/Back End splits at least save you the fetch time for the specifications of queries, forms, reports, macros, etc. Allow your users to download the front end stuff once. You might have to use UNC pathing to establish your links to the Back End depending on the uniformity of drive mapping in your network. If everyone uses the S drive for sharing, you are ok, but if not... UNC paths are in your future. You know what they are, you just might not know them by that name. For instance, the S:\foo\bar\ path might REALLY be \\oh\my\foo\bar\ - in which the file-server name is oh and the "hidden" mount point is folder "my".

 

[Wiz47]

One other thing to consider (as if you didn't have enough to think about already) is putting the main files on the main server path and not a sub path/folder. The deeper you put the files, the more time it will take to load them as each level will require the server's security system to evaluate the request. The deeper the file, the more requests - the longer it takes.

Everything you can do to speed up operation, like having all forms, queries, reports, and redundant tables on the fe, and keeping the server path shallow will help.

 

[The_Doc_Man]

Wiz - good point. The larger the organization, the more Access Control Entries (ACEs) will be likely for each folder in a multi-folder path. Heck, for the site where I work, which serves the (nation-wide) U.S. Navy & Reserves, we might have several dozen ACEs per folder. But there are some, so I've heard, that might approach over 50. Insanity at its best (or worst, depending on how you look at it.) If you have enough ACEs you need an extension file header entry just to hold them all. A performance killer if ever there was one.

 

[Wiz47]

... A performance killer if ever there was one.

Sometimes it's the little things that we don't think about that hurt us the most.

 

[Bobby1st]

Thanks to you ALL,
Moniker - I will investigate replication and synchronization and see from there. I like the way you describe about the PDA but I have to learn how to do it.

Doc Man - Actually there's two Front End, all link to the BE. The first FE is where the employee upload the data downloaded from the system through forms and more on there subfolder. The second FE db is where the scorecards are pulled, located on the team managers subfolder. The table structures and formatting are to its minimum size. All database are in the same Drive G.

Wiz - you're the closes that I can take right now and I might know about. My problem is our system right now is controlled (permission needed) down to the 4th level, therefore, the Main db start on the 5th folder level. The Data and table, two more level down, that's why they might be slow. What I have noticed is that a user should have access to the folder where the BE db is located or else the queries is restricted. Here a typical path G:\Location\Business name\SpecificBusiness\LocalFolder\ Open from this level.
To move the BE db up to the 5th level might compromise it or get deleted. I have three leads to pursue. I am sure to be back. Thank you.