We are working on a database that holds various information about 15.000 members in a complex structure. These members are managed/viewed by about 1.000 and we need a system to handled rights for these users. You are only allowed to view the members you have had connection with in the last 3 months or currently have one.

The problem we are facing is that it's very slow to find out which members you are allowed to see, because of the various ways you can have a connection.

These members have parents(~2 per member), a location (1 or more of 500), a principal (1 or more of 100) and a hobby (1 or more of 100). All of these are separate tables.

There is a connection for these members in several tables

[fixedconnection] (~500.000 rows) memberid, locationid, principalid, startdate, enddate, dayofweek

[fixedconnectionhobby] (~10.000.000 rows) fixedconnectionid, hobbyid, startdate, enddate, dayofweek

[dailyconnection] (~10.000.000 rows) memberid, locationid, principalid, hobbyid, date

[parents] (~30.000 rows) member, parent

Then the actual users also have a rights structure to define what they can see. There are companies and for each company you can select zero, one or multiple locations, principals and or hobbies. If nothing is selected, you can see all.

Each company has departments, where you can select a subset of the chosen locations for the company. If nothing is selected, you can see what the company can see. If something is selected, you can only see those.

Each dapartment has users connected and these users login.

These users have various lists they want to see and select one item to view the details, like:

  • Show me all the dailyconnections
  • Show me all the members
  • Show me all the locations
  • Show me all the hobbies

We need a way to determine in SQL which records we are allowed to show. At the moment we use a combination of joins and union all to combine all the various options to get a list of ids for a specific type. For example all the member ids we can view. We can use this in the query to view the member list.

Obviously this will make the queries quite slow, even with proper indexing, because you are constantly juggling around milions of connections, just to show a single member.

We tried to use caching and get a single table with all the members you can see, per user, but since there are constant modifications to the members and their connections, it changes often who you can see.

My question: How would you handle a structure like this, to prevent users from seeing members they shouldnt see, and keeping it fast.

  • I would argue you do not have proper indexing if your queries are conducting table scans and reading millions of unnecessary rows. Can you add data types, your current indexes, and example of your current query? – bbaird Oct 16 at 12:52
  • @bbaird All connections are on Id fields with bigint datatype and at the very least all parent connections have an index. There are also indexes on, for example, the start and enddate columns and they have the proper includes to get all required data at once. I have a feeling the problem is more that one user could get a 1000 members back and another perhaps only 5, and it's messing with the queryplans when the returned ids are included in the query to list the members – Hugo Delsing Oct 16 at 13:43
  • can you post the tables schemas, sample data, some sample queries and the query plans – armitage Oct 16 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.