Use sql_select_limit for CommandBehavior.SingleRow

[segor]

During migration from Mysql.Data we got performance issue in code that reads the first row from a SQL query using Dapper method QueryFirstOrDefaultAsync. With Oracle driver execution time is about 600 ms but with MySqlConnector execution time is about 50 seconds.
After some investigation I found that the problem is inside MySqlCommand.ExecuteReaderAsync. Here is the app I use to reproduce the issue:

using System;
using System.Data;
using System.Diagnostics;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace MysqlConnIssue
{
    class Program
    {
        static async Task Main(string[] args)
        {
            object result = null;
            var rand = new Random(); 
            using (var conn = new MySqlConnection("you conn str"))
            {
                await conn.OpenAsync();
                // table users contains 400K rows , _Ids - 1K rows
                //  a random value is used to disable query caching in mysql 
                using (var cmd = new MySqlCommand($@"
                SELECT IntegerValue 
                FROM _Ids
                WHERE IntegerValue NOT IN
                   (SELECT userid FROM users  WHERE userid <> {rand.Next()})", conn))
                {
                    var sw = new Stopwatch();
                    sw.Start();
                    // ExecuteReaderAsync is about 100 times slower when MySqlConnector is referenced
                    var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SingleRow);
                    Console.WriteLine($"ExecuteReader: {sw.ElapsedMilliseconds}ms");
                    sw.Restart();
                    if (await reader.ReadAsync())
                    {
                        result = reader[0];
                    }
                    Console.WriteLine($"Read: {sw.ElapsedMilliseconds}ms");
                }
            }

            Console.WriteLine($"Result: {result}");
        }
    }
}

Workaround is adding LIMIT 1 to the query but it will require to review all code we have

[bgrainger]

I can't reproduce the problem with the sample code you've provided. (I'm guessing there might be significant latency between your application and the DB server? How many rows would be returned from _Ids without a LIMIT 1 clause?)

However, I do see the underlying problem. Connector/NET executes a SET sql_select_limit=1; (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_select_limit) when CommandBehavior.SingleRow is specified, which ensures that only one row is returned from the database server. MySqlConnector lacks this optimisation.

[segor]

How many rows would be returned from _Ids without a LIMIT 1 clause?

It would return no more than 1000 rows. But execution time is more than 300 seconds. It seams like ExecuteReaderAsync reads some number of rows instead of just reading query headers

when CommandBehavior.SingleRow is specified, which ensures that only one row is returned from the database server

Actually I see the same issue with default CommandBehavior

@bgrainger I am going to adjust the sample app to be able to reproduce the issue locally

[bgrainger]

@segor Are you able to test 0.57.0-beta8 and let me know if there's a performance improvement?

[sergeyg-earnin]

@bgrainger Yes with 0.57.0-beta8 the ExecuteReaderAsync(CommandBehavior.SingleRow) is much faster and takes the same time comparing with Connector/NET.

But I still see significant difference in execution time of cmd.ExecuteReaderAsync()

[bgrainger]

But I still see significant difference in execution time of cmd.ExecuteReaderAsync()

Are you reading all 1000 (or so) rows from the reader? Are you measuring the time it takes for just that method to return, or something else?

I'm guessing there might be significant latency between your application and the DB server

Is that the case? 600ms just to return one row seems like a very long time.

[sergeyg-earnin]

I measure the cmd.ExecuteReaderAsync() call only , without reading any rows

var sw = new Stopwatch();
sw.Start();
// ExecuteReaderAsync is about 100 times slower when MySqlConnector is referenced
var reader = await cmd.ExecuteReaderAsync();
Console.WriteLine($"ExecuteReader: {sw.ElapsedMilliseconds}ms");

[sergeyg-earnin]

@bgrainger I have retested again, ExecuteReaderAsync() has similar time in both drivers. Sorry for confusing you.
ExecuteReaderAsync(CommandBehavior.SingleRow) in 0.57.0-beta8 is much faster. So I think this issue was fixed and can be closed.