4

I don't know what I might have done to cause this, but a await reader.ReadAsync() is taking 8000 ms, compared to 2ms if I replace ReadAsync() with Read() in the same method

using (SqlConnection conn = new SqlConnection())
{
    conn.ConnectionString = connectionString;
    await conn.OpenAsync();

    string cmdText = "SELECT * FROM images WHERE order_id = @OrderId";

    using (SqlCommand cmd = new SqlCommand(cmdText, conn))
    {
        cmd.Parameters.Add("@OrderId", SqlDbType.UniqueIdentifier).Value = id;

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            if (!reader.HasRows) return null;

            await reader.ReadAsync();
            return (byte[])reader["image"];
        }
     }
 }

Removed some of the method for readability

The varbinary in question is about 11MB in size. The actual query execution is still fast, it's just the read that's incredibly slow. Smaller varbinaries are fine.

The image column is a FILESTREAM

p3tch
  • 1,414
  • 13
  • 29
  • is anything running concurrently at the same time, how did you test your subject's statement? also change: `await Task.FromResult((byte[])reader["image"]);` to just `return reader["image"]);` – Joel Harkes Oct 06 '17 at 11:03
  • I used breakpoint in debugger initially, then a stopwatch which I output the result of There is a long running task in the background that is checking an API and saving various records to this same SQL database periodically, but even when that's idle this read still takes a long time. – p3tch Oct 06 '17 at 11:11
  • Not to skirt the issue but do you really need `async`/`await`? This is *generally* only useful if you want to scale the number of concurrent threads like with a web application. – Igor Oct 06 '17 at 11:14
  • 2
    Ooh, I remember this one. There's indeed a performance regression bug in reading binary blobs with the async methods, and there's a question on that on this very SO. Now, if only I could find it... – Jeroen Mostert Oct 06 '17 at 11:17
  • 1
    It's a wcf service @Igor – p3tch Oct 06 '17 at 11:17
  • 1
    Try `cmd.ExecuteReaderAsync(CommandBehaviour.SequentialAccess)` (copied from possible duplicate) – Igor Oct 06 '17 at 11:21
  • @Igor: no, but I do think it's describing the same issue. – Jeroen Mostert Oct 06 '17 at 11:21
  • Entire method down to 139ms with SequentialAccess! I also forgot to mention, the `image` column is a filestream. I'll edit my Q. Thanks @Igor – p3tch Oct 06 '17 at 11:26
  • 1
    Glad it worked. As the cause and solution can be found in that other question I am going to mark this as a duplicate. Feel free to upvote the accepted answer in the dupe. – Igor Oct 06 '17 at 11:27
  • 2
    I was thinking of [this one](https://stackoverflow.com/q/28543293/4137916), which has a very similar analysis (and also mentions `SequentialAccess`). The bug (in that case) is EF not using that automatically. – Jeroen Mostert Oct 06 '17 at 11:29

0 Answers0