Where is stored procedure result set stored while SqlReader is reading?-CodePudding

As per docs SqlCommandTimeout is

This property is the cumulative time-out (for all network packets that are read during the invocation of a method) for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time.

For example, with a 30 second time out, if Read requires two network packets, then it has 30 seconds to read both network packets. If you call Read again, it will have another 30 seconds to read any data that it requires.

I have code below that executes the stored procedure and then reads the data using SqlReader row by row.

public static async Task<IEnumerable<AvailableWorkDTO>> prcGetAvailableWork(this MyDBContext dbContext, int userID)
{            
    var timeout = 120

    var result = new List<AvailableWorkDTO>();

    using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
    {
        var p1 = new SqlParameter("@UserID", SqlDbType.Int)
            {
                Value = userID
            };

        cmd.CommandText = "dbo.prcGetAvailableWork";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(p1);
        cmd.CommandTimeout = timeout;

        await dbContext.Database.OpenConnectionAsync().ConfigureAwait(false);

        using (var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false))
        {
            while (await reader.ReadAsync().ConfigureAwait(false))
            {
                var item = new AvailableWorkDTO();
                item.ID = reader.GetInt32(0);
                item.Name = reader.GetString(1);
                item.Title = reader.GetString(2);
                item.Count = reader.GetInt32(3);

                result.Add(item);
            }
        }
    }

    return result;
}

In Sql Profiler I see only one call to stored procedure as expected. So I am guessing the stored proc executes and returns entire result set.

Questions
1>If SqlReader is reading one record at a time, where is the entire resultset is stored while reader is reading? Is it temporarily stored in SQL Server memory or Application Server memory?

2>Using EF Core is there any way to read the entire result set at once?

CodePudding user response：

The resultset isn't stored anywhere, it's streamed directly to the client. As the server reads rows from disk or memory, they are fed through the query plan and out across the network. This is why you always need to make sure read as fast as possible and to dispose the reader and connection: because the query is running the whole time.

To "read the entire result set at once", you just do what you are doing now: loop the reader and add it to a List. Alternatively, you could use DataTable.Load, however I do not advise this, and it is also not async.

The reader is just an object that is capable of returning individual rows from a command. What you see in the profiler is a single execution of a command. If you also monitor SQL:Batch Completed event, you will see that that only happens when the reader is finished.

CodePudding user response：

you can use a stored procedure with EF instead of ADO.Net , but I am not sure that it will be faster.

Create a special class to get data from sp or use existing AvailableWorkDTO. This class should have all properties that select clause of your store procedure has. You don't need to select everytning in your stored procedure. Just select the properties that AvailableWorkDTO has and add NotMapped atribute

[NotMapped]
public class AvailableWorkDTO 
{
.....
}

after this add this class to dbContext DbSet

public virtual DbSet<AvailableWorkDTO> AvailableWorkDTOs { get; set; }

And this is a sample function to show how to get data using the store procedure

public async Task<IEnumerable<AvailableWorkDTO>> prcGetAvailableWork(MyDBContext dbContext, int userID)
{ 
    var pId = new SqlParameter("@UserID", userID);
    return await dbContext.Set<AvailableWorkDTO>()
             .FromSqlRaw("Execute db.prcGetAvailableWork @UserID", pId)
            .ToArrayAsync();
}