CODEWITHSUNDEEP
c#multithreadingdatabase-connection
LoyalBanana
LoyalBanana

Reputation: 183

Will multiple connections to a database from mulitple threads of an application increase insert queries performance?

Here I am dealing with a database containing tens of millions of records. I have an application which connects to the database, gets all the data from a single column in a table and does some operation on it and updates it (for SQL Server - using cursors).

For millions of records it is taking very very ... long time to update. So I want to make it faster by

  1. using multiple threads with an independent connection for each thread.

or

  1. by using a single connection throughout all the threads to fire the update queries.

Which one is faster, or if you have any other ideas plz explain.

I need a solution which is independent of database type , or even if you know specific solutions for each type of db, please reply.

Upvotes: 3

Views: 2800

Answers (3)

A.m.a.L
A.m.a.L

Reputation:

If possible try to use the Stored procedure the do all the processing and update the records.

Upvotes: 0

Richard
Richard

Reputation: 109025

All depends on the database, and the hardware it is running on.

If the database can make use of concurrent processing, and avoids contention on shared resources (e.g. page base locks would span multiple records, record based would not). Shared resources in this case include hardware, a single core box will not be able to execute multiple CPU intensive activities (e.g. parsing SQL) truely in parallel.

Network latency is something you might help alleviate with concurrent inserts even if the database is itself not able to exploit concurrency.

As with any question of performance there is substitute for testing in your specific scenario.

Upvotes: 0

Kosi2801
Kosi2801

Reputation: 23115

The speedup you're trying to achieve won't work. To the contrary, it will slow down the overall processing as the database now has also to keep multiple connections/sessions/transactions in sync.

Keep with as few connections/transactions as possible for repetitive and comparable operations. If it takes too long for your taste, maybe try to analyze if the queries can be optimized somehow. Also have a look at database-specific extensions (ie bulk operations) suitable for your problem.

Upvotes: 5

Related Questions