title: database design to speed up hibernate querying of large dataset
body: <p>I currently have the below tables representing a bus network mapped in hibernate, accessed from a Spring MVC based bus route planner 
  I'm trying to make my route planner application perform faster, I load all the above tables into Lists to perform the route planner logic.</p>
  
  <p>I would appreciate if anyone has any ideas of how to speed my performace
  Or any suggestions of another method to approach this problem of handling a large set of data</p>
  
  <pre><code>    Coordinate Connections Table (INT,INT,INT)( Containing 50,000 Coordinate Connections)
      ID, FROMCOORDID, TOCOORDID
      1   1               2       
      2   1               17      
      3   1               63      
      4   1               64      
      5   1               65      
      6   1               95      
  
      Coordinate Table (INT,DECIMAL, DECIMAL) (Containing 4700 Coordinates)
      ID ,  LAT,       LNG
      0   59.352669   -7.264341
      1   59.352669   -7.264341
      2   59.350012   -7.260653
      3   59.337585   -7.189798
      4   59.339221   -7.193582
      5   59.341408   -7.205888
  
      Bus Stop Table (INT, INT, INT)(Containing 15000 Stops)
      StopID    RouteID COORDINATEID
      1000100001  100     17  
      1000100002  100     18  
      1000100003  100     19  
      1000100004  100     20  
      1000100005  100     21  
      1000100006  100     22  
      1000100007  100     23  
  </code></pre>
  
  <p>This is how long it takes to load all the data from each table:</p>
  
  <pre><code>    stop.findAll  = 148ms,  stops.size: 15670
      Hibernate: select coordinate0_.COORDINATEID as COORDINA1_2_, coordinate0_.LAT as LAT2_, coordinate0_.LNG as LNG2_ from COORDINATES coordinate0_
      coord.findAll =  51ms , coordinates.size: 4704
      Hibernate: select coordconne0_.COORDCONNECTIONID as COORDCON1_3_, coordconne0_.DISTANCE as DISTANCE3_, coordconne0_.FROMCOORDID as FROMCOOR3_3_, coordconne0_.TOCOORDID as TOCOORDID3_ from COORDCONNECTIONS coordconne0_
      coordinateConnectionDao.findAll  = 238ms ; coordConnectioninates.size:48132
  </code></pre>
  
  <p>Hibernate Annotations</p>
  
  <pre><code>    @Entity
      @Table(name = "STOPS")
      public class Stop implements Serializable {
  
          @Id
          @GeneratedValue
          @Column(name = "COORDINATEID")
          private Integer CoordinateID;
  
  
          @Column(name = "LAT")
          private double latitude;
  
  
          @Column(name = "LNG")
          private double longitude;
  
      }
  
  
  
  
      @Table(name = "COORDINATES")
      public class Coordinate {
  
          @Id
          @GeneratedValue
          @Column(name = "COORDINATEID")
          private Integer CoordinateID;
  
  
          @Column(name = "LAT")
          private double latitude;
  
  
          @Column(name = "LNG")
          private double longitude;
      }
  
      @Entity
      @Table(name = "COORDCONNECTIONS")
      public class CoordConnection {
  
          @Id
          @GeneratedValue
          @Column(name = "COORDCONNECTIONID")
          private Integer CoordinateID;
  
          /**
           * From Coordinate_id value
           */
          @Column(name = "FROMCOORDID", nullable = false)
          private int fromCoordID;
  
          /**
           * To Coordinate_id value
           */
          @Column(name = "TOCOORDID", nullable = false)
          private int toCoordID;
          //private Coordinate toCoordID;
      }
  </code></pre>
  
  <p>HashMap -> CoodinateID to Coordinate code</p>
  
  <pre><code>private void setupCoordinateIDToCoordinate() {
      HashMap&lt;Integer, Coordinate&gt; coordinateIDToCoordinate = new HashMap&lt;Integer, Coordinate&gt;();
      List&lt;Coordinate&gt; coordinates = coordinateDao.findAll();
      Iterator &lt;Coordinate&gt; itr = coordinates.iterator();
      Coordinate c;
      while(itr.hasNext()) {
          c = itr.next();
          coordinateIDToCoordinate.put(c.getCoordinateID(),c);
      }
  }
  </code></pre>