[Profile] Showing all tables related to the user takes several seconds to load

[jh-RLI]

Description of the issue

Clicking on the user profile and viewing the tables initially takes a lot of time to load.

Ideas of solution

• improve the query
• add index for frequntly filtered fields see this comment

Workflow checklist

• [ ] I am aware of the workflow in CONTRIBUTING.md

[jh-RLI]

The table list load problem seems to spread across the platform ... maybe it just takes long because of the tables in model draft. It definitely should technically not take that long.

[jh-RLI]

Possibly we can solve this through optimizing the datastructure by introducing indexes to fields that are filtered quite often including composite indexing.

class Table(models.Model):
    name = models.CharField(max_length=100)

    class Meta:
        indexes = [
            models.Index(fields=['name'], name='idx_custom_name'),
        ]

class Table_2(models.Model):
    name = models.CharField(max_length=100)
    schema = models.ForeignKey(Schema, on_delete=models.CASCADE)

    class Meta:
        indexes = [
            models.Index(fields=['name', 'schema']),  # Composite index
        ]

[jh-RLI]

I implemented a attempt for optimazation. I changed the code to use django orm related field and prefatch them. This might reduce the query response time. First i would need to test this with more thables then what i have locally.

1611

[jh-RLI]

The updated implementation is more powerful than the previous one. Depending on how many tables are linked to the current user, the page with the profile tables now loads much faster. Even if a user has permissions for all assigned tables, the new approach is better as it reduces the number of database queries.

User and table relation A table is related to the user if the user has direct or indirect permissions for a table. This means either the user himself is assigned to a table or a group in which the user is a member is assigned to the table.

Close this for now, maybe there are further steps to improve this even more.