Slow LDAP Query

Slow LDAP Query

Post by Kenneth H. » Tue, 08 Mar 2005 22:49:54


his is a multi-part message in MIME format.


I am working on a LDAP add-in for Outlook and I am having performance issues. The query is exceedingly long to the point the add-in is not usable. Below is the code any help will be greatly appreciated. ;-)

Thanks,
________________________________________________________________________________________________________________________________
Private Sub bSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bSearch.Click
Dim entry As New DirectoryServices.DirectoryEntry("LDAP://servername:389/ou=People,dc=ldap,dc=nrl,dc=navy,dc=mil")
Dim mySearcher As New System.DirectoryServices.DirectorySearcher(entry)
Dim result As System.DirectoryServices.SearchResult
Dim Data1, Data2, Data3, Data4, Data5, Data6, Data7, Data8 As String
Dim val1, sField1, sField2 As String
Dim TestVal1, TestVal2, TestVal3, TestVal4, TestVal5, TestVal6, TestVal7 As String
Me.DataSet11.Clear()
val1 = Me.txtBox1.Text
sField1 = Me.ComboBox1.Text
If sField1 = "Full Name i.e. First M. Last" Then
sField2 = "cn"
ElseIf sField1 = "Code" Then
sField2 = "departmentNumber"
ElseIf sField1 = "Phone Number" Then
sField2 = "telephoneNumber"
ElseIf sField1 = "Building Number" Then
sField2 = "buildingName"
ElseIf sField1 = "Employee Type" Then
sField2 = "emplyeeType"
End If
If val1 = "" Then
val1 = "*"
End If
If Me.ComboBox1.Text = "" Then
MsgBox("Please slect an item form the combo box!", MsgBoxStyle.OKOnly, "No Selection!")
GoTo Done
End If

mySearcher.Filter = "(&(" & sField2 & "=" & val1 & ")(cn=*)(departmentNumber=*)(buildingName=*)(roomNumber=*)(telephoneNumber=*)(mail=*)(employeeType=*))"
For Each result In mySearcher.FindAll
If IsNothing(result.GetDirectoryEntry().Properties("cn").Value()) Then
Data1 = "NA"
Else
TestVal1 = (result.GetDirectoryEntry().Properties("cn").Value.GetType.ToString)
If TestVal1 = "System.String" Then
Data1 = (result.GetDirectoryEntry().Properties("cn").Value)
ElseIf TestVal1 = "System.Object[]" Then
Data1 = (result.GetDirectoryEntry().Properties("cn").Value(0))
Else
End If
End If

If IsNothing(result.GetDirectoryEntry().Properties("departmentNumber").Value()) Then
Data2 = "NA"
Else
TestVal2 = (result.GetDirectoryEntry().Properties("departmentNumber").Value.GetType.ToString)
If TestVal2 = "System.String" Then
Data2 = (result.GetDirectoryEntry().Properties("departmentNumber").Value)
ElseIf TestVal2 = "System.Object[]" Then
Data2 = (result.GetDirectoryEntry().Properties("departmentNumber").Value(0))
Else
End If
End If

If IsNothing(result.GetDirectoryEntry().Properties("buildingName").Value()) Then
Data3 = "NA"
Else
TestVal3 = (result.GetDirectoryEntry().Properties("buildingName").Value.GetType.ToString)
If TestVal3 = "System.String" Then
Data3 = (result.GetDirectoryEntry().Properties("buildingName").Value)
ElseIf TestVal3 = "System.Object[]" Then
Data3 = (result.GetDirectoryEntry().Properties("buildingName").Value(0))
Else
End If
End If

If IsNothing(result.GetDirectoryEntry().Properties("roomNumber").Value()) Then
Data4 = "NA"
Else
TestVal4 = (result.GetDirectoryEntry().Properties("roomNumber").Value.GetType.ToString)
If TestVal4 = "System.String" Then
Data4 = (result.GetDirectoryEntry().Properties("roomNumber").Value)
ElseIf TestVal4 = "System.Object[]" Then
Data4 = (result.GetDirectoryEntry().Properties("roomNumber").Value(0))
Else
End If
End If

If IsNothing(result.GetDirectoryEntry().P
 
 
 

Slow LDAP Query

Post by Kenneth H. » Tue, 08 Mar 2005 23:59:00

his is a multi-part message in MIME format.


FYI, In another app we are working on that is web based and uses Pearl the same query runs in 2.5 seconds comaped to this one that takes 30.
Note: This query is only pullingback about 500 records.
"Kenneth H. Young" < XXXX@XXXXX.COM > wrote in message news: XXXX@XXXXX.COM ...
I am working on a LDAP add-in for Outlook and I am having performance issues. The query is exceedingly long to the point the add-in is not usable. Below is the code any help will be greatly appreciated. ;-)

