killwort
Support Async methods in ClickHouseConnection and ClickHouseCommand
Description
Propose to support async operations in implementation of ClickHouseConnection, ClickHouseCommand. It should be easy as long as it uses TcpClient under the hood.
What should be done
We can add methods OpenAsync, ConnectAsync, ExecuteNonQueryAsync and other methods as its done in System.Data.Common.DbConnection, System.Data.Common.DbCommand. In async implementation we can use existing SslStream.AuthenticateAsClient, await TcpClient.ConnectAsync and others.
I'm not @killwort but it is not much sense to have async ADO.NET implementation for ClickHouse. This doesn't give any significant benefits, at the same time async code may easily add hardly-reproducible issues.
ClickHouse is used for OLAP queries, and they rarely take less then hundreds ms. CH is not a transactional DB, it is not designed for many-many concurrent connections. If you have 100 concurrent queries, that's already may be a problem for CH. At the same time, 100 locked threads (because of synchronous implementation) is a not a big deal for web app.
If you have many similar queries to CH, it makes sense simply to organize a cache on .NET side.
At the same time, 100 locked threads (because of synchronous implementation) is a not a big deal for web app
It depends on resource you have for app (not only web) and other work this app do. Even one locked thread can be a problem. In general async implementation seems natural for such library.
at the same time async code may easily add hardly-reproducible issues.
Well, most nowday libraries communicating with database over http/tcp implement async methods, so we know how to deal with async code and its issues. I don't think it should be an argument to not create such methods.
most nowday libraries communicating with database over http/tcp implement async methods, so we know how to deal with async code and its issues. I don't think it should be an argument to not create such methods.
you may propose a PR :) it's easy to use HTTP connections async, and this is how https://github.com/DarkWanderer/ClickHouse.Client works - maybe it is more suitable in your case, when even one locked thread is significant.
I totally agree with @VitaliyMF - adding async does not provide any benefit by itself as internally this driver is completely sync. Reworking it into async model is like doing everything from the scratch and I don't have neither time neither motivation for that.
2.0.0-preview implements async stuff all around. Not production ready for now, yet I hope to stabilize it in a couple of weeks.