Framework: SQLContainer reads entire table upon scroll

[vaadin-bot]

Originally by _dolh_

---

I have around 300k records in Database that i want to populate in a table, so i used SQLContainer+FreeformQuery to help the table to perform lazy loading.

The problem is, whenever i scroll down too fast or directly jump to certain level, it just keeps loading data from Database endlessly. The process keeps on going even after the browser has been closed.

I tried in Vaadin 6.8.9, and everything's fine.

There's another problem for IE which appears in both Vaadin6.8.9 and Vaadin 7.The scrollbar in IE can only reach around 150k records and when it reach that level, no record is displayed.

---

Imported from https://dev.vaadin.com/ issue #11199

[vaadin-bot]

Originally by _snitsche_

---

I have the same problem with Vaadin 7.0.2 and found out a bit more:
The problem occurs if the SQLContainer is forced to page multiple times (for example by fast scrolling).

This creates an infinite-loop in SQLContainer.indexOfId()-Function (Lines 633-647):
An itemId of the 1st call is searched, but is (of course) not present in the current itemIndexes map because of the 2nd call. The function then caculates the "nextIndex" which is actually the current index and tries to updateOffsetAndChache.

This is dropped because it is already active, but the itemId is still not found (it belongs to previous index-page).

The bug can be fixed by adding an oldIndex-Variable and leaving the loop when nextIndex == oldIndex, but I'm very new to vaadin (started yesterday looking into it) so I don't know if the main problem might be a different.

Anyways, it should not lead to an infinite-loop if indexOfId() is called upon an object which is part of a previous page.

Hope this helps.

Edit: Seems to be a bigger problem related to caching etc. I'm not experienced enough with vaadin atm to fix it.

[vaadin-bot]

Originally by _kernwig_

---

Ran into this problem today, running v7.1.8. I was planning to take this new Vaadin 7 upgrade to production next week; now I'm dead in the water due to this bug.

[vaadin-bot]

Originally by _kernwig_

---

Discussion and possible fix: https://vaadin.com/forum#!/thread/1938138

[vaadin-bot]

Originally by _kernwig_

---

Attachment added: vaadin11199-log-output.log (104.6 KiB)
Turned on logging in com.vaadin.data. Page size set to 75. Initial load came up okay, with two fetches. Then scrolled to 151st row, resulting in over 800 database quaries.

[vaadin-bot]

Originally by _kernwig_

---

Attachment added: vaadin11199-log2.log (31.6 KiB)
Log output from pulling up and slowly scrolling down a small table.

[vaadin-bot]

Originally by _Olli_

---

The error happens when the user scrolls to a place where the SQLContainer cache page changes so that the first items fetched from the container belong to a different page than the last items. Using a table, the table first gets all the item id's for a given range, causing the cache page to update. When the table renders the cells, the first items are no longer in the cache and the container does essentially a table scan to find the correct items.

This could be remedied by allowing some overlap in the cache pages, so instead of loading a completely new page on each update have enough overlap to ensure that the previous page does not need to be accessed. However, the required overlap depends on the Table's page size, which the container is unaware of, thus completely overcoming the problem is quite hard.

[vaadin-bot]

Originally by _kernwig_

---

Three months ago I gave up on SQLContainer as unfixable and switched to Lazy Query Container. I do still use some utility classes of SQLContainer to construct my filters though.

[vaadin-bot]

Originally by @tsuoanttila

---

What database are you using with SQLContainer? It is possible there are issues with specific DB as I am unable to reproduce this bug. Cache paging has some issues, but not directly related directly to scrolling though the entire table.

[vaadin-bot]

Originally by _snitsche_

---

I'm using an Oracle 10g and 11g and can reproduce the behaviour any time I scroll a big table (> 1000 entries)

[vaadin-bot]

Originally by _kernwig_

---

I was using MySQL 5.5, but in my debugging I don't see how that matters. You do want to test with a large result set. Log the offset and limit requests, and scroll through the table.

[Pereitor]

It would be nice to know why this issue was closed without furter notice nor justification, specially when it has been reported dozens of times and it's very easy to reproduce.

[tsuoanttila]

This was before we had the habit of linking code changes from review to tickets. https://dev.vaadin.com/review/#/c/2670/

If I remember correctly from 2014, the issue was that SQLContainer was not actually caching the "previous" page of data and scrolling upwards caused the read through all of data every time. It still happens if you scroll too far or ask for index of item, but the biggest issue affecting normal use was addressed.