SQL Tuning Tricks

SQL Tuning Tricks

SQL performance tuning is a critical skill for every database professional. Slow queries can cripple applications, frustrate users, and increase costs. In this article, you will learn five powerful SQL tuning strategies that optimize query performance by reducing unnecessary workload, improving filtering efficiency, and leveraging SQL features correctly.Additionally, you will discover common mistakes that prevent databases from using indexes effectively a major cause of slow queries. By understanding these pitfalls and their solutions, you’ll write SQL that not only runs faster but also scales better as data grows. Whether you’re a beginner or a seasoned developer, these practical tips will help you unlock your database’s full potential.

1. Combine Multiple Queries into One for Better Efficiency

Running multiple queries to fetch related data wastes resources and adds overhead. Instead, join related tables in a single query to reduce round-trips and let the database optimize execution.

Why it matters:
Multiple queries increase network calls and CPU usage. A well-formed join reduces the workload and speeds up the response time.

Example:
Instead of:

SELECT
        name
FROM
        products
WHERE
        product_id = 1;

---------------------------

SELECT
        type_name
FROM
        product_type
WHERE
        product_type_id = 1;

Use:

SELECT
        p.name,
        pt.type_name
FROM
        products p JOIN product_type pt ON p.product_type_id = pt.product_type_id
WHERE
        p.product_id = 1;

2. Always Reference Columns with Table Aliases

Ambiguous column references make it harder for the query optimizer to plan efficient execution. Explicitly prefix columns with table names or aliases to clarify which table the data comes from.

Why it matters:
Ambiguity causes the optimizer to scan more data than necessary, resulting in slower queries.

Example:
Avoid:

SELECT
        p.name      ,
        pt.type_name,
        description ,
        price
FROM
        products     p,
        product_type pt
WHERE
        p.product_type_id = pt.product_type_id
AND     p.product_id      = 1;

Prefer:

SELECT
        p.name       ,
        pt.type_name ,
        p.description,
        p.price
FROM
        products p JOIN product_type pt ON p.product_type_id = pt.product_type_id
WHERE
        p.product_id = 1;

3. Use WHERE Instead of HAVING When Possible

The HAVING clause filters records after aggregation, while WHERE filters before aggregation.

Why it matters:
Filtering early reduces the amount of data processed in aggregation, improving performance.

Example:
Instead of:

SELECT
        product_type_id,
        AVG(price)
FROM
        products
GROUP BY
        product_type_id HAVING product_type_id IN (1, 2);

Write:

SELECT
        product_type_id,
        AVG(price)
FROM
        products
WHERE
        product_type_id IN (1, 2)
GROUP BY
        product_type_id;

4. Replace DISTINCT with EXISTS for Duplicate Checks

DISTINCT removes duplicates by sorting and scanning the entire result set, which is expensive. EXISTS checks for the existence of related rows, often executing faster.

Why it matters:
 EXISTS allows the database to short-circuit processing and use indexes more efficiently.

Example:
 Avoid:

SELECT
        DISTINCT pr.product_id,
        pr.name
FROM
        products pr,
        purchase pu
WHERE
        pr.product_id = pu.product_id;

Use:

SELECT
        pr.product_id,
        pr.name
FROM
        products pr
WHERE
        EXISTS
        (
                SELECT
                        1
                FROM
                        purchase pu
                WHERE
                        pu.product_id = pr.product_id );

5. Prefer UNION Over OR in WHERE Clauses

OR conditions can cause the optimizer to generate complex plans and reduce index usage. Splitting the query into multiple parts with UNION often yields better execution plans.

Why it matters:
 Simpler queries are easier for the optimizer to handle, resulting in faster performance.

Example:
 Instead of:

SELECT
        ...
FROM
        ps_jrnl_header a
WHERE
        jrnl_hdr_status = 'E'
OR      EXISTS
        (
                SELECT
                        'x'
                FROM
                        ps_jrnl_header
                WHERE
                        business_unit_js = a.business_unit_js
                AND     journal_id       = a.journal_id
                AND     unpost_seq       = a.unpost_seq
                AND     jrnl_hdr_status  = 'E' );

Use:

SELECT
        ...
FROM
        ps_jrnl_header a
WHERE
        jrnl_hdr_status = 'E' UNION
SELECT
        ...
FROM
        ps_jrnl_header a,
        ps_jrnl_header b
WHERE
        a.business_unit_js = b.business_unit_js
AND     a.journal_id       = b.journal_id
AND     a.unpost_seq       = b.unpost_seq
AND     a.jrnl_hdr_status  = 'E'
AND     b.jrnl_hdr_status  != 'E';

Index Usage Pitfalls: What Slows Your Queries Down

Indexes are your best friends for fast data retrieval but only if used correctly. Here are common mistakes that prevent index usage and how to avoid them.

1. Avoid Using Functions on Indexed Columns

Functions like SUBSTR(), TRUNC(), or TO_CHAR() applied on indexed columns disable the index because the database must transform every value before comparing.

Bad:

WHERE SUBSTR(team_name, 1, 7) = 'FENERBAHCE'

Good:

WHERE team_name LIKE 'FENERBAHCE%'

2. Avoid Using Inequality Operators (!=, <>, NOT)

Indexes are optimized for equality and range searches (=, >, <). Using inequality operators forces full scans.

Bad:

WHERE account != 0

Good:

WHERE account > 0

3. Don’t Use Functions on Date Columns in WHERE Clauses

Functions like TRUNC() on dates prevent index usage.

Bad:

WHERE TRUNC(tur_date) = TRUNC(SYSDATE)

Good:

WHERE tur_date BETWEEN TRUNC(SYSDATE)
AND TRUNC(SYSDATE) + 0.99999

4. Avoid Concatenating Columns in WHERE Clause

Concatenating columns disables the index on individual columns.

Bad:

WHERE name || type = 'FENERBAHCE'

Good:

WHERE name = 'FENER'
AND type = 'BAHCE'

5. Avoid Using Columns on Both Sides of a Comparison (e.g., NVL)

This makes the optimizer unable to use indexes efficiently.

Bad:

WHERE account_name = NVL(:acc_name, account_name)

Good:

WHERE account_name LIKE NVL(:acc_name, '%')

6. Data Type Mismatches Disable Indexes

Comparing columns with different data types leads to implicit conversions and prevents index usage.

Bad:

WHERE type_c= 123  -- if teyp_c is VARCHAR

Good:

WHERE TO_NUMBER(type_c) = 123

Conclusion

SQL tuning isn’t just about writing queries that work and also it’s about writing queries that perform. By combining multiple queries, referencing columns explicitly, filtering early, and choosing the right SQL constructs, you enable the database optimizer to do its best work.