how to improve the performance of a loop according to the results received?-CodePudding

I have to make a query in the data table and depending on the number of results received (from 0 to X) I have to add the information in another table:

    begin
                for code in select object from data where subject = 'subject1'
                and predicate = '#type' 
                and object like '/core%'
                loop
               select id into categories from categories  where id_categories  = code;
               if found then
    
                insert into fiche_categories (fiche_id, categories_id) values (fiche, categories ) on conflict (fiche, categories ) do NOTHING;
               end if;
end loop;
end;

how can i improve performance?

thank you

CodePudding user response：

Just don't use an imperative loop in the first place. Write declarative SQL:

insert into fiche_categories (fiche_id, categories_id)
select fiche, categories.id
  from data
  join categories on id_categories = data.object;
  where data.subject = 'subject1'
    and data.predicate = '#type' 
    and data.object like '/core%'
on conflict (fiche, categories) do nothing;

Also, to speed up your database in general, don't use an EAV model, and make sure your tables are backed by appropriate indices.