Tips and tricks to improve query performance

dbForge Studio for SQL Server can help you improve SQL query performance with tools such as Query Profiler, AI Assistant, and T-SQL Code Analyzer. This topic outlines the most effective techniques for executing queries efficiently, along with practical examples.

Note

Query execution results may vary even when using the same schemas and similar test data. To ensure accuracy, verify query performance in both development and production environments.

Use JOINs instead of subqueries

Using JOINs instead of subqueries is a common technique for improving performance. JOINs enable SQL Server to retrieve related data efficiently by using indexed relationships between tables. This significantly speeds up query processing and improves overall execution time, especially when compared to nested queries. You can use Query Profiler to measure query performance. To compare the execution time of different queries:

1. On the toolbar, select New SQL, then click Query Profiling.

2. In the new SQL document, enter your query.

3. Click Execute to run the query and view performance metrics.

The following examples demonstrate how query performance can vary between a JOIN and a subquery.

Example of a query with a subquery

SELECT 
    soh.salesorderid,
    soh.orderdate,
    (SELECT per.firstname + ' ' + per.lastname
     FROM person.person per
     WHERE per.businessentityid = (
         SELECT c.personid
         FROM sales.customer c
         WHERE c.customerid = soh.customerid
     )) AS customer_name,
    (SELECT pr.name
     FROM production.product pr
     WHERE pr.productid = sod.productid) AS product_name,
    sod.orderqty,
    sod.linetotal
FROM sales.salesorderheader soh,
     sales.salesorderdetail sod
WHERE soh.salesorderid = sod.salesorderid
  AND soh.orderdate BETWEEN '2013-01-01' AND '2013-12-31'
  AND sod.orderqty > 5
ORDER BY soh.salesorderid, product_name;

Example of an equivalent query using a JOIN

SELECT 
    soh.salesorderid,
    soh.orderdate,
    per.firstname + ' ' + per.lastname AS customer_name,
    pr.name AS product_name,
    sod.orderqty,
    sod.linetotal
FROM sales.salesorderheader soh
INNER JOIN sales.salesorderdetail sod 
    ON soh.salesorderid = sod.salesorderid
INNER JOIN production.product pr 
    ON sod.productid = pr.productid
INNER JOIN sales.customer c 
    ON soh.customerid = c.customerid
INNER JOIN person.person per
    ON c.personid = per.businessentityid
WHERE soh.orderdate BETWEEN '2013-01-01' AND '2013-12-31'
  AND sod.orderqty > 5 
ORDER BY soh.salesorderid, product_name;

The results show that the JOIN query runs more efficiently than the query that uses a subquery.

Apply data filtering instead of using functions

To improve SQL query performance, filter data using actual values instead of applying functions to columns. When a function is applied directly to a column (e.g., WHERE YEAR(date_column) = 2023), SQL Server can’t use indexes efficiently. This often results in full table or index scans that can significantly slow down query execution.

In the following examples, we will first use T-SQL Code Analyzer to identify potential performance issues, and then compare the efficiency of a query that applies a function to a column and a query that filters data without functions.

Step 1: Analyze a query using T-SQL Code Analyzer.

1. On the toolbar, select New SQL to open a new SQL document.

2. Enter the query you want to analyze.

3. Right-click in the editor and select Analyze Code.

4. View the results in the Output window or the Error List.

Example of a query with a function

SELECT 
    soh.SalesOrderID, 
    soh.OrderDate
FROM 
    Sales.SalesOrderHeader soh
WHERE 
    CHARINDEX('2013', CONVERT(varchar(10), soh.OrderDate, 120)) > 0;

Example of a query with data filtering

SELECT 
    soh.SalesOrderID, 
    soh.OrderDate
FROM 
    Sales.SalesOrderHeader soh
WHERE 
    soh.OrderDate >= '2013-01-01';

Step 2: Compare query efficiency.

1. On the toolbar, select New SQL, then click Query Profiling.

2. In the new SQL document, enter both queries.

3. Click Execute to run the query and view performance metrics.

Add an index to data filtering queries

To improve the performance of queries that filter data by column values, create an index on the filtered column. An index allows SQL Server to locate matching rows faster than scanning the entire table.

You can use Query Profiler to measure the performance impact of indexing.

1. On the toolbar, select New SQL, then click Query Profiling.

