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?

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. Apr 8 '20 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).
    – alroc
    Apr 8 '20 at 12:12
  • You may be right. I think using a using block would be much better than using a single connection. Apr 8 '20 at 12:20
  • The common using/open/execute pattern handles multiple threads well and leverages connection pooling too.
    – Dan Guzman
    Apr 8 '20 at 12:22
2

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.

3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.