title: Is it good to open a new database connection or keep a connection open until process completes?
body: <p>I have 2 databases <code>old</code> &amp; <code>new</code>, <code>old</code> db detail needs to be filtered/manipulated and stored into <code>new</code>. </p>
  
  <blockquote>
    <p><code>OLD DB</code></p>
  </blockquote>
  
  <ol>
  <li><p>I have around 10000 configurations (DB rows)</p></li>
  <li><p>and 10000 BLOBS (xml file size is 4MB on an average) matching config id's from above</p></li>
  </ol>
  
  <blockquote>
    <p><code>NEW DB</code></p>
  </blockquote>
  
  <ol>
  <li><p>1 new table that is going to contain the filtered data from old, but this time no BLOB data, instead absolute paths</p></li>
  <li><p>and per configuration some recommendations</p></li>
  </ol>
  
  <p>Here I wrote a program (using Groovy &amp; MyBatis for DB) which gets all the configuration records available in <code>OLD DB</code> and stores in a List of class and the DB Connection is closed</p>
  
  <p>In order to fetch the BLOBS too for each config id, a new connection is established &amp; is kept open for a long time</p>
  
  <pre><code>List&lt;String&gt; projectid
  List&lt;CSMConfigInfo&gt; oldConfigs
  List&lt;ConfigInfo&gt; newConfigs 
  Map&lt;String,CSMConfigInfo&gt; oldConfigMap
  
  SqlSession session = DatabaseConnectivity.getOldCSMDBSessionFactory().openSession()
  /*  trying to batch execute based on project id */
      projectid.each {pid-&gt;
          logger.info "Initiating conversion for all configuration under $pid project id"
          oldConfigMap.each {k,v-&gt;
  /*  Here I am keeping a DB connection open for a long time */           
              if(pid.equals(v)){                  
                  createFromBlob(k,session)                   
              }
          }                       
          logger.info "Completed for $pid project id\n"           
      }
  
  session.close()
  </code></pre>
  
  <p>After fetching the BLOB 1 by 1, I create a temp xml file which is parsed to apply the filter for inserting into <code>NEW DB</code>. Below code you can see that based on whether xml is convertible and parsible a new connection is opened for <code>NEW DB</code>. Is this good practice or do I need to keep the <code>NEW DB</code> connection open for all 10000 records?</p>
  
  <pre><code>/* XML is converted to new format and is parsable */
  def createFromBlob(CSMConfigInfo cfg,SqlSession oldCSMSession){
      .
      .
  
      if(xmlConverted&amp;&amp;xmlParsed){
          //DB Entries
          try{
              /* So now here I am opening a new connection for every old config record, which can be 10000 times too, depending on the filter */
              SqlSession sess = DatabaseConnectivity.getNewCSMSessionFactory().openSession()
  
              //New CSM Config
              makeDatabaseEntriesForConfiguration(newConfig,sess)
              //Fire Rules
              fireRules(newConfig,sess,newCSMRoot)
  
              sess.close()
  
          }
          catch(IOException e){
              logger.info "Exception with ${newConfig.getCfgId().toString()} while making DB entries for CONFIG_INFO"
          }
          logger.info "Config id: "+cfg.getCfgId().toString()+" completed successfully, took "+getElapsedTime(startTime)+ " time. $newabspath"
      }
      else{
          def errormsg = null
          if(!xmlConverted&amp;&amp;!xmlParsed)
              errormsg = "Error while CONVERSION &amp; PARSING of config id "+cfg.getCfgId().toString()+", took "+getElapsedTime(startTime)+ " time."
          else if(!xmlConverted)
              errormsg = "Error while CONVERSION of config id "+cfg.getCfgId().toString()+", took "+getElapsedTime(startTime)+ " time."
          else if(!xmlParsed)
              errormsg = "Error while PARSING  of config id "+cfg.getCfgId().toString()+", took "+getElapsedTime(startTime)+ " time."
          logger.info errormsg
      }
  
      makeDatabaseEntriesForConvertStatus(csmConfigConvertStatus,oldCSMSession)
  }
  </code></pre>
  
  <p>This currently works for 20 records, but I am not sure how will it react for all 10000 records. Please help</p>
  
  <p><strong>Update</strong></p>
  
  <p>I takes about 3-6 secs for each config</p>