alternative to temp tables with 3M+ records and growing

Question

alternative to temp tables with 3M+ records and growing

ricky969

SSC Enthusiast

Points: 130
More actions
January 15, 2021 at 2:48 pm

#3833804

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ratbak Ten Centuries Points: 1358 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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)?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ricky969 SSC Enthusiast Points: 130 More actions · Answer 4

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.

ricky969 SSC Enthusiast Points: 130 More actions · Answer 5

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.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 6

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ricky969 SSC Enthusiast Points: 130 More actions · Answer 7

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.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

Can you post the (sanitised as needed) definition of the TVF which returns the MemberIds to which the user has access?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin