|
 |
Unfortunately this contradicts the goal of having a "normalized" data structure and sharing the data in your tables with links. A data structure that is "normalized" in simple terms, means that you only have any data in your table set one time and that you share that data to any form or report that needs the data through links. This is the most efficient structure for your database and your users. You don't want your users trying to keep data in separate forms or tables synchronized. The goal for your database is to enter data once and share it everywhere. That also means that your users are updating the data and it updates everywhere. This is important to ensure that at the time any reporting is done that the data is up to date for all users.
Now we discovered that having a database "normalized" was causing a huge amount of network traffic when generating daily reports. This activity was slowing down the application and every other user on the network as well. One particular report was spiking the network server to the max for over fifteen minutes while it compiled the data. The data needed for the report came from twelve different tables linked together. Upon investigation we found that the time to create the report was largely due to data traffic. It appeared that the multiple links were causing the database to query the data over and over causing the traffic.
Our solution to reduce the traffic and the time, was to create a temporary table with reduced data and subsequently query from that table for any reporting needed. This technique works well with Access, MySQL and SQL Server databases as well.
For an example, you may be generating a productivity report where as many as 100,000 plus records for the year are kept of all tasks completed. A query linked to the tables containing the employee, tasks, department and other data is needed to generate the report. Your goal is to report on productivity data for the month of March for all employees by department. First build the query to append to a temporary table (reducing the number of tables from many to one) with all required tables and fields, selecting only the date range for the month of March. This reduces the data and reduces the number of transactions on the network at the same time. Subsequently query from this temporary table to build reports by employee with much greater speed and ease.
It has been our experience that large report queries (100,000 records plus) will process the data you need many times faster using this approach. We have seen report speeds improve as much as 15X using MS Access and 20X using SQL Server over the internet. That is something your users will certainly be happy about.
What could BioMation Systems do for you?
BioMation Systems, Inc is an Atlanta, Georgia based consulting company that develops custom database solutions that increase the efficiency of businesses around the world. BioMation's range of services can be found at www.biomationsystems.com
Contact: Jon Watson
jonw@biomationsystems.com
You can find help for Access at
http://www.accessdatabasehelp.com
http://www.accesshelpebook.com
http://www.biomationsystems.com/AccessTips.htm
Category: SoftwareMore Speed for Your Databases was written by:Author:Jon WatsonAdded: Sat, 20 Oct 2007 07:40:24 -0400 This Article Has Been Read 241 times View all Jon Watson's articlesAbout the Author: Jon Watson is the founder of Biomation Systems, Inc. With 26 years experience helping Fortune 500 companies with process improvement he formed BioMation to bring the same expertise to smaller companies that need the same improvements at an affordable price. |
More Articles About Software
Not What You Were Looking For?
 Moxie Drive Expressions
 Submit Articles
 Contact us
Add us to favorites
Hint - Hold "T" button down and right click to open Sphinn and Subbmitt in a new window. 
|