Access Tunning in Microsoft Office
Microsoft access software tuning
Microsoft access is a powerful software application that can assist users in creating and managing a desktop database. It as many features to help users construct and view their information. The most common reason for a slow Microsoft access database is a heavy database application workload. Inorder to figure out what is causing the database slow down, it’s necessary to analyze the queries causing stress on the system resources. Microsoft access software tuning is nothing more than adjusting application codes, configurations and system setting improvement for better performance. When ever the term access software tuning is used, you should immediately think of performance enhancement. Access as a built in tuning wizard that can be instructed to capture trace relationship, recommend appropriate columns for queries and estimate performance increase after every adjustment. But today I will be employing a manual approach, using three tuning steps sorted in order of greatest effect on a database performance. These three performance tuning steps are: database design, data design and database integrity.
Database design
Database design is the process of constructing a model representing data structures and their relationships. Database design plays a major part in gaining good stability and reliability of your data. Although there many ways to design a database structure, doing a paper design initially has several advantages: it serves as blue print for discussion, makes system more reliable, avoid potential data modification problems and helps in estimating costs and size. A proper database design is like a business model that illustrate the way data is stored and how data is related. The design process helps determine what information needs to be stored and how it will be retrieved. Hence the more carefully you plan your design, the better the physical database will meet users’ needs. The benefits implementing good database design processes in the beginning, is that it creates better data integrity, foster a more efficient development cycle and a clearer vision of the entities being represented in your database.
Data design
A wells design data structure is good data tuning. If the data design structure is not fundamentally sound, a company can spend a great deal of time trying to compensate for bad data design. A table should only store data for a single type of entity, because storing too much data in a table will greatly reduce efficient and reliable management of the data in the table. This scenario is an example of deficient data design: In the pubs database in Microsoft access, the titles and publishing data is stored on two different tables. Although it is not uncommon to have columns that contain data for both the book and the publisher in the titles table, this bad data structure layout can lead to several challenges. The publishers information must be added and stored redundantly for each book published by that particular publisher. This results in the lost of extra storage space in the database. Now if the publisher change address, changes has to be made manually for each book. And if the last book for a publisher is removed from the title table, then the information for that publisher is lost. In a good data design scenario the pubs database, has the data for books and publisher stored in the titles and publishers tables respectively. The data about the publisher only has to be entered in once and then linked to each book. Hence, if the publisher information is changed, it only has to be change in one place and the publisher information will be updated even if the publisher has no books in the database. This last scenario I just describe is a text book example for good data design.
Database integrity
Data integrity enforcement ensure data quality is establish in a database. Microsoft Access institute rules to establish integrity and enforce integrity. Integrity can be establish from a one-to-one or one-to-many relationships during the initial of the relationship or it can be established by modifying existing tables to establish this new relationships. Access prohibits the deletion of parent records or changing of primary key values when relationship is already establish. If no referential integrity is establish, then its said to be an indeterminate relationship that exist between the tables. For example, if a table has an dept_id column that stores the department number for the employee, that database should only allow values that are valid for the department numbers in the company. Data integrity can either be one of the four categories:
• Referential integrity is use to maintain already defined relationships between tables when records are entered or deleted. In Microsoft access referential integrity ensures that key values remain consistent throughout the tables, therefore referential integrity make sure that there is no reference to nonexistent values. If key values changes, all reference related to it changes consistently throughout the database.
• Domain integrity, validates entries to any given column. Domain integrity can be enforce by restricting the data types, format check constraints and rules, or arrange values through foreign key constraints and default definitions.
• Entity integrity treats a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier columns or the primary key of a table through indexes, identity properties, unique constraint and primary key constraints.
• User-Defined Integrity allow users to establish specific business rules that do not fall into one of four previous integrity categories.
Conclusion:
Microsoft access is a powerful software application that can assist users in creating and managing a desktop database. It as many features to help users construct and view their information. We establish that the most common reason for a slow Microsoft access database is due to heavy database application workload. Microsoft access software tuning is nothing more than adjusting application codes, configurations and system settings improvement for better performance. When ever the term access software tuning is used, you should immediately think of performance enhancement. Access does come with a built in tuning wizard that can be instructed to capture trace relationship, recommend appropriate columns for queries and estimate performance increase after the adjustment is complete. I have explained the manual approach, using three tuning steps sorted in order of greatest effect on a database performance. These three performance tuning steps are: database design, data design and database integrity. Microsoft access 2000 falls under the desktop category of database. Access is an ideal database application for individuals and work groups managing megabytes of data. Microsoft access uses file-server architecture, rather than client-server architecture like SQL.
Reference:
Getz, K, Litwin, P & Baron, A (2004) Access Cookbook. 2nd ed. Sebastopol, CA: O’Reilly Media
(Getz, Litwin & Baron 2004)
Betz, J (2005) Introduction to database design. Retrieved July 19, 2005 from http://www.schools.ash.org.au/olshc/infotech/dbdesign.htm
(Betz, 2005)
Gulutzan, P & Pelzer, T (2003) SQL Performance Tuning. 2nd ed. San Francisco, CA: Addison-Wesley
(Gulutzan, 2003)
Vandenberg, G (2005) Microsoft access. Retrieved July 19, 2005 from http://www.developersdex.com
(Vandenberg, 2005)
Data Integrity (2005) Retrieved July 19, 2005 from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_3bsp.asp
(Data Integrity, 2005)
Viescas, J (2004) Microsoft Office Access 2003 Inside Out. 1st ed. Redmond, WA: Microsoft Press
(Viescas, 2004)
Solution, A (2005) Microsoft Access Database Solution. Retrieved July 19, 2005 from http://www.microsoft-accesssolutions.co.uk/data-modeling-database.htm
(Solution, 2005)
Dam, S (2003) SQL Server Query Performance Tuning Distilled. 1st ed. Birmingham, UK: Curlingstone Publishing Ltd
(Dam, 2003)
Tow, D (2004) SQL Tuning. 1st ed. North Sebastopol, CA: O’Reilly Media
(Tow, 2004)