Top 20 SQL Performance Tuning New Tips

SQL performance tuning is a BIG Topic on Databases, and that is not theoretical all the time. It’s a practical thing that must be handled case by case. But here I try to point out some of the key points that can change the performance overall.

I would like to highlight these improvements not to make all the time, but according to the key things below, performance may change. Therefore, you must test in a real environment with an actual dataset.

• Table data load
• Hardware configuration
• Spread of indexes
• Data Mapping
• Table Mapping

SQL performance tuning depends on factors such as indexing, query rewriting, efficient joining, filtering strategies, and hardware configuration. To reduce execution time and resource usage, check the query with tools like explain plan, profiling, and reports like EWR.

SQL performance tuning optimization not only speeds up individual queries but also improves the overall application.  Tuning is a never-ending process with database updates and hardware improvements.

1️⃣ Indexing Optimization

Example

Create Index: CREATE INDEX idx_users_email ON users(email);

Use Index: SELECT * FROM users WHERE email = '[email protected]';

Explanation
Indexes are big topic on database. Find index topic on ennicode.com. indexes store a sorted structure of column values, allowing the database to quickly locate rows without scanning the entire table.

Once query found a column that index available database uses the index to jump directly to the matching rows instead of doing a full table scan.

Why It Improves Performance
For large tables, without indexes, queries that filter on columns will always require a full table scan. It took lot of disk I/O and CPU usage, slowing down performance. Index scans fast compared to full table scans for big tables.

Note: Small tables no need indexes because full table scan can faster than index scan for small tables

Taka aways

1. Create an index after analyse Explain plan on the real table
2. Prefer to create an index on BIG tables

2️⃣ Avoid Using SELECT *

Example

--
❌ Bad
SELECT * FROM users;
✔ Good
SELECT user_id, name FROM users;
--

Explanation
IF you are a beginner, you know SELECT * retrieves all columns. This increases the amount of data read from disk, which also needs to be sent over the network, and finally stores that data in memory.

Selecting only the required columns helps to improve code readability and makes queries cleaner, and saves the I/O calls from the database.

Why It Improves Performance
Fetching required columns reduces disk I/O and memory usage. This improves memory usage. On large tables, SELECT * increases the data is processing time and I/O time.

Take aways

1. Select the required columns on Queries

3️⃣ JOIN Optimization

Example

--
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
--

Explanation
SQL JOINs are used to combine rows from multiple tables. Correct joins type INNER, LEFT, RIGHT etc. change the performance. Make certain join columns are indexed, and filter the dataset before joining them. Poorly joins generate large result sets that are not valid, causing high memory usage and slow performance.

Why It Improves Performance
SQL Performance Tuning joins need to reduce unnecessary row combinations to improve performance. Properly optimized joins and filter unnecessary records early, even when tables contain millions of rows.

Take aways

1. Use Proper Join Type
2. Filter Records Before Join
3. Indexed Columns improve joining time

4️⃣ WHERE Clause Optimization

Example

--
❌ Bad
SELECT * FROM orders WHERE YEAR(order_date) = 2025;
✔ Good
SELECT * FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01';

--

ExplanationThe
WHERE clause is doing the filtration. Using functions on filter columns that prevent the indexes. Writing conditions as a range-based filter allows the database to perform index range scans efficiently. Especially, large tables with full table scans are costly.

Why It Improves Performance
Range-based conditions enable the use of indexes, which decreases CPU usage and I/O. If you really need a function to create an index as function base index.

Take aways

1. Don’t use functions on columns
2. Create function base index if really needed

5️⃣ EXISTS vs IN

Example

--
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);

--

Explanation
EXISTS is better when it checks at least one row that meets the condition. Once meets the condition It stops searching. Using IN with a subquery can force the database to complete the full list of matches.

Why It Improves Performance
EXISTS reduces processing time for meeting the first match. IN may process until all rows are consumed and consume more memory, cpu and IO.

Take aways

1. Use EXISTS rather IN to check at least one row

6️⃣ UNION vs UNION ALL

Example

--
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

--

Explanation
If you don’t care about duplicates, use UNION ALL, which simply combines results without checking for duplicates. UNION removes duplicates, which requires sorting and table time for that. UNION ALL join it faster for two datasets.

Why It Improves Performance
Avoiding duplicate use of CPU and memory usage. If duplicates are not a concern, or if you are confident there are no duplicates, then use UNION ALL, which can be faster than UNION.

