Boost SQL Query Performance: Practical Tips and Code Examples

No one likes dealing with slow queries. Waiting is stressful and can heavily impact the database.

Some simple methods can help solve this problem; before deep diving, consider this article.

All examples use the AdventureWorks2019 database, which you can download here.

Before making any changes, it is recommended to read the execution plan and understand why the query is slow. The tips below may still be helpful.

How to Instantly Improve My Query Performance?

Use SELECT TOP

TOP

clause can limit the number of rows returned, improving performance and reducing system load.

It is useful when you need a quick data check or only recent records, avoiding full table scans.

Limiting rows also reduces the chance of locking tables or causing performance issues.

Example:

SELECT TOP 100 *
FROM [AdventureWorks2019].[Production].[TransactionHistory]
ORDER BY [TransactionDate] DESC

Avoid Using DISTINCT

DISTINCT

can affect performance, especially on large datasets. If you see duplicate data, review table design or indexes to prevent duplication at the source.

Before using DISTINCT, understand the root cause of duplicates and build queries accordingly.

If you must use DISTINCT, apply it only to indexed columns to minimize cost.

Example:

SELECT DISTINCT
 [ProductID]
FROM [AdventureWorks2019].[Production].[TransactionHistory]

Prefer IN over OR in WHERE clause

Using IN is usually more efficient than multiple OR conditions, especially on large datasets. OR checks each condition individually, while IN processes multiple values with an optimized condition.

Example:

SELECT TOP 100 *
FROM [AdventureWorks2019].[Production].[TransactionHistory]
WHERE [ProductID] IN (358, 378)

Avoid SELECT *

Using SELECT * retrieves all columns, increasing data transfer and impacting performance.

Specifying only needed columns makes queries faster, clearer, and more resilient to schema changes.

Example:

SELECT TOP 1000
 [ProductID],
 [TransactionDate],
 [ActualCost],
 [Quantity]
FROM [AdventureWorks2019].[Production].[TransactionHistory]
WHERE [ProductID] IN (358, 378)

Use WHERE instead of HAVING when possible

Filtering with WHERE is more efficient because it limits rows before grouping and aggregation. HAVING filters after grouping, which can be more costly. Use HAVING only when you need to filter on aggregate values.

Example:

SELECT TOP 1000
 [ProductID],
 [TransactionDate],
 [ActualCost],
 [Quantity],
 SUM([ActualCost] * [Quantity]) AS TotalCost
FROM [AdventureWorks2019].[Production].[TransactionHistory]
WHERE [ProductID] IN (358, 378)
GROUP BY [ProductID], [TransactionDate], [ActualCost], [Quantity]
HAVING SUM([ActualCost] * [Quantity]) > 300

Index columns used in WHERE and JOIN clauses

Creating indexes on columns used in WHERE filters can speed up queries by reducing scanned data.

Similarly, indexing columns used in JOIN operations helps the database join tables more efficiently.

Example:

EXEC sp_helpindex '[Production].[TransactionHistoryArchive]';

Create a non‑clustered index on date columns with DESC

Creating a non‑clustered index on a date column with descending order speeds up queries that retrieve the most recent data.

This organization lets the database directly access recent rows without extra sorting.

Example:

CREATE NONCLUSTERED INDEX IX_TransactionHistory_TransactionDate_Desc
ON [Production].[TransactionHistory] ([TransactionDate] DESC);

Create non‑clustered indexes for frequently accessed columns

Non‑clustered indexes can accelerate queries that filter on specific columns.

Monitor common queries to identify hot columns and create indexes on them. You can include additional columns with INCLUDE to cover more queries without extra indexes.

Example:

CREATE NONCLUSTERED INDEX [IX_TransactionHistory_Fields_Include]
ON [Production].[TransactionHistory] ([TransactionDate] DESC, [ModifiedDate] DESC)
INCLUDE ([Quantity], [ActualCost]);

Maintain indexes regularly

Creating indexes is not enough; they need ongoing maintenance.

Frequent use leads to fragmentation and outdated statistics, harming performance. Set routines to reorganize or rebuild indexes and update statistics to keep queries running smoothly.

Example:

ALTER INDEX cix_your_table_id ON [your_table] REBUILD;
ALTER INDEX ncix_your_table_name_date ON [your_table] REBUILD;
ALTER INDEX cix_your_table_id ON [your_table] REORGANIZE;
ALTER INDEX ncix_your_table_name_date ON [your_table] REORGANIZE;

Running queries and maintenance during off‑peak hours reduces contention for CPU, memory, and I/O resources.

Use stored procedures for reporting and dashboard queries

Ad‑hoc queries are compiled and optimized each execution. Stored procedures have a pre‑compiled execution plan, saving processing time and allowing fine‑tuning.

Beyond performance, stored procedures improve security, organization, and maintainability, with execution rights limited to authorized users.

Example:

CREATE PROCEDURE GetTransactionByDate
 @date DATETIME
AS
BEGIN
 SELECT [ProductID],
        [TransactionDate],
        [TransactionType],
        [Quantity],
        [ActualCost],
        [ModifiedDate]
 FROM [AdventureWorks2019].[Production].[TransactionHistoryArchive]
 WHERE [TransactionDate] = @date;
END;

Use In‑Memory OLTP tables as temporary tables

In‑Memory OLTP stores tables in RAM instead of on disk, speeding up data access.

Using them as temporary tables in reports can significantly improve performance because memory reads/writes are much faster than disk operations.

This approach is especially helpful for handling large data volumes in reporting and processing pipelines.

Partition large tables

Partitioning splits a large table into smaller pieces, so inserts, updates, and deletes affect only the relevant partition.

This improves performance and reduces impact. Partitions can reside on different disks or servers; recent data on fast SSDs, older data on slower HDDs, optimizing both speed and cost.

Partitioning is an advanced technique but effective for managing large datasets.

For a detailed guide, see the linked article.

Remember, the best way to solve slow queries is to understand the execution plan and act accordingly.