Improve the performance of SQL queries

Let's see what this GPT can do. For this example, we are using SQL Server and the AdventureWorks2019 database.

Me: Please improve this query:

SELECT p.ProductID,

       p.Name AS ProductName,

       s.[BusinessEntityID] AS SalesPersonName,

       od.OrderQty,

       od.UnitPrice,

       od.LineTotal

FROM Sales.SalesOrderDetail od

    JOIN Sales.SalesOrderHeader soh

        ON soh.SalesOrderID = od.SalesOrderID

    JOIN Sales.SalesPerson s

        ON s.BusinessEntityID = soh.SalesPersonID

    JOIN Production.Product p

        ON p.ProductID = od.ProductID

WHERE soh.OrderDate

      BETWEEN '2012-01-01' AND '2022-12-31'

      AND s.[BusinessEntityID] IS NOT NULL

      AND p.Name LIKE '%bike%'

      AND od.OrderQty > 10

ORDER BY soh.OrderDate;

SQL Expert (QueryGPT):

1. Indexes

Ensure that the following columns have indexes for better performance:

Sales.SalesOrderDetail(SalesOrderID, ProductID) for the JOIN with SalesOrderHeader and Product.

Sales.SalesOrderHeader(SalesOrderID, SalesPersonID, OrderDate) for the JOIN with SalesOrderDetail and SalesPerson.

Sales.SalesPerson(BusinessEntityID) for the JOIN with SalesOrderHeader.

Production.Product(ProductID) for the JOIN with SalesOrderDetail.

Production.Product(Name) for the LIKE condition on p.Name.

Indexes on these columns can significantly reduce the amount of data that needs to be scanned and joined.

2. Optimize LIKE operation

The LIKE '%bike%' condition will cause a full table scan on the Product.Name column, as it cannot take advantage of an index effectively due to the leading wildcard %. If possible, consider changing this to a more specific search term or an indexed column.

If '%bike%' is non-negotiable, you might consider using a full-text search (if supported by your database), which is optimized for such text-based queries.

3. Filter OrderQty first

The condition od.OrderQty > 10 should be applied before the JOIN operation if possible. Filtering rows earlier helps to reduce the data volume during the join process. This depends on your DBMS's query planner, but it's typically a good strategy.

4. Consider limiting the result set

If the query needs to be highly optimized and only returns a small subset of the data, consider adding a LIMIT clause if it fits your use case.

5. Avoid IS NOT NULL on SalesPersonID

If BusinessEntityID is a primary key or cannot be NULL in the SalesPerson table, the IS NOT NULL condition is unnecessary. This can be removed to simplify the query.

6. Use INNER JOIN explicitly

If you're confident that all the JOIN operations are required (i.e., the relationships will never be NULL for the fields), then you can ensure all joins are INNER JOIN rather than an implicit join type (though in your case, you are already using explicit JOIN).

7. Query Rewrite

Here's a refined version of your query based on the optimizations:

SELECT p.ProductID,

       p.Name AS ProductName,

       s.BusinessEntityID AS SalesPersonName,

       od.OrderQty,

       od.UnitPrice,

       od.LineTotal

FROM Sales.SalesOrderDetail od

    JOIN Sales.SalesOrderHeader soh

        ON soh.SalesOrderID = od.SalesOrderID

    JOIN Sales.SalesPerson s

        ON s.BusinessEntityID = soh.SalesPersonID

    JOIN Production.Product p

        ON p.ProductID = od.ProductID

WHERE soh.OrderDate

      BETWEEN '2012-01-01' AND '2022-12-31'

      AND od.OrderQty > 10

      AND p.Name LIKE '%bike%' -- This condition could be optimized with full-text search if possible

      AND s.BusinessEntityID IS NOT NULL

ORDER BY soh.OrderDate;

Key Points:

Indexes on the involved columns, particularly SalesOrderDetail(SalesOrderID, ProductID), SalesOrderHeader(SalesOrderID, SalesPersonID, OrderDate), and Production.Product(ProductID).

Evaluate the LIKE clause for optimization (perhaps use full-text search if your DBMS supports it).

Consider the execution plan to check if reordering WHERE conditions help.

Me: As you can see, SQL Expert (SQLGPT) offers a convenient way to improve. It provides index suggestions to optimize like usage, and guidance on changing the order of filters, avoiding nulls, etc. It also offers a fixed query. Let me ask the SQL Expert to generate the T-SQL code for the indexes.

Create indexes with the SQL expert AI

Me: Can you provide me with the code to create indexes for the previous query?

SQL Expert:

To improve the performance of the query you've provided, creating indexes on the columns involved in filtering, joining, and ordering operations will help. Here's the code to create indexes:

- 1. Index on Sales.SalesOrderDetail (SalesOrderID, ProductID)

CREATE INDEX IX_SalesOrderDetail_SalesOrderID_ProductID

ON Sales.SalesOrderDetail (SalesOrderID, ProductID);

