RE: LDAP Query Performance question
Mike- When you do this is this a loop that runs 5500 queries or 1 query that updates 5500 records? Just curious what the impact is for each connection. John -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, October 31, 2008 3:45 PM To: cf-talk Subject: RE: LDAP Query Performance question Oh, by the way, regarding my earlier message. We have about 5,500 accounts that I update on a daily basis. Performance isn't really an issue, however, I have plans to cut this down from taking several minutes, to just a few dozen seconds. Mike -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Friday, October 31, 2008 12:03 PM To: cf-talk Subject: RE: LDAP Query Performance question It's just a nightly task but it may have 300-400 results each time it runs. I don't think it'll bring down the house or anything it just brought up the question to see if there was a more elegant way of doing it. ~| 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:314769 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: LDAP Query Performance question
My current process is to query AD using CFLDAP. That returns about 5,500 user objects. Then, I loop over those results and perform a CFQUERY to a SQL 2005 database. Our SQL server is pretty beefy, so we haven't had any performance issues. My goal of this process is to compare names, titles, phones, addresses, etc, from AD to the daily-updated information in SQL. If any one of the data attributes is different, then I perform an LDAP update to refresh that particular user object. It does take a few minutes to run, however, I have found a faster method of connecting AD directly to SQL Server. When I get some time, I'm going to rewrite this process to actually join AD and the SQL table and compare the data in one swoop rather than thousands of swoops. Then, I will be able to schedule this as a SQL job and take the full load of of ColdFusion. Until then, it doesn't hurt a thing. Mike -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Monday, November 03, 2008 9:19 AM To: cf-talk Subject: RE: LDAP Query Performance question Mike- When you do this is this a loop that runs 5500 queries or 1 query that updates 5500 records? Just curious what the impact is for each connection. John ~| 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:314770 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: LDAP Query Performance question
See, I'm doing the opposite. I'm getting a list of accounts from a SQL database and trying to then perform a read query to get those user accounts from AD. However, I can't find an elegant way to do that in 1 query so I'm looping over my results and doing individual queries to the AD for each account. I'm definitely looking into this AD/SQL linking which I had never heard of before. Thanks for the help! John -Original Message- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, November 03, 2008 10:27 AM To: cf-talk Subject: RE: LDAP Query Performance question My current process is to query AD using CFLDAP. That returns about 5,500 user objects. Then, I loop over those results and perform a CFQUERY to a SQL 2005 database. Our SQL server is pretty beefy, so we haven't had any performance issues. My goal of this process is to compare names, titles, phones, addresses, etc, from AD to the daily-updated information in SQL. If any one of the data attributes is different, then I perform an LDAP update to refresh that particular user object. It does take a few minutes to run, however, I have found a faster method of connecting AD directly to SQL Server. When I get some time, I'm going to rewrite this process to actually join AD and the SQL table and compare the data in one swoop rather than thousands of swoops. Then, I will be able to schedule this as a SQL job and take the full load of of ColdFusion. Until then, it doesn't hurt a thing. Mike -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Monday, November 03, 2008 9:19 AM To: cf-talk Subject: RE: LDAP Query Performance question Mike- When you do this is this a loop that runs 5500 queries or 1 query that updates 5500 records? Just curious what the impact is for each connection. John ~| 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:314771 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: LDAP Query Performance question
Would it help if you used some sort of group membership? For example, your SQL query would add all accounts to a domain group, or remove accounts, as needed. Then, you would just pull all members of that group in one CFLDAP query. I do something similar to this every morning. My process determines who is an active employee, faculty or student. I have a SSIS job that pulls current employment information from our HR system. It then checks to see if each employee is a member of our Current Employees domain group. If it is not a member, it adds them. Conversely, it also checks to see if any group members are no longer employed. If not, they are removed from the domain group. The result is the domain group is competely updated every morning. Performance is high, because group membership does not change greatly on a daily basis. You may add/drop a member here and there, but the entire group is usually not completely changed. mike -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Monday, November 03, 2008 9:31 AM To: cf-talk Subject: RE: LDAP Query Performance question See, I'm doing the opposite. I'm getting a list of accounts from a SQL database and trying to then perform a read query to get those user accounts from AD. However, I can't find an elegant way to do that in 1 query so I'm looping over my results and doing individual queries to the AD for each account. I'm definitely looking into this AD/SQL linking which I had never heard of before. Thanks for the help! John ~| 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:314773 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: LDAP Query Performance question
Here is a link to information about optimizing and profiling ldap queries to AD: http://robbieallen.com/downloads/RAllen_LDAP_Searching.ppt On Fri, Oct 31, 2008 at 11:25 AM, Burns, John D [EMAIL PROTECTED] wrote: 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:314681 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: LDAP Query Performance question
On Friday 31 Oct 2008, Burns, John D wrote: 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 Firstly, how many is a bunch, how up-to-date does the result for each need to be,and how often is the AD data updated (and how) ? It maybe you can just cache the result, or have the new data for a person pushed to you. -- Tom Chiverton Helping to efficiently incentivize visionary intuitive six-generation environments This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| 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:314682 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: LDAP Query Performance question
It's just a nightly task but it may have 300-400 results each time it runs. I don't think it'll bring down the house or anything it just brought up the question to see if there was a more elegant way of doing it. -Original Message- From: Tom Chiverton [mailto:[EMAIL PROTECTED] Sent: Friday, October 31, 2008 12:51 PM To: cf-talk Subject: Re: LDAP Query Performance question On Friday 31 Oct 2008, Burns, John D wrote: 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 Firstly, how many is a bunch, how up-to-date does the result for each need to be,and how often is the AD data updated (and how) ? It maybe you can just cache the result, or have the new data for a person pushed to you. -- Tom Chiverton Helping to efficiently incentivize visionary intuitive six-generation environments This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at Halliwells LLP, 3 Hardman Square, Spinningfields, Manchester, M3 3EB. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by The Solicitors Regulation Authority. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 2500. For more information about Halliwells LLP visit www.halliwells.com. ~| 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:314684 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: LDAP Query Performance question
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
RE: LDAP Query Performance question
Oh, by the way, regarding my earlier message. We have about 5,500 accounts that I update on a daily basis. Performance isn't really an issue, however, I have plans to cut this down from taking several minutes, to just a few dozen seconds. Mike -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Friday, October 31, 2008 12:03 PM To: cf-talk Subject: RE: LDAP Query Performance question It's just a nightly task but it may have 300-400 results each time it runs. I don't think it'll bring down the house or anything it just brought up the question to see if there was a more elegant way of doing it. ~| 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:314701 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: LDAP Query Performance question
LDAP is optimized for reading, so I would assume that tens of thousands of requests per second shouldn't phase it. (OpenLDAP 2.4 can handle 32,000 per second, (I can't seem to find a number for Active Directory)). speeves On Fri, Oct 31, 2008 at 2:45 PM, Dawson, Michael [EMAIL PROTECTED]wrote: Oh, by the way, regarding my earlier message. We have about 5,500 accounts that I update on a daily basis. Performance isn't really an issue, however, I have plans to cut this down from taking several minutes, to just a few dozen seconds. Mike -Original Message- From: Burns, John D [mailto:[EMAIL PROTECTED] Sent: Friday, October 31, 2008 12:03 PM To: cf-talk Subject: RE: LDAP Query Performance question It's just a nightly task but it may have 300-400 results each time it runs. I don't think it'll bring down the house or anything it just brought up the question to see if there was a more elegant way of doing it. ~| 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:314704 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: LDAP Query Performance question
AD can handle 32,001 ;^) -Original Message- From: Shannon Peevey [mailto:[EMAIL PROTECTED] Sent: Friday, October 31, 2008 3:06 PM To: cf-talk Subject: Re: LDAP Query Performance question LDAP is optimized for reading, so I would assume that tens of thousands of requests per second shouldn't phase it. (OpenLDAP 2.4 can handle 32,000 per second, (I can't seem to find a number for Active Directory)). speeves ~| 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:314705 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: LDAP Query Performance question
My point exactly :) On Fri, Oct 31, 2008 at 3:17 PM, Dawson, Michael [EMAIL PROTECTED]wrote: AD can handle 32,001 ;^) -Original Message- From: Shannon Peevey [mailto:[EMAIL PROTECTED] Sent: Friday, October 31, 2008 3:06 PM To: cf-talk Subject: Re: LDAP Query Performance question LDAP is optimized for reading, so I would assume that tens of thousands of requests per second shouldn't phase it. (OpenLDAP 2.4 can handle 32,000 per second, (I can't seem to find a number for Active Directory)). speeves ~| 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:314707 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