Thanks,
________________________________________________________________________________________________________________________________
Private Sub bSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bSearch.Click
Dim entry As New DirectoryServices.DirectoryEntry("LDAP://servername:389/ou=People,dc=ldap,dc=nrl,dc=navy,dc=mil")
Dim mySearcher As New System.DirectoryServices.DirectorySearcher(entry)
Dim result As System.DirectoryServices.SearchResult
Dim Data1, Data2, Data3, Data4, Data5, Data6, Data7, Data8 As String
Dim val1, sField1, sField2 As String
Dim TestVal1, TestVal2, TestVal3, TestVal4, TestVal5, TestVal6, TestVal7 As String
Me.DataSet11.Clear()
val1 = Me.txtBox1.Text
sField1 = Me.ComboBox1.Text
If sField1 = "Full Name i.e. First M. Last" Then
sField2 = "cn"
ElseIf sField1 = "Code" Then
sField2 = "departmentNumber"
ElseIf sField1 = "Phone Number" Then
sField2 = "telephoneNumber"
ElseIf sField1 = "Building Number" Then
sField2 = "buildingName"
ElseIf sField1 = "Employee Type" Then
sField2 = "emplyeeType"
End If
If val1 = "" Then
val1 = "*"
End If
If Me.ComboBox1.Text = "" Then
MsgBox("Please slect an item form the combo box!", MsgBoxStyle.OKOnly, "No Selection!")
GoTo Done
End If

mySearcher.Filter = "(&(" & sField2 & "=" & val1 & ")(cn=*)(departmentNumber=*)(buildingName=*)(roomNumber=*)(telephoneNumber=*)(mail=*)(employeeType=*))"
For Each result In mySearcher.FindAll
If IsNothing(result.GetDirectoryEntry().Properties("cn").Value()) Then
Data1 = "NA"
Else
TestVal1 = (result.GetDirectoryEntry().Properties("cn").Value.GetType.ToString)
If TestVal1 = "System.String" Then
Data1 = (result.GetDirectoryEntry().Properties("cn").Value)
ElseIf TestVal1 = "System.Object[]" Then
Data1 = (result.GetDirectoryEntry().Properties("cn").Value(0))
Else
End If
End If

If IsNothing(result.GetDirectoryEntry().Properties("departmentNumber").Value()) Then
Data2 = "NA"
Else
TestVal2 = (result.GetDirectoryEntry().Properties("departmentNumber").Value.GetType.ToString)
If TestVal2 = "System.String" Then
Data2 = (result.GetDirectoryEntry().Properties("departmentNumber").Value)
ElseIf TestVal2 = "System.Object[]" Then
Data2 = (result.GetDirectoryEntry().Properties("departmentNumber").Value(0))
Else
End If
End If

If IsNothing(result.GetDirectoryEntry().Properties("buildingName").Value()) Then
Data3 = "NA"
Else
TestVal3 = (result.GetDirectoryEntry().Properties("buildingName").Value.GetType.ToString)
If TestVal3 = "System.String" Then
Data3 = (result.GetDirectoryEntry().Properties("buildingName").Value)
ElseIf TestVal3 = "System.Object[]" Then
Data3 = (result.GetDirectoryEntry().Properties("buildingName").Value(0))
Else
End If
End If

If IsNothing(result.GetDirectoryEntry().Propert
 
 
 

Slow LDAP Query

Post by v-raygo » Wed, 09 Mar 2005 14:58:31

I noticed following line in your code:
mySearcher.Filter = "(&(" & sField2 & "=" & val1 &
")(cn=*)(departmentNumber=*)(buildingName=*)(roomNumber=*)(telephoneNumber=*
)(mail=*)(employeeType=*))"

You use many "*" in your code, that mean you want to perform this search in
a whole domain scope without any specific information. As we know, the more
specific filter is, the quicker you will get the answer. So I suggest you
put more textbox/combobox to let user select scope he would search in order
to get better performance.
Web based application always performs its action in server side and your
server has these information stored locally, that is why you get better
performance when it is web based.

Thanks,
Rhett Gong [MSFT]
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security

This posting is provided "AS IS" with no warranties and confers no rights.
 
 
 

Slow LDAP Query

Post by Kenneth H. » Wed, 09 Mar 2005 20:55:20

I had already done so in an attempt to narrow down the problem and I am
not populating the dataGrid1 either. Now the query runs in 1 second. So it
appears that the drag on the app is updating the dataGrid1 with the results.
I have also added "PropertiesToLoad.Add" for the fields I wanted queried.
FYI the Pearl web based application is static web pages that kicks off a new
query every time it's used and there is no server stored information. The
script runs just as fast on a desktop as from the web page.

So I guess now the question is what is the best method to populate and
display the queried information?

Thank you for your continued assistance. :-)
______________________________________________________________________________________________________

Private Sub bSearch_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles bSearch.Click
Dim entry As New
DirectoryServices.DirectoryEntry("LDAP://ldap.nrl.navy.mil:389/ou=People,dc=ccs,dc=nrl,dc=navy,dc=mil")
Dim mySearcher As New
System.DirectoryServices.DirectorySearcher(entry)