Take aways

1. UNION ALL is faster than UNION if duplicates are not a concern

7️⃣ DISTINCT vs GROUP BY

Example

SELECT customer_id FROM orders GROUP BY customer_id;

Explanation
DISTINCT as you know, removes duplicates, which may involve sorting large datasets. GROUP BY groups rows during retrieval, and sometimes the database uses indexes.

Why It Improves Performance
GROUP BY can use indexes to aggregate data efficiently, reducing the amount of data that needs sorting and improving speed over DISTINCT in large tables.

Take aways

DISTINCT:

• Simple queries.
• Low cardinality column
• Distinct use with Single-column

GROUP BY:

• More complex queries
• Use with other aggregate functions like AVG, COUNT, or others.

8️⃣ Aggregation Optimization

Example

--
SELECT customer_id, COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id;

--

Explanation
Use indexes on GROUP BY columns to avoid full table scans when needed. Use COUNT(1) or COUNT(*), not COUNT(column) When NULLs Exist. HAVING filters after aggregation, but WHERE reduces rows before grouping. Avoid Functions on Aggregated Columns prevent index usage.

Why It Improves Performance
Filtered aggregation helps to decrease the number of rows scanned for aggregation. then onlythe required use for Aggregation that will minimize memory and CPU usage.

Take aways

1. Filter data early using WHERE instead of HAVING
   1. Use COUNT(*) instead of COUNT(column)

9️⃣ Pagination OFFSET OptimizationExample

--
❌ Slow
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100000;
✔ Fast
SELECT * FROM users
WHERE id > 100000
ORDER BY id
LIMIT 10;

--

Explanation
OFFSET scans all skipped rows internally therefore, if you could use a WHERE condition. Pagination is important rather than using all rows directly.

Why It Improves Performance
OFFSET scans all skipped rows internally. But anyway, pagination is a good thing to use. Because no one uses all the data at once.

Take aways

Use one of the SQL limit rows If possible

• ROWNUM
• FETCH FIRST
• FETCH NEXT and OFFSET
• LIMIT
• TOP
• ROW_NUMBER

🔟 Execution Plan Analysis

Example

EXPLAIN SELECT * FROM orders WHERE customer_id = 10;

Explanation
Execution plans are the BEST way to check current execution behavior executes a query, including index usage, join order, and other information. This greatly helps detect full table scans, unnecessary loops, and IO/CPU cost that impact SQL Performance Tuning.

Why It Improves Performance
By analyzing execution plans, developers can identify required indexes or remove indexes or inefficient joins, and gather hundreds of pieces of information about queries.

Taka aways

Run the execution plan on the REAL environment before changing anything.

1️⃣1️⃣ Avoid Full Table Scans

Example

SELECT * FROM users WHERE username = ‘john’;

Explanation
Full Table Scans are not bad, but can be a problem. Without an index, the database reads every row sequentially, which is slow for large tables.

Why It Improves Performance
Indexes help to avoid full table scans. Index can slow down the DML operation. Indexes reduce a lot of IO calls.

Take aways

Full Table Scans  problem in Heavy Tables not for small tables

1️⃣2️⃣ Handling NULL Values

Example

SELECT * FROM orders WHERE shipped_date IS NULL;

Explanation
NULL value handling can prevent index usage. Better to use  Queries with IS NULL or IS NOT NULL for a better execution plan.

Why It Improves Performance
Correct NULL handling ensures indexes are usable and filtering is accurate, avoiding unnecessary full scans.

Take aways

1     Use Default Values If possible

1. Use IS NULL rather = NULL

1️⃣3️⃣ Index Types –

Example

CREATE BITMAP INDEX idx_order_status ON orders(status);

1️⃣4️⃣ CTE vs Subquery

Example

--
WITH SalesSummary AS (
  SELECT customer_id, SUM(amount) total
  FROM sales
  GROUP BY customer_id
)
SELECT * FROM SalesSummary;

--

Explanation
CTEs improve readability and modularity. SQL Performance Tuning: How They Impact CTEs are executed only once. CTE creates intermediate results that can be reused later in a query.

Why It Improves Performance
While CTEs improve clarity, careful use avoids materializing large middle datasets. Properly used, they simplify query optimization and reduce redundant calculations.

