Database Optimization and Performance Tuning Paper COMPLETE

docx

School

Grand Canyon University *

*We aren’t endorsed by this school

Course

400

Subject

Computer Science

Date

Jan 9, 2024

Type

docx

Pages

4

Uploaded by PeanutButterEater102

Report
Database Optimization and Performance Tuning Paper 1 SYM 400 Introduction to Database Structures Database Optimization and Performance Tuning Paper
Database Optimization and Performance Tuning Paper 2 A query optimizer is a crucial tool in a database management system (DBMS) and its purpose is to view the queries to find the best way to execute them. Once the query optimizer discovers which query is the most efficient it will use it to run the query. Phases and functions of the query optimizer Before the query optimizer can collect the data requested and execute it there are three phases that need to be performed which are parsing and translation, optimization, and evaluation. In the parsing and translation phase the computer takes the complex high level computer code and changes it into a readable human language while checking the syntax, verifying the name of the relation in the database, the tuple and attribute value. Once these steps are done a parse-tree is created in which the user can use relational algebra to translate further. The parsing tree is evaluated in the optimization phase to determine what the best option is to process the query being inputted. Another step in the optimization phase is determining the lowest cost method of executing the query while searching under the Where and Join operations. The final phase before executing the query is evaluation or query execution plan. In this phase the system conducts a plan or set of instructions on how to execute the query. A query execution engine generates the output of the query and executes the plan while making an output for the user query. Query Optimizing Tools and Performance Monitoring There are many tools available to edit queries and optimize them efficiently. The firs tool is called basic SQL tuning which involves the query optimizer rewriting the statements different ways to find the fastest option. There is also database engine support which can vary depending on what engine is being ran such as MySQL, MariaDB, Oracle, etc. The final optimization tool I will discuss is cloud-based database support which analyze and optimize cloud-hosted databases such as Microsoft SQL Azure or AWS RDS (Larue-Langlois, 2022). Checking the performance of a database is crucial since this involves seeing how well the database gathers data when requested. Some of the things to measure a database’s performance are throughput, response time analysis, wait times, and memory. Throughput measures how many queries are being ran per second while response time is the time between submitting a request and getting the results of that request. Database Physical Design A database relies heavily on its physical design in order to run at its best. Like setting up a network or subnet there are many different ways to set up a database and a database could be constantly changed physically to be optimized. It is important to recognize what needs the DBMS require to determine what physical design to go with for the database. Three ways to improve database performance Three different ways a user can utilize for better performance in their database are indexing, simplifying joins, and clearly defining the requirements. If a user has too many indexes it creates a lot of overhead traffic and uses a lot of data. If there are not enough indexes the query optimizer has to go through more of the database to gather the results which takes more time. When a query joins tables sometimes it can slow the execution time. Something as simple as changing the order you join tables in can also optimize
Database Optimization and Performance Tuning Paper 3 SQL queries (Tek-Tools, 2021). If the user does not define clear requirements they could receive data that is not relevant and duplicate or null results. If a user takes these steps database optimization will be effective and run efficiently with no performance issues.
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
Database Optimization and Performance Tuning Paper 4 References Ali, I. (2023, April 19). Ultimate Guide to MySQL Performance Tuning for Optimal Database Efficiency. The Official Cloudways Blog. https://www.cloudways.com/blog/mysql-performance-tuning/ Java T Point. (n.d.). Query processing in DBMS. https://www.javatpoint.com/query-processing-in-dbms Larue-Langlois, R. (2022, February 8). 6 Best SQL Query Optimization Tools in 2022. addictivetips.com. https://www.addictivetips.com/net-admin/sql-query-optimization-tools/ Tek-Tools. (2021, November 1). How to optimize SQL queries performance - Best practices. https://www.tek-tools.com/systems/sql-query-optimization

Related Documents

SYM 400 Tables Keys and Constraints.docx
SYM 400 Expanding the Scope of Queries COMPLETE.docx
ITT 307 Topic 3 DQ1.docx
SYM 400 Storage Indexing and Views COMPLETE.docx
ITT 307 Installing GUI Hash Generator.docx
ITT 370 Topic 2 DQ1.docx
Term Group Project Deliverable 1.docx
workshop_9.pdf
ALU Lab.docx
DBch5a (1)-1-1(1).docx
DB3a1-1-2(1).docx
CS331Prac1.docx
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
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
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
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
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
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
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
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
SEE MORE TEXTBOOKS