mySearcher.PropertiesToLoad.Add("cn")
mySearcher.PropertiesToLoad.Add("departmentNumber")
mySearcher.PropertiesToLoad.Add("buildingName")
mySearcher.PropertiesToLoad.Add("roomNumber")
mySearcher.PropertiesToLoad.Add("telephoneNumber")
mySearcher.PropertiesToLoad.Add("mail")
mySearcher.PropertiesToLoad.Add("employeeType")

Dim Data1, Data2, Data3, Data4, Data5, Data6, Data7, Data8 As String
Dim val1, sField1, sField2 As String
Dim TestVal1, TestVal2, TestVal3, TestVal4, TestVal5, TestVal6,
TestVal7 As String
Me.DataSet11.Clear()
val1 = Me.txtBox1.Text
sField1 = Me.ComboBox1.Text
If sField1 = "Full Name i.e. First M. Last" Then
sField2 = "cn"
ElseIf sField1 = "Code" Then
sField2 = "departmentNumber"
ElseIf sField1 = "Phone Number" Then
sField2 = "telephoneNumber"
ElseIf sField1 = "Building Number" Then
sField2 = "buildingName"
ElseIf sField1 = "Employee Type" Then
sField2 = "emplyeeType"
End If
If val1 = "" Then
val1 = "*"
End If

If Me.ComboBox1.Text = "" Then
MsgBox("Please slect an item form the combo box!",
MsgBoxStyle.OKOnly, "No Selection!")
GoTo Done
End If


mySearcher.Filter = "(&(" & sField2 & "=" & val1 & ")(mail=*))"
'Only return 'mail' fields that are not null.

mySearcher.SearchScope = DirectoryServices.SearchScope.OneLevel
Dim result As System.DirectoryServices.SearchResult
For Each result In mySearcher.FindAll()



Next

MsgBox("Query complete!", MsgBoxStyle.OKOnly, "")
Done:
End Sub


"Rhett Gong [MSFT]" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Slow LDAP Query

Post by v-schan » Thu, 10 Mar 2005 21:59:42

Hi Kenneth,

From your former code snippet, you're currently insert all the datas
queried from the directorySearcher into a DataSet's DataTable and bind the
datatable to a DataGrid, yes?

I think this is OK. Also, you can just use a simple DataSet rather than a
strong named one. Or you can just use a DataTable (no dataset needed) and
directly bind the datatable to the DataGrid.

In addition, we can also put our directory searching work into a separate
worker thread when working in a desktop UI application, that'll help
improve the UI's interactive performance when the result is very large
which may take quite some time to finish. Here is a certain tech article
discussing on the similar topic,

http://www.yqcomputer.com/
ml/dotnetadsearch.asp

Hope also helps. Thanks,

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
 
 

Slow LDAP Query

Post by Kenneth H. » Thu, 10 Mar 2005 23:44:10


One thing I have notice is that if I don't write the results out to a
text file, DataSet or TextBox the query (with 500 results) completes in 1.5
seconds. Anything I try to pipe the results to increases the query to
roughly 35 seconds for the same 500 results.

Thanks once again for the continued help!
 
 
 

Slow LDAP Query

Post by Fred Hirsc » Fri, 11 Mar 2005 00:34:42

I think you will find this rewrite much more efficient that the one you had
previously:

Me.DataSet11.EnforceConstraints = False ' not sure if this will help in this
situation...

' Just the for loop
Dim results = mySearcher.FindAll As SearchResultCollection
For Each result In results
Dim props As PropertyCollection = result.GetDirectoryEntry().Properties

Data1 = GetValue(props, "cn")
Data2 = GetValue(props, "departmentNumber")
Data3 = GetValue(props, "buildingName")
Data4 = GetValue(props, "roomNumber")
Data5 = GetValue(props, "telephoneNumber")
Data6 = GetValue(props, "mail")
Data7 = GetValue(props, "employeeType")

Me.DataSet11.Employees.AddEmployeesRow(Data1, Data2, Data3, Data4, Data5,
Data6, Data7)
Me.DataSet11.AcceptChanges()
Next

Me.DataSet11.EnforceConstraints = True

...

Private Function GetValue(ByRef props As
System.DirectoryServices.PropertyCollection, ByVal name As String) As String
Dim propVal as Object = props(name).Value()

If IsNothing(propVal) Then
GetValue = "NA"
Else
Dim TestVal As String = props(name).Value.GetType.ToString

If TestVal = "System.String" Then
GetValue = propVal
ElseIf TestVal = "System.Object[]" Then
GetValue = propVal(0)
Else
GetValue = String.Empty ' This may not be what you want...
End If
End If
End Function

You may also want to look at using XML string with StringBuilder instead of
DataSet. You could then load this string result into a dataset in one shot.

Fred




1.5


the
a
and
separate
article
http://www.yqcomputer.com/
 
 
 

Slow LDAP Query

Post by v-schan » Fri, 11 Mar 2005 14:03:32

You're Welcome Kenneth.

I'm glad that our suggestions may of assistance. In addition, you can also
have a search in the group to see whether any other community guys have
provided some similiar experience on this.

Thanks & regards,

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)