{"id":4344,"date":"2025-06-18T09:30:00","date_gmt":"2025-06-18T09:30:00","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4344"},"modified":"2025-06-20T06:59:52","modified_gmt":"2025-06-20T06:59:52","slug":"what-is-a-bad-sql-query-and-why-it-slows-everything-down","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/performance-tuning\/what-is-a-bad-sql-query-and-why-it-slows-everything-down\/","title":{"rendered":"What Is a Bad SQL Query? (And Why It Slows Everything Down)"},"content":{"rendered":"\n

The Hidden Cost of Queries That \u201cJust Work\u201d<\/h2>\n\n\n\n

Your SQL returns the correct result. So it\u2019s fine… right?<\/p>\n\n\n\n

Not even close.<\/strong><\/p>\n\n\n\n

In real-world systems, a SQL query that works<\/em> but isn\u2019t tuned<\/em> is one of the biggest hidden risks. It may work fine during development, but once real data hits \u2014 millions of rows<\/strong>, multiple joins, user concurrency \u2014 everything slows down.<\/p>\n\n\n\n

If you\u2019re not thinking about performance while writing SQL, you\u2019re building time bombs.Let\u2019s look at a single query that does almost all<\/strong> of these wrong.<\/p>\n\n\n\n

Why SQL Performance Tuning Matters<\/h3>\n\n\n\n

Whether you’re a developer, architect, or DBA, SQL tuning isn’t optional. It’s your insurance against scalability disasters.<\/p>\n\n\n\n

Here\u2019s what well-tuned SQL delivers:<\/p>\n\n\n\n

    \n
  • Faster user experience<\/strong> (especially in web and mobile apps)<\/li>\n\n\n\n
  • Lower cloud\/database costs<\/strong> (by reducing CPU, I\/O, and memory usage)<\/li>\n\n\n\n
  • Efficient index usage<\/strong> (less full scans, more targeted lookups)<\/li>\n\n\n\n
  • More secure and stable systems<\/strong> (less resource contention)<\/li>\n\n\n\n
  • Peace of mind in production<\/strong> (no random query meltdowns)<\/li>\n<\/ul>\n\n\n\n

    What Makes a SQL Query \u201cBad\u201d?<\/h3>\n\n\n\n

    A bad SQL query<\/strong> doesn\u2019t throw an error \u2014 it does something worse:<\/p>\n\n\n\n

    \n

    It silently kills performance<\/strong>, wastes resources<\/strong>, and slows down your entire system<\/strong> without warning.<\/p>\n<\/blockquote>\n\n\n\n

    These queries might:<\/p>\n\n\n\n

      \n
    • Work fine during development<\/li>\n\n\n\n
    • Return the correct result<\/li>\n\n\n\n
    • Pass testing with small datasets<\/li>\n<\/ul>\n\n\n\n

      But once they hit real-world volumes, they:<\/p>\n\n\n\n

        \n
      • Trigger full table scans<\/strong><\/li>\n\n\n\n
      • Bypass indexes<\/strong><\/li>\n\n\n\n
      • Cause row-by-row operations<\/strong> instead of set-based logic<\/li>\n\n\n\n
      • Block concurrent sessions<\/strong><\/li>\n\n\n\n
      • Force the optimizer to guess wrong due to poor statistics or structure<\/strong><\/li>\n<\/ul>\n\n\n\n

        In short:<\/p>\n\n\n\n

        \n

        A bad SQL query is one that doesn\u2019t scale.<\/strong><\/p>\n<\/blockquote>\n\n\n\n

        Let\u2019s break down what makes a query bad \u2014 and how to fix it \u2014 with real-world examples.<\/p>\n\n\n\n

        Let\u2019s look at a single query that does almost all<\/strong> of these wrong.<\/p>\n\n\n\n

        Bad SQL Query Example<\/h3>\n\n\n\n
        -- \u274c BAD QUERY: Loaded with performance issues\nSELECT DISTINCT *\nFROM orders o\nJOIN customers c ON TO_CHAR(o.customer_id) = c.customer_id     -- \u274c Implicit conversion\nJOIN order_items oi ON o.order_id = oi.order_id(+)             -- \u274c Deprecated join syntax\nLEFT JOIN inventory inv ON oi.product_id = inv.product_id      -- \u274c Unfiltered join\nWHERE o.status = 'completed'\n  AND UPPER(o.region) = 'WEST'                                 -- \u274c Function on column\n  AND o.order_date + 1 >= SYSDATE                              -- \u274c Arithmetic on column\n  AND o.total_amount > 1000\n  AND NVL(o.promo_code, 'N\/A') = 'SUMMER50'                    -- \u274c Function disables index\n  AND oi.product_id IN (\n        SELECT p.product_id \n        FROM products p \n        WHERE p.category LIKE '%Elect%'                        -- \u274c Leading wildcard\n          AND p.status = 'active'\n    )\n  AND oi.price * oi.quantity > 500                             -- \u274c Expression disables index\n  AND c.country = 'US'\n  AND ROWNUM < 100                                             -- \u274c Pagination before sorting\nORDER BY o.order_date DESC;                                    -- \u274c ORDER BY after ROWNUM<\/code><\/pre>\n\n\n\n
        \u2705 The Optimized Version \u2014 Clean, Tuned, and Scalable<\/h3>\n\n\n\n
        Here\u2019s the tuned version, using best practices<\/strong> for Oracle performance:<\/p>\n\n\n\n
        -- \u2705 GOOD QUERY: Follows tuning principles\nSELECT o.order_id, o.customer_id, o.order_date, o.status, o.region, o.total_amount, oi.product_id\nFROM orders o\nJOIN customers c ON o.customer_id = c.customer_id\nLEFT JOIN order_items oi ON o.order_id = oi.order_id\nLEFT JOIN inventory inv ON oi.product_id = inv.product_id AND inv.stock > 0  -- \u2705 Filter at join level\nWHERE o.status = 'completed'\n  AND o.region = 'WEST'                                -- \u2705 Sargable condition\n  AND o.order_date >= TRUNC(SYSDATE) - 1               -- \u2705 Index-compatible date filter\n  AND o.total_amount > 1000\n  AND o.promo_code = 'SUMMER50'                        -- \u2705 Avoids NVL\n  AND EXISTS (                                         -- \u2705 EXISTS > IN for large subqueries\n        SELECT 1 \n        FROM products p \n        WHERE p.product_id = oi.product_id \n          AND p.category LIKE 'Elect%'                 -- \u2705 No leading %\n          AND p.status = 'active'\n    )\n  AND oi.price * oi.quantity > 500                     -- \u2705 Consider function-based index\n  AND c.country = 'US'\nORDER BY o.order_date DESC\nFETCH FIRST 100 ROWS ONLY;                             -- \u2705 Proper pagination<\/code><\/pre>\n\n\n\n
        \u2705 Checklist for Writing High-Performance SQL Queries in Oracle<\/strong><\/h3>\n\n\n\n
        1. Index Awareness<\/strong><\/h3>\n\n\n\n
          \n
        • \u2705 Write \u201csargable\u201d queries<\/strong> \u2014 avoid functions on indexed columns (UPPER(col)<\/code>, TO_CHAR(col)<\/code>, col + 1<\/code>, etc.)<\/li>\n\n\n\n
        • \u2705 Avoid expressions on indexed columns<\/strong> in WHERE<\/code> or JOIN<\/code> clauses<\/li>\n\n\n\n
        • \u2705 Use equality or range comparisons<\/strong> (=<\/code>, <<\/code>, ><\/code>, BETWEEN<\/code>) \u2014 they work well with B-tree indexes<\/li>\n\n\n\n
        • \u2705 Understand which indexes exist<\/strong> (and how they\u2019re used in your execution plan)<\/li>\n\n\n\n
        • \u2705 Use function-based indexes<\/strong> if functions on columns are unavoidable<\/li>\n<\/ul>\n\n\n\n
          2. Joins & Subqueries<\/strong><\/h3>\n\n\n\n
            \n
          • \u2705 Choose the right join type<\/strong> (INNER<\/code>, LEFT<\/code>, SEMI<\/code>, ANTI<\/code>, etc.)<\/li>\n\n\n\n
          • \u2705 Filter as early as possible<\/strong> in joins (add join predicates carefully)<\/li>\n\n\n\n
          • \u2705 Avoid Cartesian joins<\/strong> unless intentional<\/li>\n\n\n\n
          • \u2705 Use EXISTS<\/code> instead of IN<\/code><\/strong> for correlated subqueries (especially when inner table is large)<\/li>\n\n\n\n
          • \u2705 Avoid NOT IN<\/code> with NULL<\/code>s \u2014 use NOT EXISTS<\/code> or ANTI JOIN<\/code><\/li>\n\n\n\n
          • \u2705 Avoid joining unnecessary tables or views \u201cjust in case\u201d<\/li>\n<\/ul>\n\n\n\n
            3. WHERE Clause Best Practices<\/strong><\/h3>\n\n\n\n
              \n
            • \u2705 Avoid functions on column side<\/strong> of condition: WHERE TRUNC(order_date) = ...<\/code> \u2192 use BETWEEN<\/code> or >= TRUNC(...)<\/code><\/li>\n\n\n\n
            • \u2705 Avoid implicit data type conversions<\/strong>: WHERE id = '123'<\/code> (when id<\/code> is NUMBER<\/code>) \u2192 slows query<\/li>\n\n\n\n
            • \u2705 Use bind variables<\/strong> to prevent hard parsing & plan cache pollution<\/li>\n\n\n\n
            • \u2705 Use IS NULL<\/code> and IS NOT NULL<\/code> appropriately (NULL handling is tricky in Oracle)<\/li>\n<\/ul>\n\n\n\n
              4. Query Structure & Output<\/strong><\/h3>\n\n\n\n
                \n
              • \u2705 Avoid SELECT *<\/code><\/strong> \u2014 select only the columns you need<\/li>\n\n\n\n
              • \u2705 Avoid unnecessary DISTINCT<\/code><\/strong> \u2014 often used to mask join logic errors<\/li>\n\n\n\n
              • \u2705 Avoid nested views and inline views unless needed<\/strong><\/li>\n\n\n\n
              • \u2705 Use WITH<\/code> clause (CTEs) to simplify and clarify complex logic \u2014 but not blindly<\/li>\n<\/ul>\n\n\n\n
                5. Sorting, Pagination, and Aggregation<\/strong><\/h3>\n\n\n\n
                  \n
                • \u2705 Use ORDER BY<\/code> only when needed \u2014 it’s expensive!<\/li>\n\n\n\n
                • \u2705 Use FETCH FIRST N ROWS ONLY<\/code> instead of ROWNUM<\/code> for consistent pagination<\/li>\n\n\n\n
                • \u2705 Use GROUP BY<\/code> and HAVING<\/code> with care \u2014 can force sort or hash operations<\/li>\n\n\n\n
                • \u2705 For large result sets, avoid client-side sorting or filtering<\/li>\n<\/ul>\n\n\n\n
                  6. Plan Visibility & Testing<\/strong><\/h3>\n\n\n\n
                    \n
                  • \u2705 Always review the execution plan<\/strong> (EXPLAIN PLAN<\/code>, DBMS_XPLAN<\/code>, or SQL Monitor<\/code>)<\/li>\n\n\n\n
                  • \u2705 Use AUTOTRACE<\/code> or SQL Developer<\/code> to estimate cost and row counts<\/li>\n\n\n\n
                  • \u2705 Check cardinality estimates \u2014 Oracle can be wrong with skewed data<\/li>\n\n\n\n
                  • \u2705 Use STATS<\/code> and GATHER_STATS<\/code> properly \u2014 bad stats = bad plans<\/li>\n<\/ul>\n\n\n\n
                    7. Anti-Patterns to Avoid<\/strong><\/h3>\n\n\n\n
                    \u274c Anti-Pattern \/ Problem<\/strong><\/th>\ud83d\udca5 Impact<\/strong><\/th>\u2705 Better Practice<\/strong><\/th><\/tr><\/thead>
                    SELECT *<\/code><\/td>Increases I\/O and memory usage<\/td>Select only the columns you need<\/td><\/tr>
                    TO_CHAR(col) = '123'<\/code> or UPPER(col) = 'ABC'<\/code><\/td>Prevents index usage (non-sargable)<\/td>Use consistent data types and avoid functions on columns<\/td><\/tr>
                    NVL(col, 'X') = 'Y'<\/code> or COALESCE(col, ...)<\/code><\/td>Indexes are bypassed<\/td>Rewrite logic to avoid functions on indexed columns<\/td><\/tr>
                    IN (SELECT ...)<\/code> with large subqueries<\/td>Slow execution due to nested looping<\/td>Use EXISTS<\/code> or refactor into a join<\/td><\/tr>
                    LIKE '%term'<\/code><\/td>Index cannot be used<\/td>Avoid leading %<\/code>, or use full-text search<\/td><\/tr>
                    Implicit data type conversion (e.g., id = '123'<\/code> when id<\/code> is NUMBER)<\/td>Causes full table scans<\/td>Ensure column and filter value have the same data type<\/td><\/tr>
                    Arithmetic on columns (col + 1 >= value<\/code>)<\/td>Disables index range scan<\/td>Rewrite the logic to keep column side clean<\/td><\/tr>
                    ROWNUM < N<\/code> without ORDER BY<\/code><\/td>Unpredictable and inconsistent pagination<\/td>Use FETCH FIRST N ROWS ONLY<\/code> with a proper ORDER BY<\/code><\/td><\/tr>
                    Using DISTINCT<\/code> to fix duplicates<\/td>Adds sorting overhead, masks deeper issues<\/td>Fix join logic to avoid unnecessary duplication<\/td><\/tr>
                    Unfiltered or unnecessary joins<\/td>Brings in extra rows, increases temp usage<\/td>Filter early and avoid joining unused tables<\/td><\/tr>
                    Legacy outer joins using (+)<\/code><\/td>Can be confusing and error-prone<\/td>Use ANSI SQL (LEFT JOIN<\/code>, RIGHT JOIN<\/code>)<\/td><\/tr>
                    Blind use of views or nested views<\/td>Hidden performance costs<\/td>Understand and profile the underlying SQL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
                    \u2705 Golden Rules to Remember<\/strong><\/h3>\n\n\n\n
                      \n
                    • Write for the data volume of tomorrow<\/strong>, not today<\/li>\n\n\n\n
                    • You\u2019re writing instructions for the optimizer<\/strong>, not just pulling data<\/li>\n\n\n\n
                    • “It works” is not enough \u2014 it must scale<\/strong><\/li>\n\n\n\n
                    • Never assume Oracle will “figure it out”<\/strong> \u2014 guide it with good structure<\/li>\n<\/ul>\n\n\n\n
                      \ud83d\udccc Final Thoughts<\/h2>\n\n\n\n
                      \n
                      \u275d Good SQL is not about writing queries that work. It’s about writing queries that scale. \u275e<\/p>\n<\/blockquote>\n\n\n\n
                      Bad SQL doesn’t break your application \u2014 it slowly bleeds it.
                      By the time users complain, it’s already hurting your performance, your cost, and your reputation.<\/p>\n\n\n\n
                      By following the principles in this post, you\u2019ll move from just writing queries<\/em> to building data pipelines and applications that perform under pressure.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
                      The Hidden Cost of Queries That \u201cJust Work\u201d Your SQL returns the correct result. So it\u2019s fine… right? Not even close. In real-world systems, a SQL query that works but isn\u2019t tuned is one of the biggest hidden risks. It may work fine during development, but once real data hits \u2014 millions of rows, multiple […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,992],"class_list":["post-4344","cposts","type-cposts","status-publish","hentry","category-notes","category-performance-tuning"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4344","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4344"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4344"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}