CODEWITHSUNDEEP
c#jsonsqldatareader
Nathan Raley
Nathan Raley

Reputation: 35

Most efficient way and fastest way to read JSON string from SqlDataReader

I am trying to return a rather large dataset (several thousand rows) from a SQL Server 2016 database formatted as JSON (using the FOR JSON AUTO feature of SQL Server 2016); however, I am running into issues reading the results from the SqlDataReader.

I was previously loading the results into a DataTable as regular rows, and this works quite well (it takes approximately 10-15 seconds to load the entire table). However, if I try to build the string returned as JSON, using the same data, it is taking several minutes to build the string returned from the DataReader. I'd like to add, that I am also returning a substantial amount of Binary data in this query (I have SqlGeometry objects I am trying to retrieve from the database). My typical string returned from this is several 100k characters long and the reading from the reader is horrendously slow.

Anyway, my loading code for parsing the JSON is as follows:

public static Task<string> ExecuteJsonReaderAsync(string connectionString, CommandType commandType, string commandText, SqlParameter[] oParams = null, int timeout = 30, CancellationToken token = default(CancellationToken))
    {
        return Task<string>.Factory.StartNew(() =>
        {
            var str = string.Empty;
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.CommandTimeout = timeout;
                    command.CommandType = commandType;

                    if (oParams?.Length > 0) command.Parameters.AddRange(oParams);

                    using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            str = $"{str}{reader[0]}";
                        }
                        reader.Close();
                    }    

                    return str;
                }
            }
        }, token);    
    }

I have tried various command options to attempt to speed it up ranging from the CloseConnection, SequentialAccess, SingleResult, but to no avail. Why is building the string taking so much longer than loading a DataTable from the same data, and is there any faster way to accomplish this?

I figure that has to be something that I am doing wrong or that I overlooked and I'm hoping that someone has ran across this issue before. Any ideas?

Upvotes: 2

Views: 2853

Answers (2)

Dave Black
Dave Black

Reputation: 8049

There are a couple of issues here:

  1. your use of string formatting via $"{str}{reader[0]}" is very performance heavy on the GC. You should be using a StringBuilder and the Append() method.
  2. you should really be using async all the way thru like so:

Here is how I would write the code for better performance (both scaling-wise via async and memory-wise via StringBuilder):

        public static async Task<string> ExecuteJsonReaderAsync(string connectionString, CommandType commandType, string commandText, SqlParameter[] oParams = null, int timeout = 30, CancellationToken token = default(CancellationToken))
        {
            var str = string.Empty;
            await using var connection = new SqlConnection(connectionString);

            await connection.OpenAsync(token);

            await using var command = new SqlCommand(commandText, connection);
            command.CommandTimeout = timeout;
            command.CommandType = commandType;

            if (oParams?.Length > 0) command.Parameters.AddRange(oParams);

            var stringBuilder = new StringBuilder(1024 * 1024);
            await using var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection, token);
            while (await reader.ReadAsync(token))
            {
                stringBuilder.Append(reader[0]);
            }
            await reader.CloseAsync();

            return stringBuilder.ToString();
        }

Upvotes: 0

Steve
Steve

Reputation: 216358

Your code reallocates the string variable in memory at each loop. This is detrimental to the performances of your code. Instead the class StringBuilder has an internal buffer that allows a lot less memory reallocations and you can also control the size of this buffer to avoid the reallocation to happen at all if you know the total length of your data.

So

// Set an initial capacity of 1MB
StringBuilder str = new StringBuidler(1024*1024);
while (reader.Read())
{
    str.Append(reader[0].ToString());
}

....
return str.ToString();

More about the C# string immutable concept here

Upvotes: 3

Related Questions