0

When is it save to modify a SqlCommand without modifying the query to be run?

For example, when I have

SqlCommand cmd = getCommand();
con.executeNonQueryAsync();
//later
modifyParameters(cmd);

is it possible that the NonQuery that gets executed sees the modified parameters? If so, is there any synchronization option before the returned task completed for after which modifying the command is guaranteed not to modify the run statement?

Martijn
  • 11,964
  • 12
  • 50
  • 96
  • 3
    Why would you do this? – ProgrammingLlama May 27 '20 at 08:41
  • 2
    `is there any synchronization option` - yes, `await con.executeNonQueryAsync();`. – GSerg May 27 '20 at 08:44
  • @John to prevent the overhead of re-creating commands in a loop, and run a number of fast NonQueries that spend relatively much time on network overhead. – Martijn May 27 '20 at 08:44
  • @GSerg "before the returned task completes" – Martijn May 27 '20 at 08:45
  • In my experience it has been better to create a table-valued parameter with each row of inputs and then perform the actual operations on the inputs server-side. – ProgrammingLlama May 27 '20 at 08:45
  • Does this answer your question? [Is SqlConnection / SqlCommand thread safe?](https://stackoverflow.com/questions/7316304/is-sqlconnection-sqlcommand-thread-safe) – Guru Stron May 27 '20 at 08:47
  • 2
    @GuruStron [async is not about threads](https://stackoverflow.com/q/17661428/11683). – GSerg May 27 '20 at 08:49
  • @GuruStron unfortunately, no. It could well all just run on a single thread. This is not about multi-threading, it's about concurrency. – Martijn May 27 '20 at 08:51
  • @GSerg I understand that. But in this case the possible issues would have similar nature, I think. – Guru Stron May 27 '20 at 08:54
  • 2
    Creating a new sql command for an existing connection is probably the most lightweight operation of them all. Having several uncompleted requests at once against the same connection, on contrary, may easily be a problem. I don't think SqlConnection supports it, and if it does, it's probably emulated via [serialized execution](https://stackoverflow.com/a/36415818/11683) anyway. – GSerg May 27 '20 at 08:57
  • 2
    @Martijn and it could not =) have you benchmarked your code to make sure that creating multiple commands is actual bottleneck? If yes the safeties option is to have a `SqlCommand` pool to reuse after finishing. – Guru Stron May 27 '20 at 08:57
  • That sounds plausible @GuruStron -- but it doesn't answer the direct question whether it's safe to modify the SqlCommand after ExecuteNonQueryAsync returns but before the returned Task completes, and if so, when. – Martijn May 27 '20 at 09:01
  • @GSerg in this case, it's a very fast operation that is likely to spend more time in network overhead and query planning than it spends in executing the query. Having a handful of commands executing concurrently is a quick and easy way to make sure you don't spend too much time with the database idle waiting for the network overhead of the previous query to finish and the next to start. – Martijn May 27 '20 at 09:07
  • I suggest you [actually benchmark it](https://ericlippert.com/2012/12/17/performance-rant/). It is most likely that you find your assumptions to be incorrect. In the unlikely event that you find them to be correct, you will find that you somehow have to work with the specific SQL that is generated by SqlCommand (which is an implementation detail subject to change). – GSerg May 27 '20 at 09:14
  • E.g. I know it internally uses `sp_executesql`, and sets an `int` cookie which it reads back after the `sp_executesql` returns, and expects the value to match - so you have to wait up until at least that, which a) you cannot do in any reliable fashion, and b) if you have waited that long, it's already about a microsecond before the suggested `await` would have completed. – GSerg May 27 '20 at 09:14
  • That sounds like the answer to the question of whether there is a point where it's safe to modify the SqlCommand before ExecuteNonQuery returns is no. That would be a useful answer to this question. – Martijn May 27 '20 at 09:20
  • It kind of is an answer, but it's not that part of it that is the answer. The part that is the answer is that it's an implementation detail. The fact that it happens to work that way now does not mean it will work that way next day. So if there was actually a way to safely reuse a command in that fashion now, due to the exact way it happens to be implemented, it wouldn't be reliable in any way in regard of the future versions. So the only answer would be "Don't rely on undocumented behaviour", which only leaves you the `await`. – GSerg May 27 '20 at 09:26

1 Answers1

1

No, you can't safely modify an SqlCommand at any time while a Task from some Execute*Async operation on the command hasn't completed yet.

Martijn
  • 11,964
  • 12
  • 50
  • 96