I have a stored procedure to fetch all users in the database. To do this it queries a view which is composed on two tables: Users (holds basic info like username) and UserPortals (maps users to certain sites).
The only parameter that changes between executions is the 'PortalId'. Here is an execution plan that returns results in about 1 second However, when changing the PortalId to a different value it takes about 21 seconds. As seen here. The slower query is for a PortalId of a newer portal so the data is presumably further towards the end of the table. Does this matter?
The interesting difference in the execution plans is that for the slower query, all rows in the Users table are scanned, however this is not the case for the faster one.
Are there any ideas on how to make the slower query faster? I assume it is an indexing problem.
Note: I have tried adding the recommended index and it actually resulted in worse performance.