SQL is the language for interacting with databases, and like all computer languages, there are many ways to achieve the right result. And like other software languages, some ways will be better and more efficient than others. What sets SQL and database programming apart from most other computer programming is the vast amount of data that is regularly dealt with, and how other factors that are not immediately obvious can impact the SQL query code you write.
This course looks at ways that you can see how the SQL Server engine views your queries and how it intends to execute them. Using that information, we look at other factors like table and index design that can positively or negatively impact your query execution.
For any feedback relating to this course, please contact us at support@cloudacademy.com.
Learning Objectives
- Understand what impacts query performance when using SQL
- Learn how the SQL Server engine processes queries
- Use execution plans to see how a query is executed
- Instruct the query engine
- Understand how different query types can be used
- Learn how to design tables to make queries more efficient
- Use database file configuration to improve performance
- Understand how database compression can impact performance
Intended Audience
This course is aimed at database administrators, data engineers, or anyone who wants to learn how to improve database query execution.
Prerequisites
To get the most out of this course, you should be familiar with SQL query syntax, SQL data types, and SQL table structure.
Resources
https://github.com/cloudacademy/azure-sql-query-tuning-dp-300
As I said in the introduction, query structure is definitely not the whole story to better query performance. The biggest bottleneck to efficiency and speed, as with most computer systems, is accessing data from the hard drive. This bottleneck is particularly acute with databases due to their massive size.
Table structure and in particular indexes have the greatest bearing on how quickly you can pull the data you want. I don't think it's an exaggeration to say that without a good structure and indexes, finding a particular record in a 20-gigabyte table would be like you were asked to find Alec Baldwin and the only information you had to go on, that he was in New York. With no other clues, you would drive up to the outskirts of New York and then just start knocking on doors and asking, "Are you Alec Baldwin?" On average, it will take a very long time to find him.
When put in those terms, it seems like an outrageous task, yet routinely computers are asked to do a similar thing in poorly structured databases. It's only because electrons move at the speed of light that it doesn't seem like a big ask to us humans. But when the database and query are fully optimized, it's like you are being given the address of Alec Baldwin's apartment building. It's literally orders of magnitude improvement in efficiency, and we can expect to see the same improvement with a database.
Obviously, with the database, you aren't always looking for people, but products or dates and times. In fact, you might want to find any piece of data that is stored. More often than not, you don't know ahead of time what you are wanting to find.
Very broadly speaking, the search strategy comes in two parts. Firstly, indexes, which are maps or directories of your data, and secondly, the strategy of divide and conquer. This is essentially breaking down a large amount of data into smaller pieces that can be searched more quickly. I know in a sense that is how indexes can be viewed, but this idea of segmenting or partitioning data can be applied to all structural levels of the database, from files to tables and indexes.
To be honest, I did struggle a bit with how to start this course. I could start at the macro level with structuring the database's files, or at the beginning of building a database with the table design. But I decided that to really appreciate the performance benefits of correctly doing those first two things is to be able to assess the difference between good and bad design. So with that in mind, let's start by looking at the tools we can use to measure query performance.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.