First, do you have SQL Server 200x? If so, create a link to Active Directory and then you can join the two datasources just as you would join two tables.
If not, retrieve all your AD accounts, using a single CFLDAP query and store them in a SQL database. From there, you can join the data as needed. If you have a beefy domain controller, you should not have any problems hitting the directory hundreds, or thousands, of times. I do this on an almost-daily basis. You could build your LDAP filter to retrieve multiple accounts, however, I wouldn't have it return more than a couple dozen, at a time. Try changing your filter to be something like: (&(objectCategory=Person)(|(cn=user_1)(cn=user_2)(cn=user_n))) Personally, I'd just hit the domain controllers as you are doing now and be happy in the simplicity of the solution. Mike -----Original Message----- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Friday, October 31, 2008 11:26 AM To: cf-talk Subject: LDAP Query Performance question I have a query of a SQL table that returns to me a bunch of usernames. >From that, I need to query some information out of Active Directory for each user. Right now I have it looping over each row in the result from the SQL query and doing a separate LDAP query to the AD. This just doesn't seem like the best way to do things. Does anyone know if there's a syntax within LDAP querying of the AD to do something like an IN keyword where I could give the whole list of usernames I want to query and get a single result set back from one query instead of doing individual queries? There's no commonality amongst the user accounts in the AD except for the fact that their usernames are all in this external SQL table. Let me know if you have any ideas. Or maybe the single LDAP queries aren't as intensive as bad as what I'm thinking. I just know I always try to avoid multiple queries in SQL whenever possible. Here's the syntax I'm using now when querying the AD: <cfldap action="QUERY" name="GetEmployeeIDByUsername" attributes="cn,displayName,givenName,extensionAttribute1" start="dc=domain,dc=com" scope="subtree" filter="(&(objectCategory=Person)(cn=#myQuery.username#))" server="domaincontroller.mydomain.com" username="#ADUsername#" password="#ADPassword#"> Thanks in advance for any help. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314700 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
