title: Improve performance in JDBC query - ArrayList comparison
body: <p>I've been trying to get the most efficient way to get a set of values from a DB and comparing them with their respective sub-string to save specific values... Here is an example: <br>
  I have this table which contains a PRODUCT(ID), from the ones in LVL - 7  I have to substring the values until I get to LVL1</p>
  
  <pre><code>LVL     PRODUCT          DESCRIPTION
  5       01AR2TGELL       DESLVL5
  6       01AR2TGELLGS     DESLVL6
  7       01AR2TGELLGSQKA  DESLVL7
  6       01AR2TGELLDE     DESLVL6
  7       01AR2TGELLDEUDP  DESLVL7
  7       01AR2TGELLDEUZN  DESLVL7
  </code></pre>
  
  <p>So I can get something like this:</p>
  
  <pre><code>LVL   PRODUCT          DESCRIPTION  | LVL   PRODUCT        DESCRIPTION  | LVL  PRODUCT     DESCRIPTION
  7     01AR2TGELLGSQKA  DESLVL7      | 6     01AR2TGELLGS   DESLVL6      | 5    01AR2TGELL  DESLVL5
  7     01AR2TGELLDEUDP  DESLVL7      | 6     01AR2TGELLDE   DESLVL6      | 5    01AR2TGELL  DESLVL5
  7     01AR2TGELLDEUZN  DESLVL7      | 6     01AR2TGELLDE   DESLVL6      | 5    01AR2TGELL  DESLVL5
  </code></pre>
  
  <p>I'm working with Java (Requirement) and also I'm using JDBC to connect to the Oracle DB. What I did is to get all the values from LVL7 with a query and save all those values in an ArrayList then, with a LVL and a Value of the PRODUCT Substring I compare the values, like this:</p>
  
  <pre><code>"select * from PRODHIERARCHY where LVL=7"
  "select * from PRODHIERARCHY WHERE LVL= 6 AND PRODUCT_HIERARCHY="+"'"+descLv7.get(j).substring(0, descLv7.get(j).length() - 4)+"'"
  </code></pre>
  
  <p>My problem is, there is approx. 35 thousand LVL7 values, and in total around 65 thousand so this is very inefficient. (To get the 35 thousand LVL7 values takes around 3 minutes since the DB is processing everything, when it gets to the comparison with the ArrayList it takes around 30 minutes per LVL).</p>
  
  <p>Can anyone please recommend me a way to modify either the query or my comparison process  so it takes about the same time as the first query? Thanks!</p>