SqlCommand inside SqlDataReader inside SqlCommand

Question

I have some code that looks something like this:

using (SqlConnection sqlCon = new SqlConnection("connection string which includes MultipleActiveResultSets=True")
{
    using (SqlCommand sqlCmd1 = new SqlCommand("SELECT * FROM MyTable WHERE ProcessedRslt IS NULL", sqlCon)
    {
        using (SqlDataReader sqlRdr = sqlCmd1.ExecuteReader())
        {
            using (SqlCommand sqlCmd2 = new SqlCommand("UPDATE MyTable SET ProcessedRslt = @Result WHERE RecordID = @RecordID", sqlCon)
            {
                sqlCmd2.Parameters.Add("@RecordID", SqlDbType.Int);
                sqlCmd2.Parameters.Add("@Result", SqlDbType.Int);

                while (sqlRdr.Read())
                {
                    int result = ProcessRecord(sqlRdr["RecordID"], ...) // <- This function takes a bunch of values from the SqlDataReader, performs some fairly complex operations on them and returns a result indicating success or what type failure was encountered.

                    sqlCmd2.Parameters["@RecordID"].Value = (int)sqlDataReader["RecordID"];
                    sqlCmd2.Parameters["@Result"].Value = result;
                    sqlCmd2.ExecuteNonQuery(); // !!!THIS IS THE LINE I'M CONCERNED ABOUT!!!
                }                    
            }
        }
    }
}

So this code is sitting in a production environment and have been running well for years. But there is the occasional hiccup and lately it's been happening more regularly. The while (sqlRdr.Read()) loop runs smoothly a few thousand times and then out of the blue the sqlCmd2.ExecuteNonuery() line causes a timeout which is caught by my exception handling.

For the life of me I can't figure out why that simple UPDATE statement should time out in SQL, especially when the same statement has just executed successfully a few thousand times without hiccups. I'm not too clued up on Microsoft SQL Server maintenance and I'm wondering if some other process that might be accessing the database and this table in particular might be causing a deadlock or something. I don't have control over (or even perfect knowledge of) what other processes might be accessing the database at what time.

But I would've thought that UPDATE MyTable SET this WHERE that is a pretty straight forward atomic query that shouldnt be affected by other queries run against the DB at the same time? Or could the fact that it sits inside a fairly long running using (SqlDataReader...) block be part of the problem?

If so, what is the idiomatic way of reading a large number of records from a table, performing a task on each one, and then updating each one individually based on the outcome of the task performed? Surely this must be a common use case?

what connection is sqlCmd2 running on? it isn't shown as running on any connection in the code, in which case no: it won't work; if it is sqlCon, then ... this is bad practice; it can work if MARS is enabled, but MARS should virtually never be enabled - a separate connection is preferred (and yes: I see that MARS is enabled in your comment, but...) - so: it should work, but: timeouts will occasionally happen; as for why: you'd need to look at the specific scenario - what locks are taken, serve activity, etc — Marc Gravell
– Marc Gravell, Commented Jun 30, 2020 at 12:38
Sorry, that's an oversight on my part. It uses the same connection as SqlCmd1. I've edited the question to reflect this. So if you say that this is bad practice, can you point me to an example of the idiomatic way to do the above? — Dewald Swanepoel
– Dewald Swanepoel, Commented Jun 30, 2020 at 13:38
for avoiding MARS: simply open a second connection and use that for the inner command — Marc Gravell
– Marc Gravell, Commented Jun 30, 2020 at 16:02

Joel Coehoorn · Accepted Answer · 2020-06-30 13:51:55Z

1

You need two connections for this, or to Fill() the results from the first query into a DataTable, so you can loop over the results after the initial query has fully completed¹. The SQL ADO.net provider supports multiple active result sets on the same connection, but what this is trying to do will change the results from the first query as it runs. This can create deadlock situation on the server, and you really don't want that.

Even better if you can move the ProcessRecord() logic to T-SQL, like this:

UPDATE t
SET ProcessedRslt = ProcessRecord(t.RecordID, ...)
FROM MyTable t
WHERE ProcessedRslt IS NULL

...

CREATE FUNCTION dbo.ProcessRecord(@RecordID int, ...)

This will likely run at least an order of magnitude faster.

Better still if you can simplify the logic enough to be able to handle it inline with the UPDATE.

_{1 The downside here is things are no longer atomic.}

answered Jun 30, 2020 at 13:51

Joel Coehoorn

418k114 gold badges581 silver badges818 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dewald Swanepoel Over a year ago

Thanks, that makes sense... kind of. I'd like to get clarity on "but what this is trying to do will change the results from the first query as it runs". I thought the sqlCmd1.ExecuteReader() command runs the actual query while the while (sqlRdr.Read()) loop simply iterates over the returned result set. So when the UPDATE is executed, it does change the results from the first query but the first query have already completed so it doesn't matter. Have I got this wrong? What exactly does SqlDataReader do? Does it continuously execute against the actual SQL db?

Joel Coehoorn Over a year ago

ExecuteReader() never downloads a full result set. Often Sql Server will need to materialize all of the results into RAM to satisfy a query, but even when this happens ExecuteReader() still only retrieves one record from Sql Server at a time. Read() sends a command to the database to download the next record. Additionally, for queries that allow it, ExecuteReader() will get results from Sql Server as they are available, so Sql Server also doesn't need to reserve RAM for the entire result set.

Joel Coehoorn Over a year ago

To continue... what can happen is Sql Server will start streaming results from the table for the first query, and therefore have a lock on that table, or at least the rows/pages that matter, but then you want to update the locked records before finishing the query, and block from the client until the update happens. So the update can't finish, but the lock is never released. Deadlock.

Fran Aguilar · Accepted Answer · 2020-06-30 12:38:22Z

0

I Suggest you to paginate your sqlCmd1 sql command in small parts to avoid possible timeouts. Long active connections can be closed by server.

answered Jun 30, 2020 at 12:38

Fran Aguilar

1105 bronze badges

Collectives™ on Stack Overflow

SqlCommand inside SqlDataReader inside SqlCommand

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related