[go: up one dir, main page]
Scribd
Upload
13 views10 pages

SQL Optimizations

The document outlines eight SQL optimization techniques to improve query performance. Techniques include using MAX instead of RANK, EXISTS instead of IN, avoiding OR in WHERE clauses, and using JOINs instead of subqueries. Additionally, it emphasizes the importance of indexing frequently used columns and selecting the attribute with the highest number of unique values for GROUP BY clauses.

Uploaded by

ashish4biradar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
13 views10 pages

SQL Optimizations

The document outlines eight SQL optimization techniques to improve query performance. Techniques include using MAX instead of RANK, EXISTS instead of IN, avoiding OR in WHERE clauses, and using JOINs instead of subqueries. Additionally, it emphasizes the importance of indexing frequently used columns and selecting the attribute with the highest number of unique values for GROUP BY clauses.

Uploaded by

ashish4biradar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 10

8 SQL Optimization

Techniques

Abhishek Agrawal
Azure Data Engineer
#1
Use MAX Instead of RANK

SELECT ID, Name, RANK() OVER (ORDER BY Salary DESC) AS


SalaryRank
FROM Employees
WHERE SalaryRank = 1;

SELECT ID, Name, MAX(Salary) AS MaxSalary


FROM Employees;

Abhishek Agrawal | Azure Data Engineer


#2
Use EXISTS Instead of IN

SELECT Name FROM Customers WHERE ID IN (SELECT


CustomerID FROM Orders);

SELECT Name FROM Customers WHERE EXISTS (SELECT 1


FROM Orders WHERE Orders.CustomerID = Customers.ID);

Abhishek Agrawal | Azure Data Engineer


#3
Avoid OR in WHERE Clause

SELECT * FROM Orders WHERE Status = 'Pending' OR Status


= 'Processing';

SELECT * FROM Orders WHERE Status IN ('Pending',


'Processing');

Abhishek Agrawal | Azure Data Engineer


#4
Use WHERE Instead of HAVING

SELECT City, COUNT(*) FROM Customers GROUP BY City


HAVING City = 'Berlin';

SELECT City, COUNT(*) FROM Customers WHERE City =


'Berlin' GROUP BY City;

Abhishek Agrawal | Azure Data Engineer


#5
Joining products (large), orders (medium),
and customers (small)

SELECT *
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN products ON orders.product_id = products.product_id;

SELECT *
FROM products
JOIN orders ON products.product_id = orders.product_id
JOIN customers ON orders.customer_id = customers.customer_id;

Abhishek Agrawal | Azure Data Engineer


#6
Use JOIN Instead of Subqueries

SELECT Name
FROM Customers
WHERE ID IN (SELECT CustomerID FROM Orders WHERE
OrderDate > '2023-01-01');

SELECT DISTINCT Customers.Name


FROM Customers
JOIN Orders ON Customers.ID = Orders.CustomerID
WHERE Orders.OrderDate > '2023-01-01';

Abhishek Agrawal | Azure Data Engineer


#7
Use the attribute or column with the highest
number of unique values when applying a
"GROUP BY" clause.

SELECT SELECT
country, country,
city, city,
customer_id, customer_id,
COUNT(order_id) AS TotalOrders COUNT(order_id) AS TotalOrders
FROM FROM
orders orders
GROUP BY GROUP BY
country, city, customer_id; customer_id, country, city;

Abhishek Agrawal | Azure Data Engineer


#8
Index Frequently Used Columns

SELECT *
FROM Products
WHERE ProductName = 'Widget';

CREATE INDEX idx_product_name ON


Products(ProductName);

SELECT *
FROM Products
WHERE ProductName = 'Widget';

Abhishek Agrawal | Azure Data Engineer


Follow for more
content like this

Abhishek Agrawal
Azure Data Engineer

You might also like