MySQL/MariaDB - Repeatedly Calling Prepared Statement using C API Performance Degradation

Question

I have a somewhat complicated SQL command that uses a dozen or so common table expressions and window functions that I need to call repeatedly. As such, I'm trying to use a prepared statement. It has 10 input bindings, and 6 output bindings.

It touches a few tables, the biggest is a few GB with 15 million records. A single execution of the query takes between 50-200 ms.

When I structure the code as:

    MYSQL_STMT* statement = mysql_stmt_init(db_connection);
    Assert(statement);
    char* sql = ...
    s32 status = mysql_stmt_prepare(statement, sql, sql_length);
    Assert(status == 0);

    // ...setup input binding (10 params)...

    s32 input_bind_result = mysql_stmt_bind_param(statement, input_bind);
    Assert(input_bind_result == 0);
    
    // ...setup output binding (6 columns)..
        
    s32 output_bind_result = mysql_stmt_bind_result(statement, output_bind);
    Assert(output_bind_result == 0);

    for (int i = 0; i < 1000; i++)
    {
        s32 execute_result = mysql_stmt_execute(statement);
        Assert(execute_result == 0);
        
        while (true)
        {
            s32 fetch_result = mysql_stmt_fetch(statement);
            if (fetch_result == MYSQL_NO_DATA)
                break;
            
            Assert(fetch_result == 0);
            
            // ...process row...
            // ...printf(timestamp and query output)...
        }

        // ...increment input parameters...
    }
    
    mysql_stmt_close(statement);

Each time the query is executed it gets slower and slower, and quite quickly at that:

    2021-06-30 21:24:40: 0.996904
    2021-06-30 21:24:40: 0.995356
    2021-06-30 21:24:41: 0.000000
    2021-06-30 21:24:41: 0.000000
    2021-06-30 21:24:41: 0.000000
    2021-06-30 21:24:41: 0.000000
    <hangs indefinitely>

It is hanging at returning from mysql_stmt_execute(). It is not deterministic. If I reset the query cache and restart, it might hang at the 5th query, or the 8th.

By contrast, if I structure the code such as:

    // ...setup input binding (10 params)...
    // ...setup output binding (6 columns)...
    
    for (int i = 0; i < 1000; i++)
    {
        MYSQL_STMT* statement = mysql_stmt_init(db_connection);
        Assert(statement);
        char* sql = ...
        s32 status = mysql_stmt_prepare(statement, sql, sql_length);
        Assert(status == 0);

        s32 input_bind_result = mysql_stmt_bind_param(statement, input_bind);
        Assert(input_bind_result == 0);     
        
        s32 output_bind_result = mysql_stmt_bind_result(statement, output_bind);
        Assert(output_bind_result == 0);
            
        s32 execute_result = mysql_stmt_execute(statement);
        Assert(execute_result == 0);
        
        while (true)
        {
            s32 fetch_result = mysql_stmt_fetch(statement);
            if (fetch_result == MYSQL_NO_DATA)
                break;
            
            Assert(fetch_result == 0);
            
            // ...process row...
            // ...printf(timestamp and query output)...
        }

        // ...increment input parameters...
        
        mysql_stmt_close(statement);
    }

That is, literally creating and releasing the prepared statement every iteration, there is no such degradation and I process each iteration quite quickly:

    2021-06-30 21:26:59: 0.996904
    2021-06-30 21:27:00: 0.995356
    2021-06-30 21:27:00: 0.000000
    2021-06-30 21:27:00: 0.000000
    2021-06-30 21:27:00: 0.000000
    2021-06-30 21:27:00: 0.000000
    2021-06-30 21:27:00: 0.994969
    2021-06-30 21:27:00: 0.997291
    2021-06-30 21:27:00: 0.997678
    2021-06-30 21:27:00: 0.998452
    2021-06-30 21:27:00: 0.998452
    2021-06-30 21:27:00: 0.998452
    2021-06-30 21:27:00: 0.998839
    2021-06-30 21:27:01: 0.998839
    2021-06-30 21:27:01: 0.998839
    2021-06-30 21:27:01: 0.998454
    2021-06-30 21:27:01: 0.999230
    2021-06-30 21:27:01: 0.999230
    2021-06-30 21:27:01: 0.999230
    2021-06-30 21:27:01: 0.998459
    2021-06-30 21:27:01: 0.998844
    2021-06-30 21:27:01: 0.998844
    2021-06-30 21:27:02: 0.999230
    2021-06-30 21:27:02: 0.999231
    2021-06-30 21:27:02: 0.999231
    2021-06-30 21:27:02: 0.999231
    2021-06-30 21:27:02: 0.999615
    2021-06-30 21:27:02: 1.000000
    2021-06-30 21:27:02: 1.000000
    2021-06-30 21:27:02: 0.999232
    2021-06-30 21:27:02: 0.999617
    2021-06-30 21:27:02: 0.999617
    2021-06-30 21:27:02: 0.999617
    ...continues at this speed for 1000+ iterations...

But this doesn't make a lot of sense to me, and negates one of the main benefits for using prepared statements. Any insight as to what could be causing this?

It feels like some resources aren't being freed, but I have tried adding:

 mysql_stmt_free_result(statement);

or

 mysql_stmt_reset(statement);

at the end of each iteration, but the degradation still persists.

Running MariaDB 10.5.10, mariadb-connector 3.1.13.

Answer 1

Show us the statement.

If one of the parameters is for OFFSET, be aware that all the "offset" rows must be stepped over.

If the statement is DELETE ... LIMIT, then it could be searching farther and farther to find the desired rows.

ETC!