title: JavaFx Tableview speed and sorting in Sql application
body: <p>I am working on a small, toy application to expand my knowledge about <code>Java</code> <code>JavaFx</code> and <code>Sql</code>. I have a MySQL server in my local network, which I am able to communicate with and a simple Tableview can be populated, sorted ... etc. The data has only to be shown to the user, no editing. Everything nice and clean.</p>
  
  <p><strong>The Problems:</strong></p>
  
  <ul>
  <li><p>There are around 170 000 rows with 10 col., all <code>chars</code>, to display, which seems to be rather hard to do in reasonable time. The query is done during startup and it take around 1 1/2 min before I can see the table.</p></li>
  <li><p>Also the memory footprint is enormous, the application without populated Tableview around 70 mb, with all the data it has 600-700 mb (the xml file which is used to populate the mysql is 70 mb in size ... ) !</p></li>
  <li><p>Sorting is REALLY slow, I am using Stringproperty which should give a boost according to: <a href="https://stackoverflow.com/questions/16805845/javafx-tableview-sort-is-really-slow-how-to-improve-sort-speed-as-in-java-swing">JavaFx tableview sort is really slow how to improve sort speed as in java swing</a> (If I understood that correctly) However the custom sort, I did not try so far.</p></li>
  </ul>
  
  <p><strong>My thoughts:</strong></p>
  
  <p>Similar to the application design for mobile, I think an adapter-pattern can fix these problems. Hence, I create an <code>OberservableList</code> with the correct size of elements, but only populate a limit of rows in the beginning. When I am scrolling done (scroll wheel) the List has to be updated with new elements in advance via <code>sql-queries</code>. This should give me a performance boost for the first Problem. Nice idea but what am I going to do if the user is going to scroll done via the scrollbar(click and drag down), then I would skip certain entries, but I need the information to give the user the feedback where to scroll to.
  How could I fix this ?</p>
  
  <p>For the sorting, I would use the sql sorting methods, so each sort will be performed on the sql server and a new OberservableList will be created. As before, only a certain amount of data would be loaded in the first query.</p>
  
  <p>If this approach would also effect the memory footprint, I am not sure.</p>
  
  <p><strong>Your opinion:</strong>
  Are my ideas reasonable and do-able in <code>Java</code>, <code>JavaFx</code> ?
  I would love to hear your ideas about these problems.</p>
  
  <p>Thank you.</p>