0

I am making a multi threaded application in which I will use a single SQL Server connection and perform select, insert, update and delete.

So once my application starts, it will open connection with SQL Server and then multiple threads will run each doing select, insert, update and delete (in no particular order) on multiple tables using the same connection.

I want to ask how many operations can SQL Server handle for a single connection?

Improve this question
4
  • What is the CPU, Memory and disk performance for this server? What are the actual queries being run? What is the exact schema (including indexes, triggers, etc.) for the database? Is your database supporting only transactional workloads or must it also support analytical/reporting? .... There are a LOT of variables. But I would guess at easily hundreds. Commented Apr 8, 2020 at 12:04
  • Why are you limiting yourself to a single connection? A connection pool would be safer and probably require less effort on your part (as there are libraries, etc. that have already implemented them). Commented Apr 8, 2020 at 12:12
  • You may be right. I think using a using block would be much better than using a single connection. Commented Apr 8, 2020 at 12:20
  • The common using/open/execute pattern handles multiple threads well and leverages connection pooling too. Commented Apr 8, 2020 at 12:22

1 Answer 1

Reset to default
3

a multi threaded application in which I will use a single SQL Server connection

Bad idea. A database connection usually is not thread-safe, and as you suspect, at any one time one connection can only execute one statement. You should open a separate connection for each application thread instead, or at least use a connection pool shared between the threads.

Improve this answer
3
  • You may be right. I think using a using block would be much better than using a single connection. And connection pooling is automatically handled by SQL Server as per following links so I don't need to worry about it (at least if threads are reasonable for e.g. running 20 threads simultaneously and not 200000) dba.stackexchange.com/questions/77060/is-sql-server-thread-safe stackoverflow.com/questions/21731641/sqlconnection-thread-safe stackoverflow.com/questions/7316304/… Commented Apr 8, 2020 at 12:21
  • I'm not arguing with you, just pointing out a limitation. Also, the OP says "will run each doing select, insert, update and delete", so interleaved selects won't really cut it, I don't think. Commented Apr 8, 2020 at 13:30
  • Oh, sure thing. I definitely don't think MARS is the answer here. In retrospect, I was just being pedantic. Commented Apr 8, 2020 at 17:13

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.