I have a biggish data table (~20k rows) called game_state, with one row per user. Whenever I want to get data from it for that user, the first step is of course to find the user’s row.
I have a column in the data table which is a link to the relevant Users row. So, to get the game_state row, I do:
gameStateRow = app_tables.game_state.get(user=anvil.users.get_user())
Unfortunately this turns out to be really quite slow - it routinely takes 1 second and sometimes takes several seconds.
I guess that I am really asking the data table to look through every row to find the one matching my query - maybe it’s reasonable that it should be slow? But it feels like this must be a standard problem and there must be a better design approach.
Things I have thought about:
-
It made sense to me to simply include a link to the game_state row in the main Users data table. To my surprise though, this means that every time a row in the Users data table is loaded, the whole game_state row is loaded too, which made things incredibly slow.
-
I am already using q.fetch_only() when I get the game_state row, so I am certain that the time taken is the time to simply find the row - no data is being loaded from it.
All help very much appreciated!
One second for two simple queries (one for the user and one for the game state) sounds way too slow.
Can you please try to print(time.time()) before and after that row, and verify that that’s really the bottleneck?
1 Like
Thanks for your response. It’s encouraging that you say it sounds way too slow. It certainly feels slow to me but I wasn’t sure what was normal.
I added some careful print(time.time()) statements around that line of code, and confirmed that it was indeed the issue.
It varies a great deal though - sometimes it’s only 0.1s or so.
Another line a few lines later is also sometimes very slow:
gameStateRow.update(column1=value1, column2=value2, column3=value3, column4=value4)
This is normally quite quick but sometimes takes up to 7s. The four columns are just simple numbers so this doesn’t seem right to me.
It seems like access to my data tables is generally slow. I don’t think the issue can be my code, as in these cases my code is so simple.
Is this situation common? Could it be a misconfiguration at Anvil’s end? Or is there perhaps something I could be doing wrong in not managing use of the data tables properly (I am not using transactions, in case they would be relevant).
This is all on a dedicated server. I would estimate it’s handling several data table requests per second.
Thanks very much for any thoughts!
If a line was able to execute in 0.1 seconds once, then that line is (almost) always able to execute in 0.1 seconds.
If you see generic slow downs not tied to one specific line, then the problem is not that line, is what manages that line.
I have tables with hundreds of thousands of lines, and querying them is never that slow.
The good news is that now you know that your problem is not where you thought it was when you asked this question.
The bad news is that you need to start investigating from scratch.
My uneducated guess is that you are running out of the resources included with your plan. Perhaps you have too many background tasks, perhaps you have too many round trips, perhaps who knows.
1 Like
Hi, thanks so much for this reply, it’s really helpful. Much appreciated!