Guide to Improving the speed of your databases

When building queries within a database with a multiple number of linked tables and a variety of criteria, it can take some time to process, especially on a network.  The more tables you have  linked causes more calls to the data tables and more network traffic if you are sharing data.  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.  
Find more help for your databases at:
www.biomationsystems.com