1 2 Previous Next 22 Replies Latest reply on Oct 7, 2009 11:23 PM by DrClap

    Need help on multi threading in JDBC connections

    807580
      Hi ALL,

      I have a input of nearly 6000 string each of having nearly 4000 length. For each string I have to do some pattern matching and store the results in MySql database.
      I have written a program which takes 2 sec to process one string and store the results into the database. But If I have to process 6000 strings using the same program, it will take 6000*2 seconds which is not I really want.

      I am using only one CPU with single processor. What I want is to create 6000 threads to processing 6000 strings and the whole program has to be completed with in 5 sec.

      Can any body please help me how to write such kind of multithread program in Java? Please give me sample code...

      Is there any open source library available using which I can create multiple threads?

      When I create 6000 threads, do I have to create 6000 independent DB connections or Do I need to use Connection pooler? If I need to use connection pooler for DB connections, Please suggest me one good pooler.

      Any body please help me, Its very urgent..

      Thank you,
      Sudheer Tumu.
        • 1. Re: Need help on multi threading in JDBC connections
          807580
          TUMU_ALBANY wrote:
          Can any body please help me how to write such kind of multithread program in Java? Please give me sample code...
          Just check the java.lang.Thread / java.lang.Runnable javadocs and tutorials here at sun.com. Code samples are available there. Google can find them.
          When I create 6000 threads, do I have to create 6000 independent DB connections or Do I need to use Connection pooler? If I need to use connection pooler for DB connections, Please suggest me one good pooler.
          Use connection pooling. Any API which has proven its robustness through years suffices. For example DBCP, C3P0 and Proxool.
          • 2. Re: Need help on multi threading in JDBC connections
            DrClap
            BalusC wrote:
            When I create 6000 threads, do I have to create 6000 independent DB connections or Do I need to use Connection pooler? If I need to use connection pooler for DB connections, Please suggest me one good pooler.
            Use connection pooling. Any API which has proven its robustness through years suffices. For example DBCP, C3P0 and Proxool.
            And whatever you do, under no circumstances should you try to share a single DB connection between two different threads.
            • 3. Re: Need help on multi threading in JDBC connections
              807580
              Hi BalusC ,

              Thank you very much for your reply..
              Ya... I searched the java.lang.Thread / java.lang.Runnable javadocs and examples, But I can only found examples for creating simple thread programs like implements runnable or extends thread demos and <class>.start().. like these kind of small examples.

              When I used these examples, the threads are taking more time than sequential programs. like.

              Say for 2 strings, my sequnce program is taking 4 seconds. But If I write two threads each processing one string, then that program is taking like 5-6 seconds(I am using single DB connection for two threads and the db access method is synchronized..).

              Please go through the sample code..
              public class Demo{
              public static void main (String args[]){
                    long startTime = System.currentTimeMillis();  
                      
                    LocalConn lcon = new LocalConn();
                    Connection con = lcon.makeConnection();
                      try {
                          con.setAutoCommit(false);
                      } catch (SQLException ex) {
                          ex.printStackTrace();
                      }
                    
                    MyThread m1 = new MyThread(con);
                    
                    
                    MyThread m2 = new MyThread(con);
                      try {
                          m1.t.join();
                          m2.t.join();
                      } catch (InterruptedException ex) {
                          ex.printStackTrace();
                      }
                    
                    
                      long endTime = System.currentTimeMillis();
                   long totalTime = (endTime-startTime)/1000;
                      
                   System.out.println( totalTime + " Seconds : ");
                    
                 }
              }
              
              public class MyThread{
              Connection con = null;
                MyThread (Connection con) {
                    this.con = con;
                    t = new Thread(this,"My thread");
                    t.start();
                 }
                  public void run() {
                     // some string processing here...
                      insert();
                  }
                 public synchronized void insert(){
                     
                     Statement stm = null;
                     try{
                          String sql = "insert into ";
                          
                          stm = con.createStatement();
                          for(int i=0;i<v.size();i++){
                              sql = "insert into xxxx values('"+ v.get(i)+"')";
                              stm.executeUpdate(sql);
                          }
                          for(int i=0;i<v1.size();i++){
                              sql = "insert into xxxx values('"+ v1.get(i)+"')";
                              stm.executeUpdate(sql);
                          }
                          con.commit();
                     }
                     catch(Exception e){
                          try {
                              con.rollback();
                          } catch (SQLException ex) {
                              ex.printStackTrace();
                          }
                         e.printStackTrace();
                         
                     }
                     finally{
                          try {
                              stm.close();
                          } catch (SQLException ex) {
                              ex.printStackTrace();
                          }
                     }
                 }
              }
              This is the sample program I have written... I think I have to use some concurrency concepts.. Please give me any code samples which consists of some multi threaded concurrency concepts...

              I have used c3P0 with hibernate.. can I use C3P0 with out hibdernate? I will evaluate C3P0... Please help me to create 6000 threads which can access the database parallel.6000 threads should complete in 2sec. Is it possible, each one processing one string? I have program which processes one string in 2sec.

              Thank you,
              Sudheer Tumu
              • 4. Re: Need help on multi threading in JDBC connections
                807580
                TUMU_ALBANY wrote:
                Say for 2 strings, my sequnce program is taking 4 seconds. But If I write two threads each processing one string, then that program is taking like 5-6 seconds(I am using single DB connection for two threads and the db access method is synchronized..).
                If you synchronize a method, threads have to wait on each other thread to finish with the particular method. They cannot invoke the method concurrently, which makes the whole multithreading idea completely superfluous and this would only add more overhead.
                I have used c3P0 with hibernate.. can I use C3P0 with out hibdernate? I will evaluate C3P0...
                C3P0 is just a connection pooling API. It's not part of Hibernate or so.
                • 5. Re: Need help on multi threading in JDBC connections
                  DrClap
                        MyThread m1 = new MyThread(con);
                        MyThread m2 = new MyThread(con);
                  Don't under any circumstances allow two threads to share the same connection, as I already said.

                  As for threading in general, it doesn't have anything in particular to do with JDBC. You should be able to pick up some examples from the many tutorials available on-line.

                  Also you seem to have misunderstood what you have read, because putting "synchronized" in your code where you did has no effect on anything at all. It isn't a magic keyword with "do-what-I-mean" properties, it actually works in a specific way. In particular you synchronize on an object, so two method calls with are synchronizing on different objects (as in your example) don't prevent each other from running at the same time at all.

                  Edit: oh yeah, one other thing: don't assume that using more than one thread to run a task will automatically speed things up. Actually you seem to be assuming that using two threads to do two units of work should take less time than one thread which only does one unit of work, which is an even less reasonable assumption.
                  • 6. Re: Need help on multi threading in JDBC connections
                    807580
                    Hi DrClap,

                    Thank you very much for your kind reply. Now I ran my program with two different Db Connections. And I removed synchronized key work for the 'insert' method.
                    MyThread m1 = new MyThread(con);
                    MyThread m2 = new MyThread(con1);
                    But even now, It has taken more time(300 sec) which is more than the time taken for sequential execution (200sec). Even If I use connection pooling, I will get different connections for different threads which may be approximately same as above.

                    and I forge to tell earlier that All of my threads should insert the data into same table.

                    So, I think there are some other areas in which I have to modify the code.I am missing some thing.
                    Can you please help me where I am missing to get better performance... Do I have to use threads to get better performance or any other alternatives? I need to process the 6000 strings and should insert the data into one table parallelly at a time to get better performance.
                    Please give me suggestions...

                    Thank you,
                    Sudheer Tumu.
                    • 7. Re: Need help on multi threading in JDBC connections
                      807580
                      Hi BalusC

                      Hi DrClap,

                      I removed synchronized key work for the 'insert' method. and I used different connections for two threads
                      MyThread m1 = new MyThread(con);
                      MyThread m2 = new MyThread(con1);
                      But even now, It has taken more time(300 sec) which is more than the time taken for sequential execution (200sec). Even If I use connection pooling, I will get different connections for different threads which may be approximately same as above. I mean connection pooler will help me to use different connections for different threads which I have done above.

                      and I forget to tell earlier that All of my threads should insert the data into same table.

                      So, I think there are some other areas in which I have to modify the code.I am missing some thing.
                      Can you please help me where I am missing to get better performance... Do I have to use threads to get better performance or any other alternatives? I need to process the 6000 strings and should insert the data into one table parallelly at a time to get better performance.
                      Please give me suggestions...

                      Thank you,
                      Sudheer Tumu.
                      • 8. Re: Need help on multi threading in JDBC connections
                        807580
                        There is a major flaw with your reasoning.

                        If you have a single CPU(assuming a single core processor here), you are only running 1 thread at a time, no matter how many threads you create. Thus you will still take 6000*2 seconds to complete, plus overhead from the threading code.

                        Even with a multi-core processor, you will take longer than 5 seconds - you would need over 2000 processors to complete the entire task in 5 second. (Ignoring the threading overhead again).
                        I am making the assumption that the database call is taking negligible time - if it is not, then you should consider some kind of batch process, where you write the results of multiple data runs at once.

                        So, what you should ask yourself is: "How can I speed up the pattern matching?"

                        Thus, I recommend doing some profiling - "instrument" your code to tell you how long different tasks are taking you, and try to figure out how you can speed things up.
                        • 9. Re: Need help on multi threading in JDBC connections
                          DrClap
                          BobC wrote:
                          I am making the assumption that the database call is taking negligible time...
                          Ouch. That's, um, not a very good assumption. The whole point of this exercise is that database calls do take a considerable amount of time.
                          • 10. Re: Need help on multi threading in JDBC connections
                            DrClap
                            Frankly I wouldn't write any threading code at all. I would use the new concurrency tools built into Java 5 instead. I haven't done this myself so I don't know the names of the classes to use, but here's the general idea:

                            (1) Set up a collection of objects which can process requests. Each of them would have their own database connection, the "request" would be an entry to be added to the database, and processing the request would consist of adding the entry to the database.

                            (2) Set up a queue. It will have one producer, your main program which sends a request for each entry to be added to the database. The collection of objects from (1) would be the consumers.

                            (3) Try this, choosing various sizes for the collection from (1).

                            You should be able to do this with classes from the java.util.concurrent package quite easily.
                            • 11. Re: Need help on multi threading in JDBC connections
                              807580
                              DrClap wrote:
                              BobC wrote:
                              I am making the assumption that the database call is taking negligible time...
                              Ouch. That's, um, not a very good assumption. The whole point of this exercise is that database calls do take a considerable amount of time.
                              That's why I gave direction on checking this assumption - determine how long the database calls are taking, to see if it's the bottleneck.

                              The point of this exercise is that he wants to do this 6000 2-second tasks in 5 seconds, and seems to think he can speed up a single processor by creating threads. Read the original post - everyone has been following the DB connection red herring, and ignoring the fundamental misunderstanding of how computers work.

                              And, as I said before, he should see how long individual tasks are taking, rather than everyone assuming that the DB connection is his problem.
                              • 12. Re: Need help on multi threading in JDBC connections
                                807580
                                I did a quick test: 6000 inserts of 4000 character strings took under 3 seconds.

                                It seems either the estimate of how long it would take is wrong, or something else is taking most of the time such as the string processing.
                                • 13. Re: Need help on multi threading in JDBC connections
                                  807580
                                  fgb wrote:
                                  I did a quick test: 6000 inserts of 4000 character strings took under 3 seconds.

                                  It seems either the estimate of how long it would take is wrong, or something else is taking most of the time such as the string processing.
                                  He appears to be doing his own pattern matching - which I suspect is taking up all his time. He is not inserting the strings directly, but rather inserting "the results" in the database.
                                  • 14. Re: Need help on multi threading in JDBC connections
                                    807580
                                    Hi BobC ,

                                    Thank you for your kind reply.

                                    You mean, you want me to do profiling my code using any profiler or you want me to test for memory leaks. I do not know any thing about profiling/memory leaking, that is why I am asking this question.

                                    Can you please suggest me any tools to do profiling. I am thiking of using NetBeans 6 to do profiling....

                                    Thank you,
                                    Sudheer Tumu.
                                    1 2 Previous Next