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 ;`