Thought of one more option.
You
could return two resultsets, one is your normal one and the other is a 'parent
perms' resultset, where it contains only the identifier and permissions for each
parent level item. Then when you're ouputting the tree nodes, if a child
does not contain its own permissions, then you can simply query of query the
second resultset for its parent permissions. Pretty low
overhead.
Kevin
---------------------------------------------------------- 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]-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin J. Miller
Sent: Monday, March 21, 2005 9:24 AM
To: [email protected]
Subject: RE: [CFCDev] Speeding up lookups in a chainI would suggest the single db call approach as well. My first preference is to denormalize the permission data and make the resultset simple and fast. However, assuming you don't like to denormalize, rather than treat each child as an individual lookup, simply 'flatten' the entire result set with inner joins and include the child data as well as its parent (as well as its parent, if necessary -- do you know how many levels you'll have ahead of time?).It will make for some redundant data in the resultset but its a very small price to pay for avoiding 99 other db round trips. I wouldn't advocate a function that handles the permission recursion per child since that will execute for each iteration and defeats the purpose of the optimization.I have a number of apps that handle hierarchical data in this manner (flattening and returning the extra column data), and while there is an increase of the amount of superfluous data returned, the performance is significantly better with a single trip returning a slightly bloated resultset (which is then looped or cfoutput group'ed) than for multiple trips with individual lookups for each child. When you're outputting the tree, you could have a cf udf that checks the query column for the child permission, then if empty, checks the parent column, and so on (they would be in the same row of data).Good Luck,Kevin---------------------------------------------------------- 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]-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Micha Schopman
Sent: Sunday, March 20, 2005 10:53 PM
To: [email protected]
Subject: [CFCDev] Speeding up lookups in a chainI 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. (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.---------------------------------------------------------- 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]
