How the performance of a JavaFx-MySQL application can be enhanced

Question

In my JavaFx application, i'm loading an ObservableList when a button is clicked and then display the list in a table.

the controller code:

 @FXML
   private void initialize() throws SQLException, ParseException, ClassNotFoundException {

    searchChoice.setItems(criteriaList);
    searchChoice.getSelectionModel().selectFirst();
    productIdColumn.setCellValueFactory(cellData -> cellData.getValue().productIdProperty());
    unitColumn.setCellValueFactory(cellData -> cellData.getValue().unitProperty());
    productTitleColumn.setCellValueFactory(cellData -> cellData.getValue().titleProperty());
    productTypeColumn.setCellValueFactory(cellData -> cellData.getValue().typeProperty());
    productUnitPriceColumn.setCellValueFactory(cellData -> Bindings.format("%.2f", cellData.getValue().unitPriceProperty().asObject()));
    productQuantityColumn.setCellValueFactory(cellData -> cellData.getValue().quantityProperty().asObject());
    productStatusColumn.setCellValueFactory(cellData -> cellData.getValue().productStatusProperty());
    descriptionColumn.setCellValueFactory(cellData -> cellData.getValue().descriptionProperty());
    reorderPointColumn.setCellValueFactory(cellData -> cellData.getValue().reOrderPointProperty().asObject());
    surplusPointColumn.setCellValueFactory(cellData -> cellData.getValue().surplusPointProperty().asObject());
    productIdColumn.setSortType(TableColumn.SortType.DESCENDING);
    productTable.getSortOrder().add(productIdColumn);

    productTable.setRowFactory(tv -> new TableRow<Product>() {
        @Override
        public void updateItem(Product item, boolean empty) {
            super.updateItem(item, empty);
            if (item == null) {
                setStyle("");
            } else if (item.getQuantity() < item.getReOrderPoint()) {
                setStyle("-fx-background-color: tomato;");
            }  else if (item.getQuantity() > item.getSurplusPoint()) {
                setStyle("-fx-background-color: darkorange;");
            } else {
                setStyle("-fx-background-color: skyblue;");
            }
        }
    });

    try {
        ObservableList<Product> productData = ProductDAO.searchProducts();
        populateProducts(productData);
        String[] expireDate = new String[productData.size()];
        String[] id = new String[productData.size()];
        String[] existingStatus = new String[productData.size()];
        for (int i = 0; i < productData.size(); i++) {
            expireDate[i] = productData.get(i).getExpireDate();
            id[i] = productData.get(i).getProductId();
            existingStatus[i] = productData.get(i).getProductStatus();
            DateFormat format = new SimpleDateFormat(app.values.getProperty("DATE_FORMAT_PATTERN"), Locale.ENGLISH);
            Date expireDateString = format.parse(expireDate[i]);
            Date in = new Date();
            LocalDateTime ldt = LocalDateTime.ofInstant(in.toInstant(), ZoneId.systemDefault());
            Date today = Date.from(ldt.atZone(ZoneId.systemDefault()).toInstant());
            if (expireDateString.before(today) && !existingStatus[i].equals(app.values.getProperty("STATUS_TYPE2"))) {
                ProductDAO.updateProductStatus(id[i], app.values.getProperty("STATUS_TYPE3"));
            }
            if (expireDateString.after(today) && !existingStatus[i].equals(app.values.getProperty("STATUS_TYPE2"))) {
                ProductDAO.updateProductStatus(id[i], app.values.getProperty("STATUS_TYPE1"));
            }
        }
        ObservableList<Product> productDataRefreshed = ProductDAO.searchProducts();
        populateProducts(productDataRefreshed);

        ObservableList<Product> productCodesData = ProductDAO.getProductCodes();
        ObservableList<Product> productTitlesData = ProductDAO.getProductTitles();
        ObservableList<Product> productTypesData = ProductDAO.getProductTypes();
        ObservableList<Product> productStatusData = ProductDAO.getProductStatus();
        String possibleProducts1[] = new String[productCodesData.size()];
        for (int k = 0; k < productCodesData.size(); k++) {
            possibleProducts1[k] = productCodesData.get(k).getProductId();
        }
        String possibleProducts2[] = new String[productTitlesData.size()];
        for (int k = 0; k < productTitlesData.size(); k++) {
            possibleProducts2[k] = productTitlesData.get(k).getTitle();
        }
        String possibleProducts3[] = new String[productTypesData.size()];
        for (int k = 0; k < productTypesData.size(); k++) {
            possibleProducts3[k] = productTypesData.get(k).getType();
        }

        String possibleProducts4[] = new String[productStatusData.size()];
        for (int k = 0; k < productStatusData.size(); k++) {
            possibleProducts4[k] = productStatusData.get(k).getProductStatus();
        }

        TextFields.bindAutoCompletion(searchField, possibleProducts1);
        TextFields.bindAutoCompletion(searchField, possibleProducts2);
        TextFields.bindAutoCompletion(searchField, possibleProducts3);
        TextFields.bindAutoCompletion(searchField, possibleProducts4);

    } catch (SQLException e) {
        Alert alert = new Alert(Alert.AlertType.ERROR);
        alert.setTitle(app.values.getProperty("ERROR_TITLE"));
        alert.setHeaderText(app.values.getProperty("FAILURE_MESSAGE"));
        alert.setHeaderText(app.values.getProperty("ERROR_GETTING_INFORMATION_FROM_DATABASE_MESSAGE"));
        alert.showAndWait();
        throw e;
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }

}

the service mysql query :

  public static ObservableList<Product> searchProducts() throws SQLException, ClassNotFoundException {
    String selectStmt = "SELECT * FROM product";
    ResultSet rsPrdcts = DbUtil.dbExecuteQuery(selectStmt);
    ObservableList<Product> productList = getProductList(rsPrdcts);
    return productList;
}

The issue here is, when there are more than 200-300 items in the list the scene gets really slow to load. What countermeasures can I take regarding this matter? Any idea will be very much appreciated. Thanks in advance.

Answer 1

You need to implement an ObservableList which only retrieves the data which is rqeusted by the TableView. Currently you retrive all elements in the table and cast the retrieved list to an ObservableList. The TableView uses the .get(int idx) method of the OL to retrieve all items which should be displayed and the .size() method for determining the size of the scrollbar. When you scroll the TableView will discard all items which are not displayed and call the get method again.

To solve your problem need to create a class which implements ObservableList<E>. First you need to implement the .get(int idx) and the .size() method, for all other methods I would throw new UnsupportedOperationException() and later on see which other method is needed. So the .size() method needs to execute the following query

SELECT count(*) FROM product

and the get(int idx) something like this

int numItems = 30;
int offset = idx - (idx % numItems)
SELECT * FROM product LIMIT offset, numItems

you can create an internal list which only holds e.g. 30 items from your db and whenever the requested idx < offset || idx > offset + numItems you issue a new db request.

I used this this approach with database tables with millions of rows and had a very performant GUI. You can also add paging to the TableView because with to many rows the scrollbar gets useless, but this is a different discussion.

edit:

I forgot to mention that this is called Lazy Loading