Hi,
I’m new to Vaadin and working on a simple single-page app. Unfortunately the UI seems to be extremly slow (like >10sec for only a selectView) when I try to load data from postgresDB using JDBC. I thought the problem was the the amount of rows (~5000) so I implemented lazy loading with DataProviders using this tutorial:
[https://vaadin.com/docs/v13/flow/binding-data/tutorial-flow-data-provider.html]
(http://)
This did not solve the performance problem, especially since the Select Component sets the limit to 2147483647 (2^31-1) which renders this entire endeavour useless. Benchmarking the SQL-queries showed that the queries shouldn’t be the problem anyway (~150ms). However, everytime I click on a view that embedds a component that loads data, the java-process consumes like 200% CPU cycles for 10-30 sec.
Here is a bit of code:
ServiceProvider Interface
public interface ServiceProvider {
List <?> fetch(int offset, int limit);
int getCount();
}
Concrete Implementation of AirlineServiceProvider
public class AirlineServiceProvider implements ServiceProvider {
private static Connection connection = null;
public AirlineServiceProvider() {
if (connection == null) AirlineServiceProvider.connection = new DbManager().getConnection();
}
@Override
public List<Airline> fetch(int offset, int limit) {
String sqlAirline = "select * from slotmachine.airline order by airline_alias\n";
if (offset != 0 && limit != 0) sqlAirline = sqlAirline + "offset ? limit ?";
PreparedStatement pstmt = null;
List<Airline> airlineList = new ArrayList<>();
try {
pstmt = connection.prepareStatement(sqlAirline);
if (offset != 0 && limit != 0) {
pstmt.setInt(1, offset);
pstmt.setInt(2, limit);
}
ResultSet res = pstmt.executeQuery();
while (res.next()) {
String name = res.getString(2);
String alias = res.getString(3);
String country = res.getString(4);
Airline airline = new Airline(name, alias, country);
airlineList.add(airline);
}
pstmt.close();
res.close();
} catch (Exception e) {
e.printStackTrace();
}
return airlineList;
}
@Override
public int getCount() {
return 1000; //just for testing
}
}
The view to render
public class ContentView extends HorizontalLayout {
public void drawFlights() {
AirlineServiceProvider alsp = new AirlineServiceProvider();
DataProvider<String, Void> dataProviderAirline = DataProvider.fromCallbacks(
query -> {
int offset = query.getOffset();
int limit = query.getLimit();
List<Airline> al = alsp.fetch(offset, limit);
return al.stream().map(Airline::getAlias);
},
query -> alsp.getCount());
Select<String> airlineSelect = new Select<>();
airlineSelect.setLabel("Airline");
airlineSelect.setDataProvider(dataProviderAirline);
add(airlineSelect);
}
}
Airline class
public class Airline {
private String name;
private String alias;
private String country;
public Airline(String name, String alias, String country) {
this.name = name;
this.alias = alias;
this.country = country;
}
public String getName() {
return name;
}
public String getAlias() {
return alias;
}
public String getCountry() {
return country;
}
@Override
public String toString() {
return "Airline{" +
"name='" + name + '\'' +
", alias='" + alias + '\'' +
", country='" + country + '\'' +
'}';
}
}
I hope you have an idea 