How to quickly get to the last page in a DataGrid

Anvil Q&A
#1

I have a DataGrid that has a similar structure to the CRUD example here.

That is, the rows of the DataGrid are given by a Repeating panel, and each row has a read and write DataRowPanel.

If I use the ‘>>’ control it takes a very long time to get to the last page (~20 seconds). There are 156 records being pulled from the DataTable.

I realize that this topic has been discussed before but I could not quite wrap my head around the techniques used there.

Even if I turn all the rows into dictionaries, make one server call, and return all rows to the client, it still takes ~20 seconds to get to the last page of 156 records.

Is there a way to optimize speed, or redesign my app, for navigating through the DataGrid’s pages (including selecting the last page), while still using DataTables?

Here is an example app that demonstrates the issue.
https://anvil.works/build#clone:6ZXHWYZVYR7Z3GWV=EEZ7VO563NOIB7WOKRPSPODY

1 Like
Add row to top of data grid without refreshing
#2

Would it make sense to reverse the sort order, so that last becomes first?

#3

In general I find that scrolling one or more pages is not very fast (even if all data is on the client and there’s not much of it).

It is not the last page’s content per se that I’m interested in. I’m looking for fast navigation to whichever pages happen to be positionally last.

The grid is used for staff to do data entry and I believe that they will find the experience rather sluggish moving from one page to another, and certainly if they try to get to the last page, the screen hangs for quite some time and this will probably confuse folks.

1 Like
#4

Sounds like it might be time to build an index. That’s the usual solution for speeding database table access.

#5

Do you mean using slices so that I can return only what I need in small chunks? For some reason I thought that that was not going to work well because of the way DataGrids query the server, at least that is what I gathered from @stefano.menci’s related post.

#6

No, not slices. Additional data, and code, dedicated to speeding up the paging.

As you point out, the default DataGrid paging scheme has some limitations, not suitable in your case. But you can roll your own paging, and it might just be faster, for your data. I’ll describe two such schemes, in general terms, below.

One way is to add a column, just for filtering purposes. For example, if the records can be numbered sequentially, and these numbers are stable (no inserts or deletes between them), then you can compute the record numbers for any particular page, and appropriately filter on that column. This takes advantage of Anvil’s built-in indexing.

If that doesn’t work, or doesn’t work fast enough, another way is to add an in-memory data structure of your own, your own private “index”. To go to a specific page, consult your index first. Your index should tell you exactly which records you need for the current page, so you’d retrieve those, and only those, with a suitable query.

In both cases, moving to another page requires re-executing the query, with slightly-different parameters. You’re bypassing DataGrid’s paging mechanism, and substituting your own. You’d have your own navigation buttons, for example, to activate your custom code.

On the other hand, you gain random access to both ends, and to any page# in between.

Unfortunately, the only way to know what the performance will actually be like, with your own data, is to build it and try it out.

#7

Another possible advantage: you get to decide what constitutes a “page”.

Perhaps it’s all the events within a specific time period, or belonging to a specific source. It doesn’t have to be a fixed numerical quantity.

#8

Okay. Great, thanks for the suggestions. I will definitely try these solutions.

#9

While trying your suggestions, I thought I’d try returning all data to the client, using slicing in conjunction with my own paging buttons to select my pages (rather than calling the server and filtering/querying for my pages). I just wanted to see what would happen in terms of speed.

Well, I can get to the last page now just as quick as I can get to any page, but nothing is particularly snappy felling and the initial load up time is quite long since I’m returning everything at once from the server.

You can see how it feels here:
https://anvil.works/build#clone:OQ5ZNCJ6P4XFTVWP=CIHT24IYJBK5KQFUI3273GXK

Anyway, now I’ll try to see if filtering/querying my pages from the server side improves things as you’ve suggested.

Al

#10

The DataGrid pagination only works with small lists. When I work with larger lists I use a repeating panel.

But if the DataGrid is almost good for you and you just want to make the user feel it a little snappier, you can use two server calls, the first one that loads only enough rows for the first page, followed by a self.timer1.interval = 0.01.

Then, after the UI has finished rendering the first page, the tick event of the timer will load the remaining rows with another call.

It is not going to be faster, but the user has a quicker feedback on the UI and feels happier.

1 Like
[Wiki] Performance optimization
Created Myself A Blocking Issue (Tables/Repeating Panels)
Suggestions to optimize performance
#11

That is clever! I could definatley see that helping the slow initial load time (while the full set of data is returned to client).

I was really hoping that bringing all my data (~200) rows to the client, and controlling my own pagination would get things snappy, but it hasn’t turned out that way exactly.

I’m going to implement your suggestion, as well as @p.colbert’s and see how that feels.

I really appreciate the help. Thanks.

1 Like
#12

@alcampopiano
Not an answer directly, but something I’m actively working on.

Might not suit as it’s no longer completely Anvil, but just in case it does I thought I’d mention it. The pagination is lightning fast.

1 Like
#13

For the record, your feedback that the Data Grid is too slow is received, loud and clear. We have a few things on our plate atm, but watch this space :slight_smile:

1 Like
#14

I appreciate that Meredydd. It’s not a huge problem. What’s nice is that for me to implement the above suggestions is not difficult in Anvil.

1 Like