Record view is very slow in medium size module

💻 Core Engineering
#1

Hello,

I was trying to get a value from a record in a module with 6000 rows, but I was very surprized by the fact it took a such long time to complete.

I run few tests, and I found this :

  • The speed of viewing the record is the same in physical schema than in an EAV modeling (json).
  • Direct query in corteza (100 ms) vs Query in corteza (physical schema) (6300 ms)

query corteza physical
Versus:

query sql
query sql1021×200 15.3 KB

Any Idea what might be causing this ?

Cheers !

1 Like
#2

I went through this as well and had to dive deep into the code and DB diagnostics to figure it out. I’ll paste my bug report here (it was in a private email with planetcrust):

Email 1

I figured it out. When you lookup a single record, a dedupe is done (why do you need to dedupe upon record lookup?)

corteza/server/compose/service/record.go at 535b7c614fec89319fc6200b9ee0679ad4851dbe · cortezaproject/corteza · GitHub

Dedupe is currently naive in its implementation (see the note here) and loops through every record individually.

Somehow an empty dedupe configuration was added to the module (I don’t know how - maybe it’s automatic?). I pressed delete, and the ui looks the same, but now the dedupe check doesn’t happen and record load quickly.

It would be good to have a global ‘turn off dedupe’ switch for things like this.

Follow up emails

I just saved a module by changing a field, and saw that a dedupe rule is automatically added to the module which is then later considered by the dedupe function.

I’d consider this a bug.

Right now I’m removing the de-duplicate rules at the database layer with an update query manually each time I save a module, so a fix would be appreciated for that as well (unless the dedupe logic ignores the empty rule in which case no fix is needed on the frontend ).

Solution

If you want to copy my solution (only works for postgres), here’s the trigger:

create trigger compose_module_after_update
    after update
    on compose_module
execute procedure after_compose_module_func();

function definition

create function after_compose_module_func() returns trigger
    language plpgsql
as
$$
BEGIN
    UPDATE compose_module
    SET config =jsonb_set(config, '{recordDeDup}', jsonb_build_object())
    -- make sure it's a physical table
    WHERE name IN
        (SELECT compose_module.config -> 'dal'::TEXT ->> 'ident'::TEXT FROM compose_module
            WHERE
            compose_module.config -> 'dal'::TEXT ->> 'ident'::TEXT!=''
        );
    RETURN NEW;
END;
    $$;

Edit

You should probably use a before trigger instead of updating from within a trigger. The trigger above is a simplified version of the one we use in production. I didn’t actually test it the way you see above. The one we use protects against infinite update recursion (aka cascading triggers) and does other things which made me choose to use an after trigger.

2 Likes
#3

Thank you very much. This saved my day !

1 Like