1️⃣5️⃣ Temporary Tables & Materialized Views

Example

--
CREATE TEMP TABLE temp_orders AS
SELECT * FROM orders WHERE order_date >= '2025-01-01';

--

Explanation
Temporary tables store intermediate results to prevent precomputations. Materialized views store precomputed data for frequently run queries.

Why It Improves Performance
Materialized views load into memory and are not suitable for frequently updated records. Most effective static tables.

1️⃣6️⃣ Data Type Optimization

Example

user_id INT — instead of BIGINT if not needed

Explanation
Choosing the smallest data type is better when memory allocations. Smaller rows allow more data in memory and caches.

Why It Improves Performance
This is reducing I/O and memory consumption, improving query speed. Fewer bytes per row means more rows per data block,

1️⃣7️⃣ Batch Processing / Bulk Collect & FORALL

Oracle Example

--
DECLARE
  TYPE t_ids IS TABLE OF orders.order_id%TYPE;
  l_ids t_ids;
BEGIN
  SELECT order_id BULK COLLECT INTO l_ids FROM orders WHERE status = 'NEW';
  
  FORALL i IN l_ids.FIRST..l_ids.LAST
    UPDATE orders SET status = 'PROCESSED' WHERE order_id = l_ids(i);
END;

--

Explanation
FORALL performs bulk DML operations, and BULK COLLECT retrieves multiple rows at once. Single I/O for multiple records is fast but multiple rows individually in a loop are slow.

Why It Improves Performance
Context switches between PL/SQL and SQL engines take much time, but BULK COLLECT and FORALL save CPU cycles and improve throughput significantly.

1️⃣8️⃣ EWR / AWR Reports

Example

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot();

Explanation
EWR (ADDM/AWR) reports are the best way to identify a full report of executed queries. The report shows a lot of information like

• Most time-consuming queries
• Time per one circle
• Time for full execution for query
• CPU / IO Cost

Why It Improves Performance
The report generates a lot of information about queries and finds slow queries. That helps to improve SQL Performance Tuning

1️⃣9️⃣ Profiling Queries

Example

--
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM orders WHERE customer_id = 10;

--

Explanation
This helps to find the most time-consuming queries. Profiling shows actual resource usage for queries, including logical reads, physical reads, and CPU time.

Why It Improves Performance
Helps developers identify inefficient queries and optimize SQL by reducing unnecessary scans or joins.

2️0⃣ Hardware & Configuration Tuning

Example
Increase buffer/cache size, CPU cores, or use SSD storage.

Explanation
Database SQL Performance Tuning depends on hardware resources and configuration. Memory caches frequently accessed data, CPUs process queries faster, and SSDs reduce disk latency.

Why It Improves Performance
More memory reduces disk reads, faster CPUs reduce query processing time, and SSDs improve I/O-bound workloads.

10 SQL Performance Interview Q&A

These questions are already explained in the top section. You may be able to answer these questions if you are in an interview next time. These SQL Performance Tuning questions may test your knowledge about database experience

1️⃣ Q: Why is using SELECT * bad?

A: Fetches unnecessary columns, wastes memory, network, IO, and CPU resources.

2️⃣ Q: When should you use EXISTS instead of IN?

A: Use EXISTS when checking for the existence of rows; it stops after the first match.

3️⃣ Q: What is keyset pagination?

A: Pagination using a WHERE clause (like id > last_id) to avoid OFFSET scanning.

4️⃣ Q: What is a CTE and why use it?

A: Common Table Expression – improves query readability and allows reuse of intermediate results.

5️⃣ Q: Why are Bulk Collect and FORALL faster than row-by-row loops?

A: Reduces SQL/PLSQL context switches and executes operations in batches.

6️⃣ Q: What is the difference between UNION and UNION ALL?

A: UNION removes duplicates (adds sorting), UNION ALL keeps all rows (faster).

7️⃣ Q: How does indexing improve SQL Performance Tuning?

A: Reduces full table scans by allowing direct lookup of rows.

8️⃣ Q: What is a function-based index?

A: An index built on an expression, allowing queries that use that expression to leverage the index.

9️⃣ Q: How can EWR/AWR reports help?

A: Identify slow queries, high I/O operations, and DB bottlenecks.

🔟 Q: Why is partitioning important for large tables?

A: Limits scanned data to relevant partitions, improving query performance.