GuglielmoCancelliParticipantJune 15, 2018 at 10:22 amPost count: 19
we successfully implemented the data virtualization on a datagrid (Xceed.Wpf.DataGrid.DataGridControl). However, we encountered the following issue, that I’ll try to explain with the following example:
– I start scrolling the grid
– the QueryItems requests the data (Data1)
– I keep scrolling
– the QueryItems requests new data (Data2, while Data1 still didn’t get back from db)
– I keep scrolling
– the QueryItems requests new data, etc.
Due to a heavy query load it takes 7-8 seconds to calculate and return the data to QueryItems. When several QueryItems requests overlap,the response time of a single request increases until the query reaches the timeout limit.
At that point the application encounters the exception and the pages that expected to receive DataN remains blank (inside of DataGrid).
The issue is even more pronounced when we try to scroll large portions of the grid with a scrollbar (and not with a mouse wheel).
Is there a way to control the frequency of QueryItems requests (or results) during the scrolling, discarding explicitly some of them? IMHO it’s useless to requests pages 2,3,4….8 if I decided to scroll directly to the page 10.
We tried to tweak the parameters found in the documentation (PageSize, PreemptivePageQueryRatio, MaxRealizedItemCount) but nothing helped. Increasing the page size only delayed the issue a little bit.
Moreover, we noticed that QueryItems sometimes (in the above scenario) requires a page multiple times, going backwards (page request flow: 1,2,1,3,4).
So, is there a way to skip/abort some page requests?Diane [Xceed]ModeratorJune 15, 2018 at 7:05 pmPost count: 1353
Unfortunately, there is not a lot of ways to configure the behavior beyond the PageSize, PreemptivePageQueryRatio and MaxRealizedItemCount properties.
The DataGridControl and the DataGridVirtualizingCollectionView are 2 different beasts. The DataGridControl displays rows and asks data items to the DataGridVirtualizingCollectionView, while the DataGridVirtualizingCollectionView requests data items to the remote data source by rising some events. The collection view is not aware of what is displayed on screen. It is not its duty to know about such things.
Since the DataGridControl may request some items near the last item requested, the DataGridVirtualizingCollectionView has a concept of caching and preemptive fetching. The properties that alter the behavior are PageSize, PreemptivePageQueryRatio and MaxRealizedItemCount. Basically, the PageSize tells how many items are contained in a cached page. If the DataGridControl asks for a single item, the collection will query a full page containing the item to the remote data source. The PreemptivePageQueryRatio tells the collection view when to query for a page next to the current one so the grid may wait less before displaying data while scrolling near the current page. The MaxRealizedItemCount indicates the collection view cache size. For example, a MaxRealizedItemCount of 1000 items for a PageSize of 200 items tells the collection view to keep 5 pages of items (mostly 5 QueryItems request) in cache all the time.
In the previous configuration, scrolling down will request pages of items to the remote data source. Since there is about 5 pages that fits in cache, the collection view will start aborting item queries on the 6th page request while there is still 5 requests pending. Lowering the number of page allowed in cache will abort items query sooner. However, lowering the number pages in cache also means that scrolling up and down has more chance to requery the same pages since any previously cached page may have been kicked out of cache.
If you scroll with the thumb, setting the DataGridControl.ItemScrollingBehavior property to ItemScrollingBehavior.Deferred will lower the number of requests since the grid will wait for the thumb to be released before requesting data items.
Since you are the one who is converting the collection view requests into queries to your data source, you may throttle the queries and reorder them as long as you tell the collection view that query result will be given back asynchronously (set the event args IsAsync property to true).
If you tell that the query result is going to be asynchronous, you have control about when you send the queries to your data source. For example, while scrolling, the collection view may ask for pages 1, 2, 3, 4, 5. Nothing prevents you from throttling the queries and queue them up so only one is done at a time on your data source. You are also in control of the query order.
For example, on your side, you may decide that you will always wait 100 milliseconds before querying your data source. If within that 100 ms, the collection view requested pages 1, 2 and 3, you may decide to only execute the query for page 3 and put the other 2 in a queue. You may also decide to query your data source right away, but only do it 1 request at a time. After the query to your data source returns, you will just pick one of the remaining query in queue and launch it. However, if in the mean time, the collection view request pages 4 and 5, you may also put them in the queue and always decide to execute to most recent one. That way, you give more chance to the collection view to abort old queries.
The final sequence could look like this:
– The collection view requests pages 1, 2 and 3.
– You execute the query for page 3 on your data source since it is the more recent one.
– In the meantime, the collection view requests pages 4 and 5. You are still executing the query for page 3 while the queue now contains queries for pages 1, 2, 4 and 5.
– After you’ve got the result for page 3, you send it back to the collection view, dequeue one of the query and launch it on your data source.
– Since query for page 5 is the most recent one, you dequeue it and launch it. While it is being executed on your data source, the collection view has the result for page 3 and your queue still contains queries for pages 1, 2 and 4.
Even though the query for page 1 was the first requested by the collection view, it will probably be the last one executed on the remote data source. If the grid is still scrolling, it will probably ask for page 6, 7, 8 and such. From the collection view’s perspective, the query for page 1 is still running, but may no longer be required since the target page may no longer fit in cache. In such case, it will be aborted. In your case, when this happens, since the request for page 1 is still in your queue, you will only have to remove it from it and ignore it. This will probably improve the application performance since it will ignore some queries and give back results for more recent queries quicker.
In the example, only one query is being executed on the remote data source at a time. You have the choice. You may execute 1 at a time, 2 at a time, or even all. In your scenario, it seems that requesting too many queries at a time has a negative impact. Throttling seems to be a good idea in your case.GuglielmoCancelliParticipantJune 19, 2018 at 12:50 pmPost count: 19
thank you for your quick reply and detailed explanation.
We implemented precisely the solution explained in your reply, using a semaphore to control async calls between two LIFO queues. We also added a 100ms delay, to avoid instant releases of the tasks. That way we obtained the required behaviour, thank you!
Unfortunately, setting ItemScrollingBehavior=”Deferred” (on Xceed.Wpf.DataGrid.DataGridControl) did not alter the number of calls to QueryItems method during the thumb scrolling.
On a side note, we would also like to change the PageSize parameter in a “lazy” fashion, i.e. after the DataGrid control has already been loaded. At the moment we are not able to do it, and it seems that PageSize property is set only once (during the loading phase of the control).Diane [Xceed]ModeratorJune 19, 2018 at 7:27 pmPost count: 1353
For the ItemScrollingBehavior=”Deferred” not working, I forgot that in the case of TableflowView, the TableflowView.IsDeferredLoadingEnabled property must be set to “false” or the deferred scrolling behavior will not kick in.
For the PageSize being set at a later time, we do not see any issue. Setting the property like this is expected to work:
var collectionView = (DataGridVirtualizingCollectionView)grid.ItemsSource; collectionView.PageSize = 50;
By default, the page size is of 200. Changing the page size to 50 means that the DataGridVirtualizingCollectionView will require items in blocks of 50 items instead of 200 to the remote data source. Within the collection view, the cache size (DataGridVirtualizingCollectionView.MaxRealizedItemCount) will remain unchanged. By default, this cache size is of 1000 items.
Instead of having 5 pages of 200 items (5 * 200 = 1000) in cache, it will contain 20 pages of 50 items (20 * 50 = 1000) in cache.
Keep in mind that the page size is a property of DataGridVirtualizingCollectionView. It is a collection view feature and not a grid feature. The grid will always display items in a scrollable fashion.
You must be logged in to reply to this topic.