-1

we have a system that contains documents & folders each with different access rights for each user the database already contains millions of data, and the final access of the document should do recursion to ensure the user has access to all parent folders The search performance is very poor and we tried a lot of ideas but still, we face the performance problem enter image description here

Muhammed Atallah
  • 25
  • 1
  • 4
    Please check [here](https://stackoverflow.com/a/34975420/4808122) what you should at least post while expecting help with performance problems... For your case is espetialy relevant the index definition of the target table and of course the *query you use* along with the execution plan and the basic statsitics of the table. – Marmite Bomber Mar 11 '21 at 11:20
  • 6
    What did you expect we could do here? We don't know your schema, e.g. what indexes you have for improving database lookups. You don't show what SQL you currently has, so how would we know how to improve it? You talk about *"ensure the user has access"* but don't show anything about that information, and any performance issue you have is very likely related to that access right logic. – Andreas Mar 11 '21 at 11:30
  • Have you profiled your program to see where the potential problems might be? – NomadMaker Mar 11 '21 at 12:01

1 Answers1

0

You should use with clause like this. (sqlite3 example)

create table tree (id int, parent_id int);
insert into tree (id, parent_id) values
(1, null), (2, 1), (3, 1), (4, 1), (5, 2), (6, 2), (7, 3), (8, 7);
with r as (
    select * from tree where id = 8
    union all
    select tree.* from tree, r where tree.id = r.parent_id
) select * from r;

output (all parents for 8):

8|7
7|3
3|1
1|

tree structure:

enter image description here