-- 2. Index on Sales.SalesOrderHeader (SalesOrderID, SalesPersonID, OrderDate)

CREATE INDEX IX_SalesOrderHeader_SalesOrderID_SalesPersonID_OrderDate

ON Sales.SalesOrderHeader (SalesOrderID, SalesPersonID, OrderDate);

-- 3. Index on Sales.SalesPerson (BusinessEntityID)

CREATE INDEX IX_SalesPerson_BusinessEntityID

ON Sales.SalesPerson (BusinessEntityID);

-- 4. Index on Production.Product (ProductID)

CREATE INDEX IX_Product_ProductID

ON Production.Product (ProductID);

-- 5. Index on Production.Product (Name) for the LIKE clause

CREATE INDEX IX_Product_Name

ON Production.Product (Name);

-- Optional Full-Text Index for the LIKE '%bike%' condition:

-- Create a Full-Text Catalog (if you don't have one already)

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

-- Create a Full-Text Index on the Product Name column

CREATE FULLTEXT INDEX ON Production.Product(Name)

KEY INDEX PK_Product_ProductID -- Assuming there is a primary key on ProductID

ON ftCatalog;

Testing the code with SQL Expert

I applied all the changes, and the query, which took 12 seconds with the indexes and the improved code, takes 1 second.

So far, it is OK. Does it create too many indexes?

For this query, it seems reasonable, but we must be aware that the solution to improve the performance is not always an index. For more information about indexes, please check our related articles:

• An Introduction to SQL Server Indexes: Stairway to SQL Server Indexes Level 1
• Index Internal Structure in SQL Server: Stairway to SQL Server Indexes Level 10
• Insert, Update, and Delete Indexes: Stairway to SQL Server Indexes Level 13

Suggest a database structure with SQL Expert

Another feature of the SQL Expert is to suggest a database structure. The following example shows how to create a database structure. Let's take a look to the example:

Me: Can you suggest a database structure for an app for inventories in a drugstore?

SQL Expert: Certainly! Below is a suggested database structure for a drugstore inventory app. This schema is designed to track products, suppliers, sales, stock levels, and other relevant data for inventory management.

Entities and Tables:

Products

This table stores all the details about the products available in the drugstore.

CREATE TABLE Products (

product_id INT PRIMARY KEY,

name VARCHAR(255) NOT NULL,

description TEXT,

category VARCHAR(100),

price DECIMAL(10, 2) NOT NULL,

sku VARCHAR(100) UNIQUE NOT NULL,

manufacturer_id INT, -- Can be a foreign key from the Manufacturers table

stock_quantity INT DEFAULT 0,

reorder_level INT DEFAULT 5, -- Minimum stock level to trigger a reorder

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

Manufacturers

This table stores the manufacturers' details for tracking where products come from.

CREATE TABLE Manufacturers (

manufacturer_id INT PRIMARY KEY,

name VARCHAR(255) NOT NULL,

contact_name VARCHAR(255),

contact_email VARCHAR(100),

contact_phone VARCHAR(20),

address TEXT

);

Suppliers

Suppliers provide products to the drugstore. This table tracks the suppliers' contact information.

CREATE TABLE Suppliers (

supplier_id INT PRIMARY KEY,

name VARCHAR(255) NOT NULL,

contact_name VARCHAR(255),

contact_email VARCHAR(100),

contact_phone VARCHAR(20),

address TEXT

);

Purchases

This table records each purchase (or restocking) of products from suppliers.

CREATE TABLE Purchases (

purchase_id INT PRIMARY KEY,

product_id INT,

supplier_id INT,

purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

quantity INT NOT NULL,

price DECIMAL(10, 2) NOT NULL,

total DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * price) STORED,

FOREIGN KEY (product_id) REFERENCES Products(product_id),

FOREIGN KEY (supplier_id) REFERENCES Suppliers(supplier_id)

);

Sales

This table tracks sales transactions. Each sale can involve multiple products.

CREATE TABLE Sales (

sale_id INT PRIMARY KEY,

sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

total DECIMAL(10, 2) NOT NULL,

customer_name VARCHAR(255),

customer_phone VARCHAR(20),

customer_address TEXT

);

Sale_Items

This table tracks which products were sold in each sale.

CREATE TABLE Sale_Items (

sale_item_id INT PRIMARY KEY,

sale_id INT,

product_id INT,

quantity INT NOT NULL,

price DECIMAL(10, 2) NOT NULL,

total DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * price) STORED,

FOREIGN KEY (sale_id) REFERENCES Sales(sale_id),

FOREIGN KEY (product_id) REFERENCES Products(product_id)

);

Stock_Adjustments

This table tracks any adjustments to stock (e.g., damaged goods, stocktaking results).

