0

I created an application, which deals with multiple database table at a same time. At present I created a single connection for the process and trying to execute query like select query for multiple tables parallel. Each table may have hundreds of thousands or millions of records.

I have a connection and multiple statements that are executing parallel in threads. I want to find out is there any better solution or approach?

I am thinking that if I use connection pool of for example 10 connections and run multiple thread (less than 10) to execute select query. Will this increase my application's performance?

Is my first approach okay?

Is it not a good approach to execute multiple statement same time (parallel) on the database?

In this forum link mentioned that single connection is better.

Community
  • 1
  • 1
Tej Kiran
  • 2,218
  • 5
  • 21
  • 42
  • 1
    If going for multiple connections use a connection pool because closing and re-establishing connections is resource intensive in itself. – Recct Feb 25 '16 at 10:20

2 Answers2

0

Databases are designed to run multiple parallel queries. Using a pool will almost certainly enhance your throughput if you are experiencing latency not caused by the database.

If the latency is caused by the database then parallelising may not help - and may even make it worse. Obviously it depends on the kind of query you are running.

OldCurmudgeon
  • 64,482
  • 16
  • 119
  • 213
  • I am executing select query always – Tej Kiran Feb 25 '16 at 10:35
  • @TejKiran - How often? How many records might be returned? How many tables are joined? All of these contribute to whether parallelism will improve matters. – OldCurmudgeon Feb 25 '16 at 10:36
  • In the correct flow I am fetching all records in single query, because I don't know about the table structure that may have primary key or not. I am fetching all the record in on shot and then traversing each record and creating csv file [Its happening for each thread with single connection]. I read that all Oracle JDBC API methods are synchronized, if two threads try to use the connection object simultaneously, then one will be forced to wait until the other one finishes its use, it may be the main cause of not increasing performance. – Tej Kiran Feb 25 '16 at 10:42
0

I understand from your question that you are using a single Connection object and sharing it across threads. Each of those threads then executes it own statement. I will attempt to respond to your queries in reverse order.

Is it not good approach to execute multiple statement same time (parallel) on the database?

This is not really a relevant point for this question. Almost all databases should be able to run queries in parallel. And if it cannot then either of your approaches would be almost identical for a concurrency benefit perspective.

Is my first approach Okay?

If you are just doing SELECTs it may not cause issues but you have to very cautious about sharing a Connection object. A number of transactional attributes such as autoCommit and isolation are set on the Connection object - this would mean all those would be shared by all your statements. You have to understand how that works in your case.

See the following links for more information

Is MySQL Connector/JDBC thread safe?

https://db.apache.org/derby/docs/10.2/devguide/cdevconcepts89498.html

Bottomline is if you can use a Connection pool, please do so.

Will this increase my application's performance ?

The best way to check this is to try it out. Theoretical analysis for performance in a multithreaded environment and with database functions rarely gets you accurate results. But then again, considering point 2 it seems you should just go with Connection pool.

EDIT

I just realized what I am thinking as the concern here and what your concern actually is may be different. I was thinking purely from sharing the Connection object perspective to avoid creating additional Connection objects [either pooled or new].

For performance of getting all the data from the database either way (assuming the the 1st way doesn't pose a problem) should be almost identical. In fact even if you create a new Connection object in each thread the overhead of that should typically be insignificant compared to querying millions of records.

Community
  • 1
  • 1
uncaught_exception
  • 1,068
  • 6
  • 15