ADP slow because SYSREFERENCES cached on first VIEW or TABLE open

[John G. Regan III]

KB275085 explains that the extended property MS_SubdataSheetName setting
effects whether the SYSREFENCES or INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
is cached on the first open of a SQL VIEW or TABLE for an ADP. The article
says it applies to MDBs only, but it also applies to ADPs.

We resolved our caching induced delay for SYSTEM_ADMINISTRATORs only by
using a derivative of the code to set all user procedure, table and view
Subdatasheet Name propertys to "[None]". However, UNLESS the user has SQL
SYSTEM_ADMINISTRATOR role attributed to them, the ADP will enumerate all the
foreign key possibilites for the entire database on the first open of any
table, view or user procedure for each instantiation of an ADP.

Can anyone explain WHY this is occuring for only NON -
SYSTEM_ADMINISTRATORs? Or, is there a fix or setting that will cause this to
NOT occur for the common users?

 

[John G. Regan III]

We RESOLVED this problem for ourselves. For PUBLIC or NON -
SYSTEM_ADMINISTATOR users, we PROHIBITed the SELECT permission on the
SYSFERENCES table. This does not appear to have created any additional issues
at this time.

Good luck,