Essay On Query Optimization

Query Optimization:-

Query optimization is the method of practicing the most efficient means of extracting data quickly from the database through performance optimal SQL queries. We can obtain the same result by writing different SQL queries. But by using the best query is important when performance is critical. Main objective of query optimization is to retrieve the data quickly. Query optimization helps to bring down speed of execution and save time in extracting the data and is cost effective. Most of the Databases after they are initially built and loaded with data and further usage will bring down the speed of data extraction. The time taken to execute the query and return the results increases as the amount of data increases in the database leading to more wait times on the application and user. Sometimes wait time to execute the query might lead to hours or days and could also result in deadlock issue in database. An SQL query or statement can be executed in many different ways, like index scans, full table scan, nested loops, and hash joins. It can handle more queries in the same amount of time because; each request will take less time than un-optimized queries.

Query optimizer being an important module of a DBMS has a greater impact on the database performance. It analyzes a number of candidate plans generated for a given query which have equivalent output but having different resource costs. It selects an efficient plan out of these candidates’ plans having the least cost. Although much work has been performed in query optimization, but cardinality estimation to manage optimization time and effective cost estimation with respect to the server state are still the challenging problems. Different methods for query processi...

... middle of paper ...

...oes not need to sort the results to distinguish the results.

8) De-normalization is also optimization technique use to speed up the query performance (It combines multiple tables into single table so table joins will be reduced).

In general developers are responsible for writing the proper code, but the DBA has critical responsibility to ensure that SQL is optimal. Developers and QA testers may test the application conscientiously, but the application may not scale well when exposed to heavy duty real life production environment conditions. Consequently DBAs are left scrambling to find solutions to a poorly performing SQL statement after the code is running in the production environment. In many cases we will experience performance problems on production environment that was designed and coded long ago. Try to fix the SQL Statements first if that’s at all possible.