2. In the new SQL document, enter your query.

3. Click Execute to run the query and view performance metrics.

The following examples show query performance before and after applying an index.

Example of a query with column filtering

SELECT
    BusinessEntityID,
    EmailAddress,
    EmailAddressID, 
    ModifiedDate
FROM
    Person.EmailAddress
WHERE
    ModifiedDate = '2013-01-01';

Example of a query with an index

CREATE NONCLUSTERED INDEX IX_Modified_Date
     ON Person.EmailAddress (ModifiedDate);

The results show that a query that uses an indexed column runs faster than the same query without an index.

Transform a UNION-based query into a PIVOT query

The UNION operator combines the results of two or more SELECT statements into a single result set. When you use several UNION operations, SQL Server must repeatedly scan and process the data, which can reduce performance.

In many cases, you can replace UNION with the PIVOT operator. PIVOT transforms rows into columns in a single scan, which is generally more efficient and requires fewer resources.

In this example, we will measure the performance of a query that uses multiple UNION statements by using Query Profiler. We will then convert the query to use PIVOT with AI Assistant and measure its performance again.

Step 1: Compare performance using Query Profiler.

1. On the toolbar, select New SQL, then click Query Profiling.

2. In the new SQL document, enter the UNION-based query you want to test.

3. Click Execute to run the query and view the profiling results.

Step 2: Modify the query using AI Assistant.

1. Click the AI Assistant icon on the toolbar and select Open Chat Window.

2. Enter a prompt that includes the UNION-based query and click Send.

3. Copy the optimized PIVOT query returned by the AI Assistant.

4. Paste it into the SQL document and click Execute to run the query.

Example of a UNION query

WITH SalesData AS (
    SELECT YEAR(soh.OrderDate) AS OrderYear, 'Bikes' AS Category, SUM(soh.TotalDue) AS SalesAmount
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p ON sod.ProductID = p.ProductID
    JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
    JOIN Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
    WHERE pc.Name = 'Bikes'
    GROUP BY YEAR(soh.OrderDate)
    UNION
    SELECT YEAR(soh.OrderDate), 'Accessories', SUM(soh.TotalDue)
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p ON sod.ProductID = p.ProductID
    JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
    JOIN Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
    WHERE pc.Name = 'Accessories'
    GROUP BY YEAR(soh.OrderDate)
    UNION
    SELECT YEAR(soh.OrderDate), 'Clothing', SUM(soh.TotalDue)
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p ON sod.ProductID = p.ProductID
    JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
    JOIN Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
    WHERE pc.Name = 'Clothing'
    GROUP BY YEAR(soh.OrderDate)
)
SELECT 
    OrderYear,
    SUM(CASE WHEN Category = 'Bikes' THEN SalesAmount ELSE 0 END) AS Bikes,
    SUM(CASE WHEN Category = 'Accessories' THEN SalesAmount ELSE 0 END) AS Accessories,
    SUM(CASE WHEN Category = 'Clothing' THEN SalesAmount ELSE 0 END) AS Clothing
FROM SalesData
GROUP BY OrderYear
ORDER BY OrderYear;

Example of an equivalent PIVOT query generated by AI Assistant

 WITH SalesData AS (
    SELECT YEAR(soh.OrderDate) AS OrderYear, pc.Name AS Category, SUM(soh.TotalDue) AS SalesAmount
    FROM Sales.SalesOrderHeader soh
    JOIN Sales.SalesOrderDetail sod ON soh.SalesOrderID = sod.SalesOrderID
    JOIN Production.Product p ON sod.ProductID = p.ProductID
    JOIN Production.ProductSubcategory psc ON p.ProductSubcategoryID = psc.ProductSubcategoryID
    JOIN Production.ProductCategory pc ON psc.ProductCategoryID = pc.ProductCategoryID
    WHERE pc.Name IN ('Bikes', 'Accessories', 'Clothing')
    GROUP BY YEAR(soh.OrderDate), pc.Name
)
SELECT OrderYear, [Bikes], [Accessories], [Clothing]
FROM SalesData
PIVOT(
    SUM(SalesAmount)
    FOR Category IN ([Bikes], [Accessories], [Clothing])
) AS PivotTable
ORDER BY OrderYear;

In this example, the PIVOT query executed faster and used fewer resources than the UNION-based query.