What i've done (but not in relation to permissions) using the adjacency model is to create a parent (or ancestor) list for each node of the tree that contains the id's of the ancestory chain. The parent list is created using a recursive function when editing the tree, which doesn't happen all that often. You then can traverse quite easily up the tree.
Don't know if that would help in your case, but i toss it out there, just in case. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Barney Boisvert Sent: Monday, March 21, 2005 2:02 AM To: [email protected] Subject: Re: [CFCDev] Speeding up lookups in a chain Do you really have many-to-many parent/child relationships? Those aren't really parent-child relationships, are they? If you use the nested set model, rather than an adjacency list (hit google for details), then pulling a list of ancestors is ALWAYS a one-query operation. If you add a WHERE condition to only pull ancestors that have permissions defined, and then limit it to the first record (using TOP, LIMIT, or ROWNUM, depending on your DB), then you can always pull the node that defines permissions for an arbitrary object in a single query. Very powerful. I'd read up on nested sets a while back, but never bothered to try them out for real until the past couple weeks, becuse the tree maintenance tasks are significantly more complex than with the adjacency model. I was a fool for waiting. Don't know if it's possible to switch your app to use nested sets, but if it is, it's something I'd definitely consider doing. cheers, barneyb On Sun, 20 Mar 2005 21:53:06 +0100, Micha Schopman <[EMAIL PROTECTED]> wrote: > I have a treeview where each node has a permission set, similair like NT > Security. If a node doesn't have permissions set, they are inherited from > the first parent in the "bloodline" who has permissions. ( > http://c2.com/cgi/wiki?ChainOfResponsibilityPattern) > > When I expand a level in the treeview, the following happens. > > - Childs are retrieved from the database (many to many relationships) > - Each child's permission set is retrieved, if no permissions have been set, > this lookup continues untill the system found a parentnode with permissions. > > The problem is the 2nd step. This step executes in a few milliseconds, but > once you execute 100 database calls to check for the permissions of each > child, you might imagine the delays grow with every lookup. The queries have > been optimized already, and run under 1ms but it is the amount of queries in > combination with the extensive model causing delays. > > I was hoping for people with similair issues when working with permission > sets on objects. Did you find a way to improve lookups for those > permissions? > > I figured out some options: > - lazy loading, but even then it takes at least one request to create the > cache. > - a quick lookup table, specifically for lookup actions. Side effects are > that you need some extensive queries and logic to update these tables on > each change in permissions, groups, roles, policies, departments, and users. -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/ Got Gmail? I have 50 invites. ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). An archive of the CFCDev list is available at www.mail-archive.com/[email protected] ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com). An archive of the CFCDev list is available at www.mail-archive.com/[email protected]
