.NET Async operation performance problems
Posted by: Tuomas Hietanen
Date: May 24, 2016 04:17AM

If I have a code that ExecuteNonQuery or ExecuteReader or ExecuteScalar they work just ok and the performance is... well, quite ok. Now, if I wan't to not eat up not so much resources, I run the async versions, ExecuteNonQueryAsync or ExecuteReaderAsync or ExecuteScalarAsync and I expect the business logic thread to be non-blocked while SQL is executing. For technical background details see: http://fsprojects.github.io/SQLProvider/core/async.html

Anyways, this is the expected result. Actual result is that if I run this with 10 of threads (like web servers usually do), not only the threads are not locked ( http://stackoverflow.com/questions/30551459/mysql-c-sharp-async-methods-doesnt-work ), but the whole server is jammed and I got SQL-deadlocks and everything.

Here is the sample source code in F#:
let cstr = "server = localhost; database = TestDB; uid = root;pwd = 12345"

let ExecuteSqlOld (query : string) parameters =
    use rawSqlConnection = new MySqlConnection(cstr)
    rawSqlConnection.Open() 
    use command = new MySqlCommand(query, rawSqlConnection)
    parameters |> List.iter(fun (par:string*string) -> command.Parameters.AddWithValue(par) |> ignore)
    let affectedRows = command.ExecuteNonQuery()
    ()

let ExecuteSql (query : string) parameters =
  async {
    use rawSqlConnection = new MySqlConnection(cstr)
    do! rawSqlConnection.OpenAsync() |> Async.AwaitTask
    use command = new MySqlCommand(query, rawSqlConnection)
    parameters |> List.iter(fun (par:string*string) -> command.Parameters.AddWithValue(par) |> ignore)
    let! affectedRows = command.ExecuteNonQueryAsync() |> Async.AwaitTask
    ()
  }

The problem is also reproducable with the source code in this repo, under Thread test, changing the last method to use SubmitUpdatesAsync:
https://github.com/fsprojects/SQLProvider/blob/master/tests/SqlProvider.Tests/scripts/MySqlTests.fsx

Options: ReplyQuote


Subject
Written By
Posted
.NET Async operation performance problems
May 24, 2016 04:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.