will multiple connections to any type of database(MS SQL, Oracle,DB2, MS access) from mulitple threads of an application developed in C# increase insert quries performance?

Question

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 mssql - using cursors).

For millions of records it is taking very very ... long time to update.So I want to make it faster by i).using multiple threads with an independent connection for each thread. or ii).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, plz reply.

Answer 1

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.

Answer 2

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.

Answer 3

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