When working with highly loaded databases, poorly designed queries can significantly degrade performance. To ensure efficient query execution, it is important to minimize the utilization of server resources. In SQL Server, query optimization can be achieved through various methods and techniques.

One useful tool for identifying performance bottlenecks in SQL Server is the EXPLAIN command. By using EXPLAIN before executing a query, you can obtain valuable insights into how the database server processes the query. However, manually adding EXPLAIN to each query can be tedious and time-consuming.

To simplify the process of identifying problematic queries, SQL Server provides tools like the Query Profiling Mode and Generate Execution Plan in dbForge Studio for SQL Server. These tools allow you to analyze the execution plan of a query and identify areas for optimization.

Let’s consider a simple example to demonstrate the use of execution plans. Suppose we have a database with two tables: models and products. We want to find the vendors of PCs, excluding laptops. Here is the sample data:

CREATE TABLE models (
    model_id INT (11) NOT NULL PRIMARY KEY,
    model_type VARCHAR (10) NOT NULL
);

CREATE TABLE products (
    maker_id INT (11) NOT NULL,
    model_id INT (11) NOT NULL,
    PRIMARY KEY (maker_id, model_id),
    CONSTRAINT FK_products_models_model_id FOREIGN KEY (model_id) REFERENCES models (model_id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO models (model_id, model_type)
VALUES (1, 'PC'), (2, 'Laptop'), (3, 'Tablet'), (4, 'Phone'), (5, 'PC'), (6, 'Laptop');

INSERT INTO products (maker_id, model_id)
VALUES (1, 1), (1, 2), (1, 3), (2, 4), (4, 4), (2, 5), (3, 5), (3, 6);

A common approach to solving this task is to use a query with JOIN and NOT EXISTS clauses:

SELECT DISTINCT p.maker_id
FROM products AS p
JOIN models m ON p.model_id = m.model_id
WHERE m.model_type = 'PC'
AND NOT EXISTS (
    SELECT p2.maker_id
    FROM products AS p2
    JOIN models m2 ON p2.model_id = m2.model_id
    WHERE m2.model_type = 'Laptop'
    AND p2.maker_id = p.maker_id
);

However, this query can be rewritten to eliminate re-readings from the same tables, which can impact performance. By using aggregating functions, we can simplify the query:

SELECT p.maker_id
FROM products AS p
JOIN models AS m ON p.model_id = m.model_id
WHERE m.model_type IN ('PC', 'Laptop')
GROUP BY p.maker_id
HAVING COUNT(CASE WHEN m.model_type = 'PC' THEN 1 END) > 0
AND COUNT(CASE WHEN m.model_type = 'Laptop' THEN 1 END) = 0;

Even with a large number of records, the JOIN to the models table can still affect performance. To speed up the query, denormalization of the schema can be performed by duplicating the data from the model_type column of the products table:

UPDATE products AS p
JOIN models AS m ON p.model_id = m.model_id
SET p.model_type = m.model_type;

It is also recommended to index the model_type column since it is filtered by. However, careful consideration should be given to the selection of columns included in the primary key, as including columns in the primary key can increase the database size.

As a result, the selection query can be further simplified:

SELECT maker_id
FROM products
GROUP BY maker_id
HAVING COUNT(CASE WHEN model_type = 'PC' THEN 1 END) > 0
AND COUNT(CASE WHEN model_type = 'Laptop' THEN 1 END) = 0;

By optimizing the query and considering the server configuration, you can significantly improve the performance of your SQL Server database. It is important to note that there is no one-size-fits-all solution for query optimization, and the optimal settings may vary depending on the specific tasks performed.

Using tools like the profiler embedded in dbForge Studio for SQL Server can help you identify performance bottlenecks and fine-tune your server configuration. For a more detailed discussion on server configuration, stay tuned for our upcoming article.