Performance Optimization in SQL: A Beginner’s Guide. (14)

SQL is a powerful and popular language for querying and manipulating data in relational databases. However, writing efficient and fast SQL queries is not always easy. Sometimes, a poorly written query can cause performance issues, such as slow response time, high resource consumption, and poor user experience. Therefore, it is important to learn how to optimize SQL queries and improve their performance.

In this blog post, we will cover some of the basic concepts and techniques of SQL performance optimization, such as indexing, query optimization, and explain plan. We will also provide some examples and references for further learning. This post is part of the SQL Learning Series, where we aim to teach you the fundamentals and best practices of SQL in a simple and practical way. If you are interested in learning more about SQL, please follow me on Medium and LinkedIn, and check out the other posts in this series.

What is SQL Performance Optimization?

SQL performance optimization is the process of enhancing SQL queries to speed up the performance of your server. Its main goal is to shorten the time it takes for a user to receive a response after sending a query and to utilize fewer resources in the process. The idea is that users can occasionally produce the same intended result set with a faster-running query.

SQL performance optimization is speeding up queries against a relational database. There is not just one tool or method for optimizing SQL speed. Instead, it’s a set of procedures that utilize a variety of methods, and procedures1.

Let’s talk about some of the major factors that will influence how many computations you must perform and how long it takes for your query to run:

• Table size: Performance may be impacted if your query hits one or more tables with millions of rows or more.
• Joins: Your query is likely to be slow if it joins two tables in a way that significantly raises the number of rows in the return set.
• Aggregations: Adding several rows together to create a single result needs more processing than just retrieving those values individually.
• Other users executing queries: The more queries a database has open at once, the more it must process at once, and the slower it will all be. It can be particularly problematic if other users are using a lot of resources to perform queries that meet some of the aforementioned requirements.

In this post, we will focus on three main techniques that can help you optimize your SQL queries: indexing, query optimization, and explain plan.

Indexing Strategies

Table indexes in databases help retrieve information faster and more efficiently. In SQL Server, when you execute a query, the optimizer generates an execution plan. If it detects the missing index that may be created to optimize performance, the execution plan suggests this in the warning section. With this suggestion, it informs you which columns the current SQL should be indexed, and how performance can be improved upon completion.

For example, suppose we have a table called customers with the following columns: id, name, email, phone, address, city, state, and zip. If we want to query the customers by their name:

SQL

SELECT * FROM customers WHERE name = 'John Smith';

However, this query will perform a full table scan, which means it will check every row in the table to find the matching ones. This can be very slow if the table has a large number of rows. To speed up this query, we can create an index on the name column, which will store the values of this column in a sorted order and allow the query to use a binary search algorithm to find the matching rows. To create an index, we can use the following syntax:

SQL

CREATE INDEX idx_name ON customers (name);

Now, if we run the same query again, the execution plan will show that it uses the index to find the matching rows, which will be much faster than the full table scan.

You can also create indexes on multiple columns, which can be useful if you frequently query by a combination of columns. For example, if you often query by the city and state of the customers, you can create an index on both columns:

However, this query will perform a full table scan, which means it will check every row in the table to find the matching ones. This can be very slow if the table has a large number of rows. To speed up this query, we can create an index on the name column, which will store the values of this column in a sorted order and allow the query to use a binary search algorithm to find the matching rows. To create an index, we can use the following syntax:

SQL

CREATE INDEX idx_name ON customers (name);

Now, if we run the same query again, the execution plan will show that it uses the index to find the matching rows, which will be much faster than the full table scan.

You can also create indexes on multiple columns, which can be useful if you frequently query by a combination of columns. For example, if you often query by the city and state of the customers, you can create an index on both columns:

SQL

CREATE INDEX idx_city_state ON customers (city, state);

This will allow the query to use the index to filter by both columns at once, instead of scanning the whole table or using a separate index for each column.

However, creating indexes is not always a good idea. Indexes have some drawbacks, such as:

• They take up additional space on the disk and in memory.
• They increase the overhead of insert, update, and delete operations, as they need to be maintained and updated along with the table data.
• They can become fragmented over time, which can reduce their efficiency and performance.

Therefore, you should create indexes only on the columns that are frequently used in queries and that have high selectivity, which means they have a large number of distinct values and can filter out a large percentage of rows. You should also avoid creating too many indexes on the same table, as this can cause more harm than good.

Query Optimization:

Query optimization is the process of writing SQL queries in a way that minimizes the amount of work that the database has to do to execute them. Query optimization techniques in SQL Server include understanding the query, reducing the table size by filtering data, limiting the dataset in a subquery, avoiding duplicate data, and simplifying joins.

