View: dbo.Staff_Client_Permission_By_Type_View
View definition
--
-- Create an indexed view for use in client/staff security filtering.
--
-- Expression queries handed to the data service are normally automatically modified to
-- filter out client-specific rows which the current staff member is not allowed to see.
-- These additional filters have often been the source of performance problems, because
-- they tend to rely on indexes of the Client_N_Staff table which start with the staff
-- key. The number of rows in this table per staff member varies wildly (by a factor of
-- 10,000 in current MyPay databases). Cached query plans built for one staff key value
-- can perform horribly for a different value. Removing parameterization of the staff key
-- value is not an attractive option, since it would cause every query plan to be unique
-- to a single staff key value.
--
-- To work around this, we are changing the security filter to use the following indexed view,
-- which will encourage the optimizer to use statistics based on the number of clients associated
-- with a staff /type/. There are only six different staff types (currently), so embedding
-- the staff type value literally will not cause the same kind of proliferation of query plans.
-- The results will not be uniformally beneficial, however, because within each staff type
-- there can still be a fairly wide range of numbers of clients associated with specific staff
-- key values. The ranges seen in normal usage, however, seem to be much smaller.
--
-- There should be no need for an IEntity definition to accompany this table.
--
-- Further improvements would probably require a complete rethinking of the security model.
CREATE VIEW dbo.Staff_Client_Permission_By_Type_View
WITH SCHEMABINDING
AS
SELECT cns.staff_KEY staff_KEY, s.staff_type_KEY, cns.client_KEY
FROM dbo.Staff s
INNER JOIN dbo.Client_N_Staff cns ON s.staff_KEY = cns.staff_KEY
WHERE s.staff_status_KEY = 1 -- only pay attention to active staff
;