I’ve been doing a bit of reading on the bubble DB notes and I hadn’t realised they indexed things, used as foreign keys, but I gathered from those notes, they were secondary indexes. (but useful all the same).
It’s pretty cool. I hadn’t realised Bubble had gone this extra step. I’ve been going down the traditional route of putting the UID in tables as foreign keys (which works well) but is not required for Bubble. Also it means the relationships are there for you and mean the joins are done for you.
This is unnecessary and inefficient in Bubble. It means every time you want to get a thing you have to Do a search for (unique ID):first item… it’s inefficient from a performance perspective, and from an editing perspective. More details here:
Yeah I found those articles/posts are really good. Thanks. I’d also found similar by a guy called Nigel had said the same. It’s all good.
Fortunately, I’ve structured the app so I don’t have to keep looking up the UID all the time (I have years of structured programming). I have been maintaining state through the URL, Slug and browser memory.
But…I think it makes a lot of sense, to make use of these abstractions. They are cool.
@david.j.hansford My understanding is that you are doing this search to delete all children records from related tables based on the deletion of an entry in a specific table.
“Triggers” can be configured in the backend so that whenever a modification / insertion / deletion of a record from a specific table happens, automatically it is fired to execute whatever logic you want (in your case, is to delete all related entries).
Please take a look at this page for more information:
Made changes (switched to fetching a list of 100 items, 5s apart, and doing the secondary workflow on a list, then fetching the next 100 after 500s, as well as doing :count >= 1) and I’m still getting database timeouts.
How do I dig into what could be causing this? For example, @adamhholmes was able to check WU usage (presumably from logs when the search actually works) as well as time taken (logs also?).
I wonder if the issue could be being caused by the database getting hit hard by another part of the app but it seems to always be a problem with calls to the Posts table specifically. It also seems to be consistently a problem with this workflow, regardless of what state the rest of the app is in.
Is there some information about database load somewhere in the bubble dashboard that I’ve not seen before? If this was AWS I’d be able to debug the issue easily.
@ahmed.elkaffas1
I understand what you mean now. The issue with this approach is that I’d have to delete the Team entry first when it’s necessary for me to leave deleting this until last. Also, presumably under the hood the database triggers are still asking the same thing of the database so if the issue is related to the Posts table it could still fail in the same way on that trigger?
Yes that could be right. I am really curious to know what causes this error. I don’t see a problem in your “Do a Search for:first item”. Also you are not sorting anything so it should simply return to you the first item it finds that matches your criteria.
Also about linking with the “Team” object, that’s correct.
One last thing I might think of which might not make much sense is to change the name of the variable to be anything other than the same spelling as the “Team” data type although it shouldn’t make a difference.
Yes, there’s a text field that can theoretically have any number of characters. The data is slack messages so probably has an average length of 100 characters?
This field could be the problem. I would suggest to create a branch in your Dev. environment and try to delete / isolate this field temporarily if possible just to try the same query that causes the “Database timeout” issue and see if it works on the 600K records, then this means that this field is the problem root cause.
Are there any other similar fields in your “Posts” table ? How many fields does it have and what are their datatypes ?
Most probably this will be the problem given the high number of records stored in the table.
