Database optimization
Database management systems are pervasive in the modern world. The idea of a library of continuous, redundant, and highly distributed information has become the most important concepts in the repertoire of information technology. In fact, almost every man in the western world to interact with a database management system of some kind every day, often without using a personal computer at any time of day.
With millions of data transactions that occur each second, it comes as little surprise that the database optimization is a key research area for academic and research institutes and corporate development departments. From the perspective of a software company, most often a relational database serves as the core of the software application data-driven, and the lack of optimization of the database in such key areas can lead to significant costs for both client and company.
With millions of data transactions that occur each second, it comes as little surprise that the database optimization is a key research area.
The purpose of this article is to discuss basic database optimization using mathematical cost estimates for various types of questions, a review of performance and impact of various physical access structure on a particular query instance. The intended audience should be familiar with basic concepts of SQL and relational databases - usually an experienced database developer. Specific examples will be given in the context of MS SQL Server 2005, but they will illustrate the concept in general is able to sign up to support the SQL-relational DBMS (DatabaseManagement System). After reviewing the paper, the reader is expected to have a better understanding of how RDBMS formulate strategies for complex query execution and can use this knowledge to retrieve information at lower cost.
Using Database Optimization Database Index
DATABASE INDEX REVIEW
A database index structure of physical access to a database table that serve other than the name would suggest. These are files that inform the sorted database where records are physically located on disk. To get an idea of the index as consider a textbook. In order to find a particular section, readers can start reading the book and keep reading until he found what he sought, or, alternatively, he may consult the table of contents and go directly to the desired part. A lot of functions such as database indexes index text books do not.
Adding appropriate indexes for large tables is the most important database optimization, as we shall see when we discuss some examples of cost estimates. Creating a single index for a large table, there is no existing index can reduce the execution time of a query with an order of magnitude. For example, consider the following scenario. Say we have a database table called EMPLOYEES with 100,000 records. Assume that we want to do a simple query on the table and no index on the table:
SELECT FirstName, LastName FROM EMPLOYEES WHERE = EmpID 12 345;
In order to search employee records with the appropriate EmpID, the database could potentially have to scan through all records of 100,000 employees to return the results benar.Jenis scan is called a full table scan.
Fortunately, when I surfai from most database developers can create indexes on columns EmpID to prevent the scan of what has happened. Additionally, if this field has a UNIQUE constraint, internal DBMS will compile the index as a hash table, with each employee hashing ID to a physical disk address record desired. Scanning was thus become totally unnecessary, and location of records is done in constant time. After adding a database developer of this index, the DBMS can immediately find a record of 12 345 employees with EmpID-reduction potential of 100,000 operations.
TYPE index
The index fell to one of two categories: clustered and unclustered. The main difference between the two categories is that the unclustered index does not affect that bookings are actually index the records on disk and not clumped. Because the cluster indexing the physical affects the reservation record, there will be at most one clustered index per table. The same restrictions do not apply for unclustered indexes, so that we can make as much disk space is possible (although this is not always a good idea, as we shall see in a moment)
Clustered Index
Unclustered Index
Tuning Options tab allows the database developer to configure whether they want advisors to replace an existing index or only consider adding new ones. Database developer must evaluate the existing index to determine whether or not existing index should be dropped.
Once the configuration options set, click the "Start Analysis" akanmulai tuning. Once the analysis is complete, the window will be displayed along with recommendations to increase performance estimasiquery being optimized. Recommendations include:
Creating an index
Make statistical
Recommendations can be applied automatically through item> Apply Recommended Actions menu.
Estimated Cost for Database Optimization
Estimated cost of the process of implementing meaningful and consistent measure of execution costs for a particular query. Various metrics can be used for this purpose, but the most common and most relevant metric is the number of blocks needed olehquery access. Because disk I / O is like a costly operation in terms of time consumption, our goal is to minimize the number of access blocks as much as possible while not sacrificing functionality.
Estimated Operating Costs SELECT
Estimated Operating Costs JOIN
nested Loop
Single Loop (using index)
Sort-Merge Join
conclusion
After reading this article, database developers are expected to have a better understanding of basic database optimization techniques and how to formulate the DBMS execution strategies for different types of questions, although the examples given are very limited in scope. Database developers must also understand the importance of creating an index is really good and what criteria go into choosing the columns for indexing. "Up here the explanation from me about Database Optimization is indeed very important for us to learn, thank you and hopefully useful for all readers.
source
Categories: