I’m trying to add Access Rights to my Customization Project, but it’s not working!
I click the Add button on the Access Rights page of the Customization Project Editor, key a screen code or click the screen name selector, and … nothing happens. I eventually get a popup:
Localhost says:
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
I’ve restarted the IIS App Pool. Rebooted. Republished. No difference.
I eventually used SQL Profiler to find the offending SQL query. It’s this one…
SELECT /* AU.20.52.00, 388CE942 */
MAX( [SiteMap].[NodeID]), /*some field omitted for brevity*/
[SiteMap].[ScreenID],
MAX( [RolesInCacheInProject].[CreatedByScreenID]), MAX( [RolesInCacheInProject].[CreatedDateTime]), MAX( [RolesInMemberInProject].[CompanyID])
FROM [SiteMap] [SiteMap] with (nolock)
LEFT JOIN [RolesInGraph] [RolesInGraphInProject] with (nolock) ON ( [RolesInGraphInProject].[CompanyID] IN ( 1, 2) AND 8 = SUBSTRING( [RolesInGraphInProject].[CompanyMask], 1, 1) & 8) AND [SiteMap].[ScreenID] = [RolesInGraphInProject].[ScreenID] AND [RolesInGraphInProject].[CompanyID] > 1
LEFT JOIN [RolesInCache] [RolesInCacheInProject] with (nolock) ON ( [RolesInCacheInProject].[CompanyID] IN ( 1, 2) AND 8 = SUBSTRING( [RolesInCacheInProject].[CompanyMask], 1, 1) & 8) AND [SiteMap].[ScreenID] = [RolesInCacheInProject].[ScreenID] AND [RolesInCacheInProject].[CompanyID] > 1
LEFT JOIN [RolesInMember] [RolesInMemberInProject] with (nolock) ON ( [RolesInMemberInProject].[CompanyID] IN ( 1, 2) AND 8 = SUBSTRING( [RolesInMemberInProject].[CompanyMask], 1, 1) & 8) AND [SiteMap].[ScreenID] = [RolesInMemberInProject].[ScreenID] AND [RolesInMemberInProject].[CompanyID] > 1
WHERE ( [SiteMap].[CompanyID] IN ( 1, 2) AND 8 = SUBSTRING( [SiteMap].[CompanyMask], 1, 1) & 8) AND ( [SiteMap].[ScreenID] IS NOT NULL AND [SiteMap].[ScreenID] <> '00000000')
GROUP BY [SiteMap].[ScreenID]
ORDER BY MAX( [SiteMap].[NodeID]) OPTION(OPTIMIZE FOR UNKNOWN)
Removing the join to RolesInMember showed it to be the problem. Causing 10s of millions of clustered index seeks into a 1000 row table.
I managed to get it to perform quickly by moving some rows out the way, hacking the data using:
update [dbo].[RolesInMember] set CompanyID=-CompanyID where CompanyID>1 and ScreenID='PD100100'
(Where PD100100 happens to be one of my bespoke screens.)
So it looks like the data caused the query to misbehave, but I feel there should have been a better solution.
Any suggestions?
