January 15, 2021 at 2:48 pm
I'm struggling with the performance of one of my stored procedures. Depending on the user that is calling the report, we have to filter some of the records. If the user has a restriction on the members he can view, we're inserting into a temp table only the members he is allowed to see; if there are no restrictions we are inserting into the same temp table all the members.
I believe this is very inefficient, especially when there are no restrictions! we have 3M+ members so adding them to a temp table and indexing doesn't seem the best way to handle the issue.
I thought about duplicating the same code into two blocks, one for restricted users (so i can join to #Members) and one for unrestricted users (so i can join to dbo.Members) .
Can you suggest or recommend a better solution? The member table will soon double to 5-6M so that's something else I have to keep in mind.
Thanks
January 15, 2021 at 3:07 pm
If the user has a restriction on the members he can view, we're inserting into a temp table only the members he is allowed to see;
Isn't the list of allowed members already in a table then? Why don't you do an EXISTS check (rather than a JOIN) directly against the existing table rather than using a temp table? Also, make sure you have an index on ( user_id, allowed_member_id ) on that table.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 15, 2021 at 3:16 pm
Is performance bad merely because of the # of users, or because of the volume of data returned by a lot of users? You probably want to look at indexing to see if that is a problem.
The switchboard approach -- simple query w/ no temp table if user is unrestricted, and use of temp table if restricted, seems like a logical way to avoid the cost of the extra insert and filter on join to temp table.
When restricted, how many users (a) can be included; and (b) are included on average? You're still going to be in trouble if the list is restricted to 2,999,999. If # of allowed users can be large, you may want to look at a hierarchical/group approach where you assign users to a group, and assign permissions to groups rather than individual users. But that would , still ultimately requires a join back from groups to users. It would potentially simplify administration of allowed users, but could add additional query cost.
January 15, 2021 at 3:33 pm
How is the list of members which a user is permitted to see defined?
Does the members table have a clustered index on MemberId (or whatever you have chosen to call it)?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 15, 2021 at 4:19 pm
If I determine that I need to filter the members, I make a call to a tablevalue function and insert those records in a temp table
In my test environment, without filtering I have 970K members, with the filtering I can go from a min of 100 members to a max of 450K.
user A can see 100 members
user B can see 250k members
user C can see 500k members
and user D, E and F can see all members
I'm not storing the value to filter in the member table.
Based on the securityID passed in at runtime, I filter from table A and join to table B and from table B I join to table C (member). Later on the query I need to join members with a bunch of other tables.
January 15, 2021 at 4:28 pm
Unfortunately there's no clustered index on the MemberID which is of a type uniqueidentifier
I did notice that if I disable the filtering and join straight to the dbo.Member table (so no temp table) I save minimum 14 to 20 seconds...and that is before I start joining with anything else in my query.
January 15, 2021 at 6:26 pm
It shouldn't be clustered, but you do need an index, as I noted above, on:
( user_id, allowed_member_id )
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 15, 2021 at 9:31 pm
the index on the temp table helped. what also helped tremendously was to use the switchboard approached mentioned above. Now user without restrictions can join to dbo.Member and get the results back in a couple of seconds. For the users with restrictions, nothing changed...the query will still run slow but with the new index is definitely better than before.
i just don't like to see the same code repeated twice but...
Thanks for all the help and if you can think of anything else i'll appreciate any other comment.
January 16, 2021 at 5:55 am
You really should not use a temp table for this. It's a lot of overhead copying rows to the temp table, esp. if you then separately build an index afterward. Create and maintain a permanent table of user_id and associated allowed_member_ids.
Did you do a JOIN or a WHERE EXISTS() to check for the allowed member ids?
Also, add a "bypass" for the "without restrictions" to the main query and OPTION(RECOMPILE) and you won't have to have two separatequeries. Something like this:
SELECT ...
FROM dbo.main_table
...
WHERE (@unrestricted_user = 1 OR /*prevents table lookup for unrestricted users*/
EXISTS(SELECT 1 FROM <other_table> ot WHERE ot.allowed_member_id = mt.member_id))
OPTION(RECOMPILE)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 16, 2021 at 1:38 pm
Can you post the (sanitised as needed) definition of the TVF which returns the MemberIds to which the user has access?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply