Tuesday, February 07, 2006

Slow Active Directory Search Using ADO

I was informed by my boss that one of our Intranet web applications which queries our Active Directory for user's information was slow. It is an ASP.Net web application but is using ADODB to query the Active Directory. There are three search filters namely FirstName, LastName and Country. User can use any combination of the three search filters to search for information.

I did a search by providing the FirstName and it took about 35 seconds to return the result. However, subsequent search immediately after the first search took less than 8 seconds. This is probably caused by result caching. I used performance monitor to check on the Domain Controller average disk queue, processor usage and network traffic while running the query. However, I can't find anything wrong with the Domain Controller.

The next thing is to look at the source code which was written by our vendor. At first glance, the code looks perfectly alright until I output the ADO command text. I noticed that wildcard was being used in the search filter. I didn’t notice that because the search values were passed in from another function call. The problem was not really the wildcard itself but where it was placed. Wildcards were placed before and after the search value. For example, if I entered Mich for the FirstName and Jor for the LastName search filters, the command text will be as follow.

Select givenName,sn,telephonenumber,department,L,cn,c from 'LDAP://DC=contoso,DC=com' where givenName='*Mich*' AND sn='*Jor*' AND objectCategory='person'

Although givenName and sn attributes are both indexed in the Active Directory database but because of the wildcard before of the search value, the index becomes useless. This will caused the query to search through every user objects in our Active Directory domain which is about 5000.

Now the question is why didn't we have this problem last time? Well, this web application was implemented two years ago and during that time, only two sites were migrated to Active Directory and the number of user objects was slightly over 1000. Over the years, the number had grown about five times. I guess either user is patient enough to wait for 30 seconds or the usage of this web application; the later has higher possibility (you know what I mean).

We are in the process of improving the web application as to attract more users to use it. Now, what is the solution? Answer is to remove the wildcard added before the search value or give our users patience enhancement lessons (if I am patient enough to do that).

No comments: