Maximize SQL Query Performance and Efficiency at Rego University
Enhance your SQL query execution time and minimize system resource usage with tips from Rego University's educational community. Learn about code reusability, database optimization, and best practices for improving SQL query performance. Presented by Dave Matzdorf on April 1, 2015.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
www.regouniversity.com www.regouniversity.com Clarity Educational Community Clarity Educational Community Creating and Tuning SQL Queries that Engage Users Presented by: Dave Matzdorf | Date Prepared: April 1, 2015
Agenda Goals Reusing Code Know Your Data Know Your Database Bottlenecks Tips and Tricks Exercises 2 2 Clarity Educational Community Clarity Educational Community
Goal Improve the execution time of SQL queries Minimize system resources required to execute queries Utilize lesser known built in functions to avoid complexity Give the SQL optimizer the best chance of selecting a good execution plan 3 3 Clarity Educational Community Clarity Educational Community
Reusing code Copying code Over-reliance can lead to bloated and inefficient code Review every time code is used Understand the code Database views One size does not fit all Stacked views 4 4 Clarity Educational Community Clarity Educational Community
Know your data What areas of the application are being used and in what volumes Be a partner in the requirements, not just a developer Understand the data model Entity Relationship Diagram Data dictionary tables SQL Traces 5 5 Clarity Educational Community Clarity Educational Community
Know Your Database SQL vs Procedural SQL (PL/SQL, T-SQL) Do not use PL/SQL when SQL can be used Row by row vs. set based processing Context switching Hard parsing vs. soft parsing Query caching Hard parsing Soft parsing Dynamic SQL Limited query caching benefits Can often be written as static SQL 6 6 Clarity Educational Community Clarity Educational Community
Know Your Database Bind Variables Bind variables Finding queries in the cache without exact text match SELECT ID FROM SRM_RESOURCES WHERE UNIQUE_NAME = dmatzdorf SELECT ID FROM SRM_RESOURCES WHERE UNIQUE_NAME = ? Escape characters Data type handling Crucial when executing SQL within a programming loop 7 7 Clarity Educational Community Clarity Educational Community
Common Bottlenecks IN vs. Exists IN is typically better when the inner query contains a small result set EXISTS is typically better when the inner query contains a large result set EXISTS vs. DISTINCT DISTINCT produces the entire result set (including duplicates), sorts, and then filters out duplicates EXISTS proceeds with fetching rows immediately after the sub-query condition has been satisfied the first time Double dipping UNION queries Repeated Inline Views 8 8 Clarity Educational Community Clarity Educational Community
Subquery Factoring WITH Clause Simplify complex queries Reduce repeated table access by generating temporary datasets during query execution Can be used as an inline view or a table 9 9 Clarity Educational Community Clarity Educational Community
Analytic Functions Deriving Totals Replaces complex logic to summarize data Can be partitioned to derive summaries at different levels Provide simple solutions to things like running totals 10 10 Clarity Educational Community Clarity Educational Community
Analytic Functions Selecting Records Replaces complex logic select certain records Select most recent status report Fetch prior or next records 11 11 Clarity Educational Community Clarity Educational Community
Working Examples Pull a list of users that belong to at least one security group Get the first resource assigned to each project team What percentage of total project allocation belongs to each team member 12 12 Clarity Educational Community Clarity Educational Community
Questions We hope that you found this session informative and worthwhile. Our primary goal was to increase your understanding of the topic and CA PPM in general. Phone 888.813.0444 Email info@regouniversity.com There were many concepts covered during the session, if you would like to contact any presenter with questions, please reach out to us. Website www.regouniversity.com Thank you for attending regoUniversity 2015! 13 13 Clarity Educational Community Clarity Educational Community
