Database Performance with Relationship Fields

For HanDBase related topics that are not specific to one platform.

Database Performance with Relationship Fields

Postby hudsonhawk » Sat Nov 07, 2020 5:33 am

I've read other posts on this but from have seen slightly confusing information so I'd like some clarification.
I've been using Handbase as a CRM and cannot get acceptable performance with my current table design. The following is on my iPhone X.

There is an Organization table (parent) and a Contacts Table (Children). The Contacts table has one db lookup field pointing to the parent ‘primary key’ field and several Relationship fields which pull data from the parent based on this key value. This data is necessary for tracking in other tables. This has severe performance issues when opening the child table even with just 600-700 records. The parent table has 2700 records
* No relationship fields and the child table opens immediately.
* 1 Relationship field and the child table takes 4-5 seconds to load.
* 4 Relationship fields and the child table takes 10-11 seconds to load!

None of these fields used in views. While I could use a DB Popup field, this is very less desired as I'm often creating Contacts from the parent table and I'd like these auto-populated. If you make the other fields dblookups, only the first field auto-populates even though they are all in the same group. The only way to populate them is doing a manual dblookup again (as opposed to simply having them feed in when creating the child record from the parent table).

Additionally, if anything changes in the parent table, I'd like that of course to automatically update when viewing the child table. So am I stuck? Is there nothing that can be done? If there was a way that using a Link/Linked field combo I can live with that but as I understand it there's only one linked field. I've tried to follow advise in other posts (i.e. ensuring these fields are not used in Views, trying but not finding a db setting to disable auto-calculate for Relationship Fields)

Please, advise. Thank you!
hudsonhawk
 
Posts: 18
Joined: Sun Nov 27, 2016 4:49 pm

Re: Database Performance with Relationship Fields

Postby hudsonhawk » Sat Nov 14, 2020 10:10 am

Just an update. It appears that the way Relationship fields are implemented on iOS is pure rubbish unless I'm doing something terribly wrong.

I've a table with just 19 rows in it and it is taking 18-19 seconds to load! It has the following characteristics:
- It is used to track opportunities for companies
- 3 Relationship fields to a parent table to pull the name, parent, and key. Each relationship is tied to a Linked field.
- 11 Relationship fields that pull from a child table that has a list of products being sold. Each field is critical to calculating various totals. Each relationship is tied to a Link field.
- 17 Conditional fields - mainly used to either archive company info should the company be deleted and several to check whether the entry from has been completed correctly.
- 24 Calculated fields. Largely to calculate sums.

My primary view has no relationship or calculated fields shown but it does have one field populated by the External-CombineText field.

There really should be (a) better instruction on how to construct databases for faster performance (maybe there is none?) and (b) a way to simply turn off what ever calculations are being executed to open a table. I'm going to do testing but if I'm correct, what all this means is that a table with 100 records will take a minute and a half to load.

Regards,
g
hudsonhawk
 
Posts: 18
Joined: Sun Nov 27, 2016 4:49 pm


Return to HanDBase General Discussion

Who is online

Users browsing this forum: Bing [Bot] and 1 guest

cron