1 / 11

Query Optimization Presented by Mary Radnor

Query Optimization Presented by Mary Radnor. Overview of Query Optimization Purpose Steps to Optimizing Your Own Queries General Techniques MS Access ORACLE Example Summary. Purpose of Query Optimization To optimize the performance of the database Because…

dreama
Télécharger la présentation

Query Optimization Presented by Mary Radnor

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Query Optimization Presented by Mary Radnor

  2. Overview of Query Optimization • Purpose • Steps to Optimizing Your Own Queries • General Techniques • MS Access • ORACLE • Example • Summary

  3. Purpose of Query Optimization • To optimize the performance of the database • Because… • Queries having the same final output will vary in the amount of time it takes to execute them • No single optimization technique is best for every query

  4. Steps to Optimizing Your Own Queries • Develop multiple ways to run it • Test and measure performance • Choose the one that is the fastest • Over-Optimization: Where more resources are used to determine the best plan than is required to execute the plan

  5. General Query Optimization Techniques • Use short table and field names instead of long names • Use numeric primary keys instead of text • Use primary keys or unique indexes when possible • And many, many more…. • Query Performance is Also Affected by • Tables and/or queries that underlie it • Complexity of the query

  6. Methods the MS Access Query Optimizer Uses • Display the minimum number of fields in a query • Use non blank unique fields • Careful use of Between and Equal to, rather than > or < • Use numeric rather than text primary keys • Use primary keys or unique indexes whenever possible • Many more!

  7. The ORACLE Query Optimizer • Is more complex than the MS Access Query Optimizer • Uses many different techniques to optimize queries since no single technique is best for everything • Ex. Full-Table Scan, B-Tree, Reverse-Key Index, etc

  8. Example Query Products table contains 77 rows SQL: SELECT ProductID, ProductName, UnitPrice FROM Products WHERE (UnitPrice > 12.5) AND (UnitPrice < 14) Performs a full table scan

  9. Optimize Query by Using an Index SQL: CREATE INDEX [IDX_UnitPrice] ON Products (UnitPrice) SELECT ProductID, ProductName, UnitPrice FROM PRODUCTS WHERE UnitPrice BETWEEN 12.5 AND 14 This sorts the values of UnitPrice into a B-tree The database quickly narrows the number of records it must look at to a minimum

  10. Summary • The goal is to optimize the performance of the database • There are many ways to execute a query • Even the best of query optimizers may not always find the ideal way to execute any given query • This presentation is a small overview of the query optimization field

  11. References Advent-It.co.uk. 6 Nov 2005. Microsoft Access Query Optimization. <http://www.advent-it.co.uk/queryop.html> Jacobs, Ken. “Query Optimization.” ORACLE Magazine. Jul/Aug 2002 <http://www.oracle.com/technology/oramag/oracle/02- jul/o42dba.html> Ioannidis, Yannis. “Query Optimization.” ACM Computing Surveys 28:1 (March 1996): 121-3. OdeToCode.com. 9 Nov 2005. SQL Server Indexes. <http://odetocode.com/Articles/70.aspx> Riccardi, Greg. Principles of Database Systems with Internet and Java Applications. Boston: Addison Wesley, 2001. SQLSummit.com. 6 Nov 2005. Query Optimization. <http://www.sqlsummit.com/Optimization.htm#real%20world>

More Related