Database Performance Tuning and Query Optimization Flashcards
- What is SQL performance tuning?
SQL performance tuning refers to activities on the client side that are designed to gen- erate SQL code that returns the correct answer in the least amount of time, using the minimum amount of resources at the server end.
- What is database performance tuning?
Database performance tuning refers to a set of activities and procedures designed to ensure that an end-user query is processed by the DBMS in the least amount of time.
- What are database statistics, and why are they important?
Database statistics refer to a number of measurements gathered by the DBMS that describe a snapshot of the database objects’ characteristics. The DBMS gathers statistics about objects such as tables, indexes, and available resources, which include the number of processors used, processor speed, and temporary space available. The DBMS uses the statistics to make critical decisions about improving query processing efficiency.
- In simple terms, the DBMS processes a query in three phases. What are the phases, and what is accomplished in each phase?
DBMSs process queries in three phases. In the parsing phase, the DBMS parses the SQL query and chooses the most efficient access/execution plan. In the execution phase, the DBMS executes the SQL query using the chosen execution plan. In the fetching phase, the DBMS fetches the data and sends the result set back to the client.
- What is the difference between a rule-based optimizer and a cost-based optimizer?
A rule-based optimizer uses preset rules and points to determine the best approach to execute a query. A cost-based optimizer uses sophisticated algorithms based on statistics about the objects being accessed to determine the best approach to execute a query. In this case, the optimizer process adds up the processing cost, the I/O costs, and the resource costs (RAM and temporary space) to determine the total cost of a given execution plan.