Here are some examples of query optimization techniques:

• Use the WHERE clause to filter out unnecessary rows from the result set. For example, if you only want to see the customers from New York:

SQL

SELECT * FROM customers WHERE state = 'NY';

This will reduce the number of rows that the query has to process and return.

• Use the LIMIT clause to limit the number of rows returned by the query. For example, if you only want to see the top 10 customers by their total orders:

SQL

SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total DESC
LIMIT 10;

This will stop the query after it finds the first 10 rows that match the criteria, instead of returning all the rows and sorting them.

• Use the EXISTS operator instead of the IN operator when checking for the existence of a value in a subquery. For example, if you want to see the customers who have placed at least one order, you can write:

SQL

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

This will return the customers as soon as it finds a matching order, instead of collecting all the customer ids from the orders table and comparing them with the customers table.

• Use the DISTINCT keyword to remove duplicate rows from the result set. For example, if you want to see the unique cities of the customers, you can write:

SQL

SELECT DISTINCT city FROM customers;

This will eliminate any duplicate city values from the result set.

• Use the JOIN operator instead of the WHERE clause to combine tables based on a common column. For example, if you want to see the customer name and the order amount for each order, you can write:

SQL

SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

This will perform a more efficient join operation than using a WHERE clause, such as:

SQL

SELECT c.name, o.amount
FROM customers c, orders o
WHERE c.id = o.customer_id;

Explain Plan:

Explain plan is a tool that shows how the SQL Server query optimizer executes a query step by step, scans indexes to retrieve data, and provides a detailed overview of metrics during query execution. You can use explain plan to analyze the performance of your queries, identify the bottlenecks, and find ways to improve them.

To generate an explain plan, you can use the EXPLAIN keyword before your query:

SQL

EXPLAIN SELECT * FROM customers WHERE name = 'John Smith';

This will show you a graphical representation of the query execution plan, which consists of different operators and arrows. Each operator represents a step or an action that the query performs, such as scanning a table, filtering a row, sorting a result, etc. Each arrow represents the flow of data between the operators. The thickness of the arrow indicates the amount of data that is passed.

The explain plan also shows you some useful information about each operator, such as:

• The estimated number of rows that the operator will process or return.
• The estimated cost of the operator, which is a measure of the resources that the operator will consume.
• The actual number of rows that the operator processed or returned, after the query execution.
• The actual time that the operator took to execute, after the query execution.

You can use this information to compare the estimated and actual values, and see if there is any discrepancy or deviation. This can help you identify if the query optimizer made any wrong assumptions or estimations, and adjust your query or your database accordingly.

For example, if you see that the actual number of rows is much higher than the estimated number of rows, it may mean that the query optimizer did not have accurate statistics about the table or the column, and chose a suboptimal execution plan. In this case, you may need to update the statistics or create an index to help the query optimizer make better decisions.

If you want to learn more about explain plan in SQL, you can check out some of the below weblinks.

1. Oracle
2. SQL Easy
3. Explaining & Displaying Execution Plans
4. SQL Execution or Explain plan
5. SQL Developer Execution Plan

They explain how to use and run the SQL Developer execution plan, how to interpret the execution plan output, and how to identify and fix performance issues.

Conclusion:

Let's summarize the main points and benefits of SQL performance optimization:

• SQL performance optimization is the process of enhancing SQL queries to speed up the performance of your server and improve the user experience.
• SQL performance optimization techniques include indexing, query optimization, and explain plan.
• Indexing is the process of creating and maintaining indexes on the columns that are frequently used in queries and that have high selectivity. Indexes help the query optimizer to find the matching rows faster and more efficiently.
• Query optimization is the process of writing SQL queries in a way that minimizes the amount of work that the database has to do to execute them. Query optimization techniques include using the WHERE clause, the LIMIT clause, the EXISTS operator, the DISTINCT keyword, and the JOIN operator.
• Explain plan is a tool that shows how the SQL Server query optimizer executes a query step by step, scans indexes to retrieve data, and provides a detailed overview of metrics during query execution. You can use explain plan to analyze the performance of your queries, identify the bottlenecks, and find ways to improve them.

I hope you enjoyed this post and learned something new about SQL performance optimization. If you did, please like 👍, share, and clap 👋for this post, and follow me on Medium and LinkedIn for more SQL tips and tricks. Also, don’t forget to join the SQL learning series community, where you can find more resources on SQL practice, test skills, and performance optimization. Also Check out Books available on Amazon →Performance Optimization.

Quote “The only way to learn a new programming language is by writing programs in it.” — Dennis Ritchie

Thank you for reading and happy learning! 😊