Query to shuffle the order from app table

Anvil Q&A
1

*What I’m trying to do:
I would like to shuffle or randomize the order of the table
Currently, I am sorting the order by serial number

result = app_table.tables.search(
      tables.order_by("serial_num", ascending=True),

What I’ve tried and what’s not working:
I tried randomizing on the client side.

    self.repeating_panel_1.items = results
    results = list(results)
    random.shuffle(results)

Since the table is big around 20k rows, it is taking quite a time to load.
I am looking if there is a sql query
something like this

result = app_table.tables.search(
      tables.order_by("serial_num", 'RANDOM')

Thanks

2

Are you trying to get all the 20k rows in shuffled order or only a few of them?

3

Hi! All the 20k rows in shuffled order

4

It doesn’t work

This doesn’t work because the repeating panel is generated when items is assigned.
It will work if you do this:

    results = list(results)
    random.shuffle(results)
    self.repeating_panel_1.items = results

It’s slow

I think you can speed up things by executing the list(results) on the server side.

I think that search will fetch the first batch of rows (~100), then list(result) will add batches as needed, as it goes through the comprehension.

If you do it on the client, you end up with 1 round trip per batch, 20000/100=200 round trips (assuming each batch is 100 rows).

If you do it on the server, you end up with 200 accesses to the database, but without the round trips, and it should be faster.

20000 items on a repeating panel

The next consideration is about creating a repeating panel with 20000 items…

Generating 20000 forms, even if they are simple, could take some time.

If the repeating panel is inside a DataGrid with pagination enabled, then you will quickly see the first page. But if the user clicks on the last button to go to the last page, you will trigger the generation of all 20000 forms (unless I’m wrong and the DataGrid is smart enough to only generate the forms on the last page).

So, if you are using the DataGrid, I suggest to test what happens if the user clicks on the last page button, and, if it’s too slow, to remove the navigation buttons and create your own, with the first 3 buttons and without the last page button.

If you are not using the DataGrid, you could have a look at this so you add a few rows at a time instead of waiting the generation of 20000 forms at once.

3 Likes
5

This is in no way a solution but more a complete hack - if you only need some rudimentary randomisation, you could add a ‘random’ column to the table which has random entries in it and then sort by that within the search query. If you want to extend the randomness you could write a function that changes the random column values in a background or scheduled task… but it all depends on how truly random the search needs to be.

2 Likes
6

Also, if you are going to return a massive list from the server, run this on the server side before returning, I would venture to guess that the pure python implementation of randomizing a list in-place on the server module will be way faster than having a client side browser running python -> skulpt -> javascript do it.

7

This would be easier to do if you have SQL access (I think available on dedicated plans and above), because you could quickly run a query and add the 20000 random numbers in one quick shot.

While doing it with a Python loop would be slow, and could benefit from running on a background task as you said.

8

If you had SQL access you could just do ORDER BY RANDOM() in the query :sweat_smile:
(for Anvil’s PostgreSQL)

1 Like
9

Hi. You’re right about the shuffling part. I need to shuffle first and then assign it to the repeating panel.
What I observed about the DataGrid with a repeating panel inside is, that it takes 7-8 secs (in my case) to load all the data. Then clicking the next page changes in no time.

10

Hi! I think this is the other way.
Returning an iterator to the client and converting to a list on the client side is faster compared to converting to a list on the server side.