Dapper: QueryFirst and its alternatives are too slow

[Abdallah-Darwish]

Hi, I was trying extensions QueryFirst, QueryFirstOrDefault, QuerySingle, QuerySingleOrDefault and I noticed that they are much slower than using IDbReader In case the command returns more than one row "SELECT * FROM [Employee] WHERE [SALARY] < 90000".

So here is my testing code

  private static void Main()
        {
            var sw = new Stopwatch();
            TimeSpan readerTime = default, dapperTime = default;
            const string query = @"Select * FROM [Entity] Where Value < 99999";
            const int TestsCount = 100;
            Entity x, z;
            using (var con = new SqlConnection(@"Data Source=.;Initial Catalog=Scratch;Integrated Security=True"))
            {
                con.Open();
                for (int i = 0; i < TestsCount; i++)
                {
                    x = new Entity();
                    sw.Restart();
                    using (var com = new SqlCommand(query, con))
                    using (var reader = com.ExecuteReader())
                    {
                        reader.Read();
                        x.ID = reader.GetFieldValue<int>(0);
                        x.Value = reader.GetFieldValue<int>(1);
                        reader.Read();
                        sw.Stop();
                        readerTime += sw.Elapsed;
                    }
                }
                for (int i = 0; i < TestsCount; i++)
                {
                    sw.Restart();
                    z = con.QueryFirstOrDefault<Entity>(query);
                    sw.Stop();
                    dapperTime += sw.Elapsed;
                }
            }

            Console.WriteLine($"Normal approach time : {readerTime.TotalMilliseconds / TestsCount}ms");
            Console.WriteLine($"Dapper approach time : {dapperTime.TotalMilliseconds / TestsCount}ms");
            Console.WriteLine($"So..{(readerTime < dapperTime ? "reader " : "Dapper")} is faster!");
            Console.ReadLine();
        }

and here is the class Entity

class Entity
    {
        [Key]
        public int ID { get; set; }

        public int Value { get; set; }
    }

I tried to find the cause of the problem and I found some reasons behind it :

1. The linewhile (reader.Read()) { /* ignore subsequent rows */ } in method SqlMapper.QueryRowImpl , I can't understand why it even exists.
2. The enum CommandBehavior.SingleRow that is supplied to the reader, WHY I really dont know but if you pass it to the reader in my testing code you will get almost identical times.

[NickCraver]

This isn't really the designed/intended case - it's far more efficient to ask SQL for one row. Why would you not have a TOP 1 (or LIMIT, etc.) on the query? If the goal is efficiency, having SQL do more work than necessary (a single row can easily be a different plan, for example) is where it all starts.

[Abdallah-Darwish]

So...whats the reason behind this line while (reader.Read()) { /* ignore subsequent rows */ } ?
Why _not_ just dispose the reader ?

[mgravell]

In TDS, error messages (for exceptions) can follow the data. If you
dispose a reader without reading past the end of the stream, you can
totally miss errors.

So: that's why.

On 15 January 2018 at 10:36, abdallahesam [e-mail] wrote:

So...whats the reason behind this line while (reader.Read()) { /* ignore
subsequent rows / } ?
Why *not just dispose the reader ?

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/StackExchange/Dapper/issues/921#issuecomment-357644584,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AABDsJ_UgZr-rkVXDTvvoSvHQZ4XyPl3ks5tKyokgaJpZM4Rdcaa
.

--
Regards,

Marc