Database Optimization and Performance Tuning Paper

docx

School

Grand Canyon University *

*We aren’t endorsed by this school

Course

400

Subject

Computer Science

Date

Dec 6, 2023

Type

docx

Pages

4

Uploaded by shawnarroyo

Report
Database Optimization and Performance Tuning Shawn Arroyo Grand Canyon University SYM-400 Introduction to Database Structures Mortoza Abdullah 2/26/2023 1
Query optimizer is a component within a database system that analyzes queries and helps simplify them for ease and efficiency. It will generate query plans and run them while finding the most efficient possibility. The 5 phases of query optimization are Parse, Binding, Simplification, Trivial Plans, and Explore Search. Within the parse phase, a query’s syntax is validated and processed within the database. If not, a syntax error would be returned. In the binding step, the query validates the data within the database that is being used. Within the simplification step, the optimizer will analyze the data within the logical tree of the query and simplify out any redundancy. In the trivial plans step, the optimizer will avoid cost-based optimization for any simple queries, so that resources are not wasted for full optimization. Lastly in the explore search phase, a cost will be calculated for all possible query plans and decide on the optimum plan. There are tools such as EverSQL, APEXSQL, SQL Azure, and SQL tuning that can be used to edit the needs for optimizing queries. The performance of a database system is measured by the database throughput. It is the amount of work completed by the database over a period of time, measured by the number of queries executed. Even though response time and throughput are similar, response time is related to certain queries while throughput is an overall measure of the database’s performance. Response times and throughput can be altered by allocating resources towards certain queries or the database’s queries overall. The physical design of a database will optimize performance and ensure data integrity. This allows for data redundancies to be avoided and helps with performance. When designing a physical database, the logical aspects need to be translated into table and attributes to make up the database. 2
Queries can be altered to increase database performance by being concise and only retrieving necessary data. Using * in a SELECT statement will query all the columns within a particular table. Only selecting certain columns that are needed would bring a faster response time and only show data that is going to be used. Creating indexes can help with retrieving data faster and optimizing queries by leaving a point that will be accessed by the query at a later time. This allows for faster data retrieval since the query will not have to access the entire database to find the data. Query optimization tools will help with creating a concise and optimal query for the data that is being retrieved. These can all be used as stored procedures especially if they are going to be constantly used to access the same information. 3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
References How To Optimize SQL Queries Performance - Best Practices | Tek-Tools. (n.d.). TekTools. https://www.tek-tools.com/systems/sql-query-optimization Query Optimization in Centralized Systems . (n.d.). Online Tutorials Library. https://www.tutorialspoint.com/distributed_dbms/distributed_dbms_query_optim ization_centralized_systems.htm#:~:text=Query%20optimization%20involves%20three %20steps,and%20query%20plan%20code%20generation.&text=A%20query%20tree%20is %20a,are%20represented%20as%20leaf%20nodes. IBM Documentation . (n.d.). IBM - Deutschland | IBM. https://www.ibm.com/docs/en/db2-for- zos/11?topic=relationships-physical-database-design Key Database Performance Metrics You Must Track Regularly - Ubiq BI . (n.d.). Ubiq BI. https://ubiq.co/analytics-blog/key-database-performance-metrics-must-track- regularly/#:~:text=Database%20throughput%20is%20one%20of,of%20queries%20executed %20per%20second. Response time and throughput . (n.d.). HCL Product Documentation. https://help.hcltechsw.com/onedb/2.0.1/prf/ids_prf_046.html#:~:text=Th roughputThroughput%20measures%20the%20overall,an%20individual%20transaction%20or %20query. 4

Related Documents

Practice Questions on Objects.docx
Module 1 Lab 1.11.JPG
Cmput 101_ Assignment python.pdf
Module 4 - Lesson 1_ Knowledge Check_ Business Application Programming with Lab - 61535.pdf
Module 3 - Lesson 1_ Knowledge Check.pdf
ABA assignemnt 1.docx
Foster_Supervision Instrument Narrative_Assignment.docx
Foster C_Hour of Code_Assignment.pdf
HW3_Report.pdf
Report.docx
Ch2_PracticeAnswers.txt
quiz 8.pdf
Recommended textbooks for you
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
SEE MORE TEXTBOOKS