Improving Query Performance in SQL Server
In this course, students will learn to write queries that are both efficient and easy to read and understand.
Start Course for Free4 Hours16 Videos58 Exercises
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.Loved by learners at thousands of companies
Course Description
A mission critical assignment is depending on your SQL coding skills. You’ve been given some code to fix. It is giving the results you need but it’s running too slow, and it’s poorly formatted making it hard to read. The deadline is tomorrow. You’ll need to reformat the code and try different methods to improve performance. The pressure is on!!! In this course we’ll be using SQL on real world datasets, from sports and geoscience, to look at good coding practices and different ways how we can can improve the performance of queries to achieve the same outcome.
- 1
Introduction, Review and The Order of Things
FreeIn this chapter, students will learn how SQL code formatting, commenting, and aliasing is used to make queries easy to read and understand. Students will also be introduced to query processing order in the database versus the order of the SQL syntax in a query.
Introduction50 xpFormatting - player BMI100 xpCommenting - player BMI100 xpCommenting - how many Kiwis in the NBA?100 xpAliasing50 xpAmbiguous column names50 xpAliasing - team BMI100 xpQuery order50 xpProcessing order50 xpSyntax order - New Zealand earthquakes100 xpSyntax order - Japan earthquakes100 xpSyntax order - very large earthquakes100 xp - 2
Filtering and Data Interrogation
This chapter introduces filtering with WHERE and HAVING and some best practices for how (and how not) to use these keywords. Next, it explains the methods used to interrogate data and the effects these may have on performance. Finally, the chapter goes over the roles of DISTINCT() and UNION in removing duplicates and their potential effects on performance.
Filtering with WHERE50 xpColumn does not exist100 xpFunctions in WHERE100 xpTest your knowledge of WHERE50 xpFiltering with HAVING50 xpRow filtering with HAVING100 xpFiltering with WHERE and HAVING100 xpTest your knowledge of HAVING50 xpInterrogation after SELECT50 xpSELECT what you need100 xpLimit the rows with TOP100 xpShould I use ORDER BY?50 xpManaging duplicates50 xpRemove duplicates with DISTINCT()100 xpUNION and UNION ALL100 xpUNION or DISTINCT()?50 xp - 3
Sub-queries and presence or absence
This chapter is an introduction to sub-queries and their potential impacts on query performance. It also examines the different methods used to determine if the data in one table is present, or absent, in a related table.
Sub-queries50 xpUncorrelated sub-query100 xpCorrelated sub-query100 xpSub-query vs INNER JOIN100 xpPresence and absence50 xpINTERSECT100 xpEXCEPT100 xpInterrogating with INTERSECT100 xpAlternative methods 150 xpIN and EXISTS100 xpNOT IN and NOT EXISTS100 xpNOT IN with IS NOT NULL100 xpAlternative methods 250 xpINNER JOIN100 xpExclusive LEFT OUTER JOIN100 xpTest your knowledge50 xp - 4
Query performance tuning
Students are introduced to how STATISTICS TIME, STATISTICS IO, indexes, and executions plans can be used in SQL Server to help analyze and tune query performance.
Time statistics50 xpSTATISTICS TIME in queries100 xpSTATISTICS TIME results50 xpPage read statistics50 xpSTATISTICS IO: Example 1100 xpSTATISTICS IO: Example 2100 xpSTATISTICS IO comparison50 xpIndexes50 xpTest your knowledge of indexes50 xpClustered index100 xpExecution plans50 xpSort operator in execution plans100 xpTest your knowledge of execution plans50 xpQuery performance tuning: final notes50 xp
Collaborators
Prerequisites
Intermediate SQL ServerDean Smith
See More
Founder, Atamai Analytics
Dean Smith is geoscientist specializing in information management systems for mining and mineral exploration. He has more than ten years of experience working with SQL Server and data visualization. Dean is the founder of Atamai Analytics, which provides information management and data analytics consulting services to the mining and mineral exploration communities.
Join over 12 million learners and start Improving Query Performance in SQL Server today!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.