Reduce Menu Generation Time

Question

Warning

I wanted to add a disclaimer to warn against this database structure. I would highly advise a more streamlined approach.

My personal preference is to use a hierarchical one or two table structure to store navigation menus. For guidance on erecting a menu from a flat structure, see https://stackoverflow.com/a/444303/1778606

Answer

In my case, the answer was to cache the menu on the client. Read on if you want.

---

I have a three tier menu that is generated using several queries. In the application I have inherited, there are many user groups. Each user has access to different pages/parts of the web application via user groups. A single user may be in many user groups.

In their menu, each user should only be able to see and access pages their groups have access to. ie. only certain Menu Items, Menu Submenu Sections, and Menu Submenu Section Items should be visible for each user. The pivot tables control visibility and access.

To generate the menu, I need to make several queries. My sample proposed menu design is shown below.

This menu looks drastically different depending on which tier1, tier2, or tier3 items a user has access to.

Would it be appropriate to cache the entire menu in session for each user instead of generating it for each page load? Would this be potentially to much data to cache? My main concern is that it would like cache all three tables (Tier1, Tier2, Tier3) for each active user in session. I need to access the data anyway to see if the user has permissions, but meh!

Are there any architecture designs that would help reduce the number of queries required to generate the menu? (assuming the menu is generated on each page load as normally works as I assumed when I started writing this question)

Is this kind of menu thing normal in applications - we have like 20 groups and users are in anywhere from 2 to all 20? Any advice or comments are welcome.

(I want to minimize page load and processing time.)

Context: I'm using c#, asp.net mvc3, oracle. Max user base estimated at ~20,000. Max active user base close to 1000. Likely active user base is 100.

Answer 1

At first when a user logs in, then load the permitted menus under the logged in user in cache. Then access the menus/submenus from the cache for that user. when the user logs out then clear the cache. In this scenario each time a new user logs in, cache will only be loaded for that user.

Thanks

Answer 2

Instead of using session to cache this information, you could add something like app fabric or memcached.

This has the advantage of not having to handle session in a load balanced application.

Space should not be much of an issue.

Answer 3

Load the entire menu once for the each user and cache it on the client.

It won't need to be reloaded unless they invalidate the cache (via a reload).

A query will still be needed on each page to validate the user has permissions.

(Answer attempt #2 for my own question)

Answer 4

Heres an option...

Keep the Tier1, Tier2, Tier3 data table in the application data cache

Store the primary keys of the Tier1, Tier2, Tier3 tables in arrays in session for each user, ie. a Tier1Key, Tier2Key, Tier3Key int[].

This should reduce the memory footprint somewhat. There is still going to be a bit of a load generation. You may be able to solve this by using a partial view for the submenu and caching the partial view on the client. (This would require a delayed partial menu load via ajax, which would happen on MenuItem click)

You may need to a query to validate access on each page.

for client caching, see OutputCache Location=Client does not appear to work,

(Answer attempt for my own question)