Multi-threaded database access

Question

I have a process (C ++ code) that reads and writes from a database (Oracle). But the process is time consuming.

I was thinking of creating partitions in the tables that this process requests. And then, making the process multithreaded so that each stream (one for each section) can read / write data in parallel.

I will create a DB connection for each thread.

Will write slow it down?
Will this work? Is there any other way to improve performance (all requests are tuned and optimized already)?

Thank you Nihil

Answer 1

If the current bottleneck writes data to the database, then creating more threads to write more data may or may not help, depending on how the data is partitioned and whether the records can occur at the same time or they interfere (or database lock level, or at the database disk IO level).

Creating more threads instead will allow the application to process more data and put them in a queue for writing to the database, assuming that there is sufficient concurrency hardware for processing additional threads (for example, on a multi-core computer).

Partitioning can improve database performance as well as change indexes in the corresponding tables. If you can put separate partitions on separate physical disks, then this can improve IO when only one partition needs to access a given SQL statement.

Dropping indexes that are not needed, changing the order of the index columns to match queries, and even changing the type of index can also improve performance.

Like everyone: profile it before and after each proposed change.