Interaction with AI #5

Trying to optimize a basic SQL query (A success story).

Previously, I’ve written about optimizing the a basic SQL query using GitHub Copilot and Claude 3.7 Sonnet. Here is the link:

It was an epic fail, especially since it was just SQL 101. I shouldn’t have spent that much time on one specific case. There are lots of query problems waiting in line to be solved.

Today, I’ve tried ChatGPT reasoning (with o3 model) and it was amazing. Let’s remember the SQL case:

,CASE WHEN CUSTOMER_NUM IS NOT NULL THEN
                CASE WHEN NVL(EBITDA_FACTOR,0) = 0  THEN '>20’
                        WHEN NVL(EBITDA_FACTOR,0) > 20 THEN '>20'
                        WHEN NVL(EBITDA_FACTOR,0) > 10 THEN '>10'
                        WHEN NVL(EBITDA_FACTOR,0) > 9  THEN '>9'
                        WHEN NVL(EBITDA_FACTOR,0) > 8  THEN '>8'
                        WHEN NVL(EBITDA_FACTOR,0) > 7  THEN '>7'
                        WHEN NVL(EBITDA_FACTOR,0) > 6  THEN '>6'
                        WHEN NVL(EBITDA_FACTOR,0) > 5  THEN '>5'
                        WHEN NVL(EBITDA_FACTOR,0) > 4  THEN '>4'
                        WHEN NVL(EBITDA_FACTOR,0) > 3  THEN '>3'
                        WHEN NVL(EBITDA_FACTOR,0) > 2  THEN '>2'
                        WHEN NVL(EBITDA_FACTOR,0) > 1  THEN '>1'
                        ELSE '<1'
                END
END AS DEBT_EBITDA_RATIO

It thought 1 minute and 3 seconds. For a computer it must be forever 🙂

At this point, the result is still wrong because second when block use the EBITDA_FACTOR as is. And “>10”, “>20” conditions are not handled properly.

Let’s continue.

This is more promising. Because previously other model couldn’t differentiate that “>20” is another condition to be handled. But “<1” ,”>10” conditions are still missing.

Ignore the CROSS APPLY part, it will be explained later but it is irrelevant.

And, here it comes…

Code format:

,CASE
    WHEN customer_num IS NULL THEN NULL                            -- keep outer check
    ELSE
      CASE
        /* get the value once */
        WHEN nvl(trim(ebitda_factor),0) IN (0)          -- special rule from the original
             OR nvl(trim(ebitda_factor),0) > 20 THEN '>20'
        WHEN nvl(trim(ebitda_factor),0) > 10           THEN '>10'
        WHEN nvl(trim(ebitda_factor),0) >  1           THEN
             '>'||TRUNC(nvl(trim(ebitda_factor),0))    -- handles >2 … >9
        ELSE '<1'
      END
  END AS debt_ebitda_ratio

This is proper handling. It thought and realized “>20”, “>10” are the conditions which should be handled separately. Previously it used FLOOR function, now it is TRUNC. For this case FLOOR and TRUNC create the same result for a value of 3.456. I had a huge smile on my face when I double check the results with actual data.

Explains the process.

Its second suggestion uses CROSS APPLY and DUAL table. Even tough it created the same correct result, it creates unnecessary complexity in my opinion. And it is not always possible to touch to FROM clause as it suggested.

Some performance notes.

And this comment made my day. It said “Sanity check.” Maybe I had made a typo or a logic mistake. Of course not — but it was very considerate of it to ask

Yes, I know, it took too much time, too many code lines and too many tries to get this point. Is it worth it? No, not yet. But in a couple of years maybe, with proper training of course it will be possible to create a top to bottom query logic for a long query not just a case when block.