title: How to increase UPDATE speed, java connected to sql or using cursor
body: <p>I am trying to increase the speed of UPDATE using jdbc. There are 300000 rows, and I want to change the column of X of each row to 5. But I needed to make them sorted according to their primary keys. Because there are 400000 rows and I want to change 300000 rows from top.</p>
  
  <p>Here is what I did so far:</p>
  
  <p>First I tried to send every query when it is created using prepared stetament</p>
  
  <pre><code>    long time = System.currentTimeMillis();
      System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date (time)));
  
      for(int i=number-1; i&gt;=0; i--){
          try{
              pStmt.setInt(1, (int) hashMap.get(i));
              time = System.currentTimeMillis();
              result = pStmt.executeUpdate();
              time = System.currentTimeMillis() - time;
              totalTime += time;
          }catch(Exception e){}
      }
  
      System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date (System.currentTimeMillis())));
      time = System.currentTimeMillis() - time;
  </code></pre>
  
  <p>But it took many time. About 900 sec.</p>
  
  <p>Then I tried to add them to a batch, using addBatch()</p>
  
  <p>java.sql.PreparedStatement pStmt2 = conn.prepareStatement(query);</p>
  
  <pre><code>    for(int i=0; i&lt;number; i++){
              pStmt2.setInt(1, (int) hashMap.get(i));
              pStmt2.addBatch();
      }
  
      long time = System.currentTimeMillis();
      System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date (time)));
  
      pStmt2.executeBatch();
  
      System.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS").format(new Date (System.currentTimeMillis())));
      time = System.currentTimeMillis() - time;
  </code></pre>
  
  <p>It was better but not that better. Only there was a 50 sec. difference.</p>
  
  <p>And I tried to execute blocks. That means: there are 300000 rows and for every 30000 I added queries to a batch, then execute them. And I did this 10 times.</p>
  
  <p>It was much better but still too slow. It was about 800 sec</p>
  
  <p>Then I want to do this process with small rows such as 10000, in one line:</p>
  
  <p>Update tabel1 set outcome=3 order by key DESC limit 10000.</p>
  
  <p>which takes first 10000 rows, and change every outcome cells of them. This took about 24sec. This time is so close to other tests when I tried themn with 10000 rows.</p>
  
  <p>Then I tried to do this process with using cursor. Updating 10000 rows only took 4 secs.</p>
  
  <p>The result is interesting. </p>
  
  <p>But I want to update them using jdbc. Is there any other ways to do it? And why cursor is too speed?</p>