CREATE TABLE Stock_Adjustments (

adjustment_id INT PRIMARY KEY,

product_id INT,

adjustment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

quantity INT NOT NULL,

adjustment_type VARCHAR(50), -- e.g., 'stocktake', 'damaged', 'manual'

reason TEXT,

FOREIGN KEY (product_id) REFERENCES Products(product_id)

);

Users (Optional, if managing user roles is needed)

This table stores information about employees or users of the system who can perform various actions.

CREATE TABLE Users (

user_id INT PRIMARY KEY,

username VARCHAR(100) UNIQUE NOT NULL,

password_hash VARCHAR(255) NOT NULL,

full_name VARCHAR(255),

email VARCHAR(100),

role VARCHAR(50), -- e.g., 'admin', 'cashier', 'manager'

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

Indexes and Optimizations:

1. Indexes - You may want to create indexes on frequently searched columns such as sku, product_id, supplier_id, sale_id, and purchase_id for faster queries.

CREATE INDEX idx_products_sku ON Products(sku);

CREATE INDEX idx_sales_date ON Sales(sale_date);

CREATE INDEX idx_purchases_date ON Purchases(purchase_date);

Triggers for Stock Updates

A trigger can automatically update stock quantities when sales are made or new stock is purchased. Example for updating stock after a sale:

CREATE TRIGGER update_stock_after_sale

AFTER INSERT ON Sale_Items

FOR EACH ROW

BEGIN

UPDATE Products

SET stock_quantity = stock_quantity - NEW.quantity

WHERE product_id = NEW.product_id;

END;

You could have similar triggers for stock updates on purchases or adjustments.

Relationships and Constraints:

• Products → Manufacturers: Many products can come from one manufacturer (foreign key).
• Products → Suppliers: Products can be associated with one or more suppliers for restocking.
• Sales → Customers: If customer information is collected during a sale, it can be stored in the Sales table.
• Stock Adjustments: These keep track of any manual changes in stock quantities.

This schema covers key areas like product tracking, purchasing, sales, and stock adjustments, and is flexible for scaling or adding more features, such as barcode scanning, customer management, or order history.

Me: As you can see, it provides a lot of suggestions, explanations, and code to start creating your tables, indexes, constraints, etc.

Generate a file with the code in SQL Expert

This time, I will ask the SQL Expert to generate a file with all the previous code. This is useful to compile all the code to create tables, relationships, constraints, etc.

Me: Can you create a SQL file with all the previous code?

SQL Expert: The SQL schema file has been created. You can download it using the link below: Download drugstore_inventory_schema.sql

How to solve errors in SQL Server

SQL Expert helps to solve errors in the code. In this section, we will explain how to work with it.

Me: Please help me fix this error:

Msg 102, Level 15, State 1, Line 17

Incorrect syntax near ','.

This is the code:

SELECT

p.ProductID,

p.Name AS ProductName,

s.BusinessEntityID AS SalesPersonName,

od.OrderQty,

od.UnitPrice,

od.LineTotal

FROM

Sales.SalesOrderDetail od

JOIN

Sales.SalesOrderHeader soh ON soh.SalesOrderID = od.SalesOrderID

JOIN

Sales.SalesPerson s ON s.BusinessEntityID = soh.SalesPersonID

JOIN

Production.Product p ON p.ProductID = od.ProductID

WHERE

soh.OrderDate BETWEEN '2012-01-01', '2022-12-31'

AND od.OrderQty > 10

AND p.Name LIKE '%bike%' -- This condition could be optimized with full-text search if possible

AND s.BusinessEntityID IS NOT NULL

ORDER BY

soh.OrderDate;

SQL Expert:

The error you're seeing is due to incorrect syntax in the WHERE clause, specifically this part:

WHERE

soh.OrderDate BETWEEN '2012-01-01', '2022-12-31'

The BETWEEN operator should use AND to specify the range, not a comma. Here's the corrected version of that line:

soh.OrderDate BETWEEN '2012-01-01' AND '2022-12-31'

Here's the full corrected query:

SELECT

p.ProductID,

p.Name AS ProductName,

s.BusinessEntityID AS SalesPersonName,

od.OrderQty,

od.UnitPrice,

od.LineTotal

FROM

Sales.SalesOrderDetail od

JOIN

Sales.SalesOrderHeader soh ON soh.SalesOrderID = od.SalesOrderID

JOIN

Sales.SalesPerson s ON s.BusinessEntityID = soh.SalesPersonID

JOIN

Production.Product p ON p.ProductID = od.ProductID

WHERE

soh.OrderDate BETWEEN '2012-01-01' AND '2022-12-31'

AND od.OrderQty > 10

AND p.Name LIKE '%bike%' -- Consider using full-text search if available

AND s.BusinessEntityID IS NOT NULL

ORDER BY

soh.OrderDate;

Let me know if you'd like help optimizing this further.

Conclusion

In this article, we learned how to use the SQL Expert. This GPT is a specialized version of ChatGPT included in the paid version. This GPT provides extra information and more details than the generic GPT.