Query optimizations

Now that we have a basic understanding of how to design our databases with performance in mind, we are ready to look at best practices for writing efficient queries. We will also look at query execution plans and some advanced SQL techniques. To make our example SQL statements relatable, we will use a book inventory and order processing database throughout this section.

Query execution

Understanding how queries are handled by our database is key to being able to optimize them.

A query execution plan

A query execution plan provides details on how a database engine executes queries

A query execution plan includes details on database query operations, such as joins and sorts. Let’s look at a simple query that gives us a specific book’s total sales:

FROM Orders o
JOIN Books b ON o.BookID = b.BookID
WHERE b.Title = 'High Performance with Java';

Now, let’s add an EXPLAIN command to the same query to reveal the steps the database engine follows to execute our query:

EXPLAIN SELECT SUM(o.Qty * o.Price) AS TotalSales
FROM Orders o
JOIN Books b ON o.BookID = b.BookID
WHERE b.Title = 'High Performance with Java';

By viewing the query execution plan, we can identify potential bottlenecks, providing us with an opportunity to further optimize our database and queries.

Next, let’s look at some best practices for writing efficient queries.

Best practices

Our goals when writing SQL queries are to minimize resource use and reduce execution time. To achieve these goals, we should follow best practices, including the ones detailed as follows for the SELECT statement, JOIN operations, subqueries, and common table expressions (CTEs).

SELECT statement

There are three best practices involved with using the SELECT statement. First, we should avoid using SELECT * and only specify the columns we need. For example, instead of using SELECT * FROM Books;, use SELECT Title, AuthorID, Genre FROM Books;.

Another best practice is to use the WHERE clause to narrow down our results to the maximum extent possible. Here is an example:

SELECT Title, AuthorID, Genre FROM Books WHERE Genre = 'Non-Fiction';

A third best practice for using the SELECT statement is to limit the number of rows returned by our query. We can use the LIMIT clause, as shown here:

SELECT Title, AuthorID, Genre FROM Books WHERE Genre = 'Non-Fiction' LIMIT 10;

The three best practices for working with the SELECT statement are key to improving the efficiency of our queries. Next, let’s look at best practices for using JOIN operations.

JOIN operations

There are two best practices for using JOIN operations. First, we should ensure that all columns used in JOIN conditions are indexed. This will improve the efficiency of these operations.

Another best practice is to use the appropriate JOIN type, as indicated in the following table.

Type	Purpose
INNER JOIN	Used to match rows
LEFT JOIN	To include all rows from the left table
RIGHT JOIN	To include all rows from the right table

Table 15.5: JOIN type and its purpose

Next, let’s look at the concept of subqueries and their related best practices.

Subqueries

As the title suggests, subqueries are used to break a complex query into multiple, simpler queries. Here is an example:

SELECT b.Title, b.Genre
FROM Books b
WHERE b.BookID IN (SELECT o.BookID FROM Orders o WHERE o.Qty > 100);

Next, let’s look at CTEs.

CTEs

CTEs can be used to make complex queries more readable. This increases their reusability and eases their maintainability. Here is an example:

WITH HighSales AS (
    SELECT BookID, SUM(Qty) AS TotalQty
    FROM Orders
    GROUP BY BookID
    HAVING SUM(Qty) > 100
)
SELECT b.Title, b.Genre
FROM Books b
JOIN HighSales hs ON b.BookID = hs.BookID;

Now that we have reviewed several best practices for writing queries, let’s look at some advanced SQL techniques.

Advanced SQL techniques

This section demonstrates three advanced SQL techniques – window functions, recursive queries, and temporary tables and views.

Window functions

A window function is used to calculate across a set of rows related to a current row. Here is an example:

SELECT BookID, Title, Genre,
      SUM(Quantity) OVER (PARTITION BY Genre) AS TotalSalesByGenre
FROM Books b
JOIN Orders o ON b.BookID = o.BookID;

Recursive queries

Recursive queries are complicated and can be useful when you have hierarchical data, such as book categories and subcategories. Here is an example:

WITH RECURSIVE CategoryHierarchy AS (
    SELECT CategoryID, CategoryName, ParentCategoryID
    FROM Categories
    WHERE ParentCategoryID IS NULL
    UNION ALL
    SELECT c.CategoryID, c.CategoryName, c.ParentCategoryID
    FROM Categories c
    JOIN CategoryHierarchy ch ON c.ParentCategoryID = ch.CategoryID
)
SELECT * FROM CategoryHierarchy;

Temporary tables and views

Another advanced technique is to use temporary tables and views to achieve better performance and help manage complex queries. Here is an example of a temporary table:

CREATE TEMPORARY TABLE TempHighSales AS
SELECT BookID, SUM(Qty) AS TotalQty
FROM Orders
GROUP BY BookID
HAVING SUM(Qty) > 100;
SELECT b.Title, b.Genre
FROM Books b
JOIN TempHighSales ths ON b.BookID = ths.BookID;

The following SQL statement is an example of a temporary view:

CREATE VIEW HighSalesView AS
SELECT BookID, SUM(Qty) AS TotalQty
FROM Orders
GROUP BY BookID
HAVING SUM(Qty) > 100;
SELECT b.Title, b.Genre
FROM Books b
JOIN HighSalesView hsv ON b.BookID = hsv.BookID;

Experimenting with the advanced techniques presented in this section can improve your ability to write efficient queries, contributing to the overall performance of your Java application.