We are using a grid with lazy loading and the backend with JDBC to load the grid.
As shown in the example below, with setPageSize = 5 and considering that there are 25 records in the database, Vaadin automatically triggers getUsers() 5 times, when what we expect is for it to load the first 5 records and wait for a user action, such as selecting the grid row or using the scroll button.
What we are doing wrong: Grid grid = new Grid<>(User.class, false);
grid.setPageSize(5);
grid.setHeight(“300px”);
grid.addThemeVariants(GridVariant.LUMO_COMPACT);
grid.setSelectionMode(SelectionMode.SINGLE);
grid.addColumn(User::getId).setHeader(“ID”);
grid.addColumn(User::getNome).setHeader(“Nome”);
SQL Log
select * from user u where limit 5
select * from user u where offset 5 limit 5
select * from user u where offset 10 limit 5
select * from user u where offset 15 limit 5
select * from user u where offset 20 limit 5
Now I understand the behavior, about doing an initial load to improve the user’s perception, I noticed that this happened up to record 25, that is, it loaded the first 25 records and then it loaded the others as I scrolled down the Grid.
In the test, I have 84 records in the database, I used the scroll until I loaded all the records, the last SQL executed was the one below and the grid had 84 rows, as expected.
select * from user u where offset 80 limit 5
However, when moving the scroll up and then down a few times, the call to the back was triggered twice, according to the SQL below and the grid had only 21 rows.
select * from user u where u.name ilike ‘%ma%’ offset 85 limit 5
select * from user u where u.name ilike ‘%ma%’ offset 90 limit 5
I didn’t understand, because the other rows were removed from the grid.
The code is exactly this below, the getUsers method makes a call passing offset and limit, which executes the SQL according to the logs I have previously passed. I have also attached a video to demonstrate the behavior.
Follows the log of SQL executed during this operation
select * from user u order by id limit 5
select * from user u order by id offset 5 limit 5
select * from user u order by id offset 10 limit 5
select * from user u order by id offset 15 limit 5
select * from user u order by id offset 20 limit 5
select * from user u order by id offset 25 limit 5
select * from user u order by id offset 30 limit 5
select * from user u order by id offset 35 limit 5
select * from user u order by id offset 40 limit 5
select * from user u order by id offset 45 limit 5
select * from user u order by id offset 50 limit 5
select * from user u order by id offset 55 limit 5
select * from user u order by id offset 60 limit 5
select * from user u order by id offset 65 limit 5
select * from user u order by id offset 70 limit 5
select * from user u order by id offset 75 limit 5
select * from user u order by id offset 80 limit 5 select * from user u order by id offset 85 limit 5 select * from user u order by id offset 90 limit 5
The information about small page sizes seems strange to me, since the goal is to actually trigger traffic only when the user demands it. In the current application we have with Vaadin 7, we always load records in a Table in increments of 10.
Just a general note. SQL queries do have some constant overheads, hence it hardly pays of to query very small number of rows at the time. The default page size of Grid is 50, which I think is quite close to optimum, which may range between 30 - 100 depending on your application and data model. Going for less rows at the time actually reduces the performance.
I tried your initial code (without estimates or count query), and I am unable to reproduce the issue.
Having 84 records, I never get a request with an offset greater than 80.
The only reason that comes to mind for the additional requests is that with offset grater than 80, the query is still returning some record.
Can you share the getUsers(...) code? Is it just a simple select statement with offset and limit?
BTW, it looks like the issues with small page size have been fixed in recent Vaadin versions.
Hi,
I changed the code to consider the pagesize as 50 and now the code is as below, the problem when scrolling up continues.
Note that it is a very basic implementation with a very simple SQL and I really don’t understand why scrolling up triggers new calls, since I am browsing data already present in the grid, I understand that this should only happen when scrolling down and if I get close to the end of the lines already loaded.
Here are the SQL statements executed and at what point Executed when starting the grid
select * from user u order by id limit 50
Executed when scrolling down the grid, loading all records that are in the database, which are currently 118
select * from user u order by id offset 50 limit 50
select * from user u order by id offset 100 limit 50
Executed when I scroll up the grid and no records are returned
select * from user u order by id offset 150 limit 50
Regarding equals/hashcode, we use the ID of the record in the database, which is the primary key. The User class extends BMEntity
@Override
public int hashCode() {
return Objects.hash(id);
} @Override public boolean equals(Object obj) { if (this == obj) return true;
if (obj == null) return false;
if (getClass() != obj.getClass()) return false;
BMEntity other = (BMEntity) obj;
return Objects.equals(id, other.id);
}
Probably, I still do not understand your issue, but given that you are not providing to the grid a way to know how many rows are there in total, I think is it expected that an additional request is done, to reach the point the callback does not return any row.
If you provide a count callback, you should not see any the additional query, with the cost of the count query being executed when data is refreshed.
I created a small project with an example implementation with the problem that is happening, however, here it does not allow sending, where can I forward it?
