DbDataReader.GetValuesAsync

[rferraton]

I think an Async version of GetValues in a DbDataReader could be beneficial.
Something like

private static async IAsyncEnumerable<object[]> GetValuesAsync(this DbDataReader dataReader)
{
	var row = new object[dataReader.FieldCount];
	while (await dataReader.ReadAsync().ConfigureAwait(false))
	{
		dataReader.GetValues(row);
		yield return row;
	}
}

[roji]

GetValues is generally a bad API, since it forces the driver to internally allocate a new array each time it's used, without allowing for reuse of the array. Users are encouraged to avoid it and read into their own arrays, allowing array reuse across rows. In addition, unless CommandBehavior.SequentialAccess is specified, most ADO.NET buffer the entire row in any case, so DbDataReader.GetValues never needs to perform I/O in any case; that means that GetValueAsync isn't actually need in that case.

Regardless, if there's a case for adding such an API, it's preferable to discuss this as a general enhancement to DbDataReader in System.Data (on https://github.com/dotnet/runtime), rather than as a SqlClient-specific feature.

[rferraton]

Thank you for your answer @roji.
To be honnest, i am looking for a way to improve performance to retrieve large dataset. I did'nt find a fetchsize property like it exist in jdbc. So i oriented my reflexion with async way.
But maybe you could guide my reflexion to a better way ?

The application that i built is a bcp export like C# application where I use StringBuilder.AppendFormat applied on the object array return by GetValues.

[roji]

@rferraton I'd advise having clearer performance goals based on measurements and identified bottlenecks for your specific application scenarios, rather than a general "I'm looking to improve perf". In other words, have you encountered a performance issue that you're trying to fix?

IIRC JDBC fetchsize controls how many rows are fetched in each roundtrip; this is quite unrelated to DbDataReader.GetValueAsync, which only operates on a single row, fetching all its column values. In .NET, ADO.NET providers don't generally buffer rows by default (e.g. via database cursors), but rather stream them; drivers typically still buffer network data opportunistically (e.g. if more data is immediately available then is required for the next row), but that's under-the-hood driver behavior that shouldn't concern the user.

Again, I'd advise against using GetValues since it allocates an array per row. Other than that I'd need more details on what exactly you're trying to achieve in order to provide more guidelines.

[rferraton]

Hello @roji , thanks again or you advice against GetValues. I wrote a custom GetString (synchronous) method and remove other async call to return to a synchronous way of doing things.
Performances are 30% better than with the GetValuesAsync (or GetValues) and it is 15% faster than the original bcp export (when using the Microsoft.Data.SqlClient).
Initially I used a StringBuilder.AppendFormat with the GetValues hoping that the formatting could by applied to several rows in one shoot... but this is not possible as you said.

The current version is split into several parts where duration are like that :

• 1/3 of the time is spent in the Read()
• 1/3 in the GetString()
• 1/3 in the WriteLine(string.Join())

May be I could have better performance with asynchronous for all step to fluidify the process ?

• ReadAsync()
• GetStringAsync (using IAsyncEnumerable or Task?)
• WriteLineAsync() (Join need to be async too)

The current version look like the following code :

using (sqlConnection)
{
	var FullFileName = Path.Combine(filePath.ToString(), fileName.ToString());
	string vquery = inputsql;

	var sw = new StreamWriter(new FileStream(FullFileName, FileMode.Append, FileAccess.Write));

	try
	{
	int crows = 0;
	sqlConnection.Open();
	DbCommand sqr;
	sqr = new SqlCommand(inputsql, sqlConnection as SqlConnection);
		using (var sdr = sqr.ExecuteReader())
		{
			while (sdr.Read())
			{
				var row = new string[sdr.FieldCount];
				for (int i = 0; i < sdr.FieldCount; i++)
				{	
					row[i] = GetString(sdr.GetValue(i), dateformat, useQuoteIdentifier, nfi, typeCodes[i]);							
				}
				sw.WriteLine(string.Join(delimiter, row));
				crows++;
			}
		}					
		Console.WriteLine($"{DateTime.UtcNow:O} - INFO - file generation completed - {crows} rows in {FullFileName}");
	}

	catch (Exception e)
	{
		Console.WriteLine($"{DateTime.UtcNow:O} - ERROR - {e}");
		throw;
	}
	finally
	{
		sw.Dispose();
	}
}

public static string GetString(object objValue, string dateformat, int useQuoteIdentifier, NumberFormatInfo nfi, TypeCode typecode)
{
	switch (typecode)
	{
		case TypeCode.Int16:
		case TypeCode.Int32:
		case TypeCode.Int64:
			return objValue.ToString();
		case TypeCode.DBNull:
			return string.Empty;
		case TypeCode.DateTime:
			DateTime dt = (DateTime)objValue;
			return dt.ToString(dateformat);
		case TypeCode.Decimal:
			string NumericalFormatDecimal = "0.0###";
			var dec = (decimal)objValue;
			return dec.ToString(NumericalFormatDecimal, nfi);
		case TypeCode.Double:
			string NumericalFormatDouble = "0.0#####";
			var dbl = (double)objValue;
			return dbl.ToString(NumericalFormatDouble, nfi);
		default:
			if (useQuoteIdentifier == 1)
			{
				return "\"" + objValue.ToString().Replace("\"", "\\\"") + "\"";
			}
			else
			{
				return objValue.ToString();
			}
	}
}

`

[roji]

@rferraton it's hard to have a discussion about perf without knowing exactly what you're doing, what your goals are, what you're trying to optimize, etc. But here are a few general pointers:

• The general .NET recommendation is to use async I/O everywhere. This improves program scalability by using less threads and therefore also having less context-switching between them; you'd likely see a perf advantage here only if you execute your code concurrently. In any case, as a general rule avoid mixing async and sync I/O together in the same program (either do all-sync or all-async I/O).
  • One exception is DbDataReader APIs which reader columns (e.g. GetString). Database drivers usually buffer the entire row, meaning that all values are in memory and GetString doesn't do any I/O (this all makes sense unless you have very large columns, in which case CommandBehavior.SequentialAccess and async column APIs should be used). This means that ExecuteReaderAsync and ReadAsync should generally be used, but at the column level using the sync GetString/GetFieldValue should be fine.
• I see no discussion above on how you're measuring performance, only some Console.WriteLines with DateTime.UtcNow. I strongly recommend using the BenchmarkDotNet library to do reliable benchmarking.
• WriteLine's performance generally shouldn't matter in your application, as it's not typically used in an actual production application. Think carefully about what your actual application will do and what parts of it need optimization.