CODEWITHSUNDEEP
javajakarta-eejdbc
Oomph Fortuity
Oomph Fortuity

Reputation: 6148

5 Different Queries with 5 JDBC Connection Or 5 Different Queries with 1 JDBC Connection.Which is good?

I want to know about JDBC Connection use with respect to performance.

E.g I want to fetch 5 Different Objects from Database having 5 Different Select Queries to fetch.

I have to options to achieve this.You can also tell another ways.

First One which is Currently I am Using

void fetchSeventObjectMethod(long id1,long id2,.....,long id5) {

    1. Open JDBC Connection

    2. PrepareStatment 1 
       2.1   Object 1 Fetch Query
       2.1   Bind Object 1 from ResultSet 1
       2.1   **Close** PrepareStatment 1 and ResultSet 1

    3. PrepareStatment 2 
       2.1   Object 2 Fetch Query
       2.1   Bind Object 2 from ResultSet 2
       2.1   Close PrepareStatment 2 and ResultSet 2

    4. PrepareStatment 3 
       2.1   Object 3 Fetch Query
       2.1   Bind Object 3 from ResultSet 3
       2.1   Close PrepareStatment 3 and ResultSet 3

    5. PrepareStatment 4 
       2.1   Object 4 Fetch Query
       2.1   Bind Object 4 from ResultSet 4
       2.1   Close PrepareStatment 4 and ResultSet 4

    6. PrepareStatment 5 
       2.1   Object 5 Fetch Query
       2.1   Bind Object 5 from ResultSet 5
       2.1   Close PrepareStatment 5 and ResultSet 5

    7. Close JDBC Connection in FINAL BLOCK

}

And Another is 

 void fetchSeventObjectMethod(long id1,long id2,.....,long id5) {    
    call fetchObjectOneMethod(id1); 
    call fetchObjectTwoMethod(id2); 
    call fetchObjectTHreeMethod(id3); 
    call fetchObjectFourMethod(id4); 
    call fetchObjectFiveMethod(id5); 
    }

        void fetchObjectOneMethod(long id1) {    
            1. Open JDBC Connection      
            2. PrepareStatment 1 
               2.1   Object 1 Fetch Query
               2.1   Bind Object 1 from ResultSet 1
               2.1   **Close** PrepareStatment 1 and ResultSet                          
            3. Close JDBC Connection in FINAL BLOCK           
        }

       void fetchObjectTwoMethod(long id2) {              
            1. Open JDBC Connection            
            2. PrepareStatment 1 
               2.1   Object 1 Fetch Query
               2.1   Bind Object 1 from ResultSet 1
               2.1   **Close** PrepareStatment 1 and ResultSet 1                         
            3. Close JDBC Connection in FINAL BLOCK            
        }

       void fetchObjectTHreeMethod(long id3) {             
            1. Open JDBC Connection           
            2. PrepareStatment 1 
               2.1   Object 1 Fetch Query
               2.1   Bind Object 1 from ResultSet 1
               2.1   **Close** PrepareStatment 1 and ResultSet 1                        
            3. Close JDBC Connection in FINAL BLOCK           
        }

Here scenario is void fetchSeventObjectMethod(....) is called by several threads at time upto thousands or more.

in case of first one Just Single Connection is Used in case of Second Five Connections are Used

Which is better option in performance consideration or is there is alternative way. Here I am using c3p0 connection pooling also. Please help me.

Thank you

Upvotes: 0

Views: 93

Answers (4)

Pantelis Natsiavas
Pantelis Natsiavas

Reputation: 5369

As Steve Mc Conell says, regarding performance, no assumptions are safe. You have to measure things before making a change, in order to decide if the code change, the code shuffling etc worth the trouble.

In your case though, I suppose that the choice is relatively easy. You should open the connection once and execute multiple queries for each connection. This way, you pay the connection overhead only once. This is the reason why JDBC offers you a way to handle the connections and many connection management schemas have been developed. Applying JDBC connection pooling gives you the option to use multiple connections for multiple queries, without the open/close overhead.

Hope I helped!

Upvotes: 2

Jay
Jay

Reputation: 681

As you say if you are working with multiple threads you can use JDBC Connection Pooling to manage your connections. But though you have 1000+ threads, getting 1000+ connections from connection pool is also very heavy process. Number of connections will be dependent on size of connection pool you provide. You can work with batch processing.

Upvotes: 0

xrcwrn
xrcwrn

Reputation: 5327

In first case if you can use joins it will be better.

Here IO time is also less.

for individual fetch you have to write code again(reusable for individual use is less)

In secone case IO time is more.

for individual fetch you can use the same code again(re usability is there for individual use)

In your scenario first one is good.

But you can get better performance by using joins, using stored procedures,using connection poolings

Upvotes: 2

harsh
harsh

Reputation: 7692

JDBC Connection are heavy resource object, open and close are costly affair in terms of IO (atleast).

The lesser the connection objects you can manage the better.

But in your case 1000+ concurrent calls, you must consider using Connection-Pool with your approach-1. Approach-2+Connection-Pool can equally be used but additional cost of pool management would be present in contrast to approach-1.

Upvotes: 3

Related Questions