VB6 & Large ADO RecordSet Issues

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: StephenMcC (StephenMcC_at_discussions.microsoft.com)
Date: 11/04/04 

Date: Thu, 4 Nov 2004 03:24:02 -0800

Hi All,

I'm performing a query on Active Directory using ADO, passing a command text
of the mixed SQL and LDAP dialects but I seem to be having trouble witht
large recordsets. We're in the porgress of migrating an online app from
Win2000 to Win2003 and need to carry-out performance testing, I've written
scripts to populate the AD with a base line collection of users, the AD
should now contain an average of 2.5 million users. I've written a script to
count these to confirm all have been added, so we can be sure that the
performance tests have indeed been run against the large user base.

The count script works fine for small numbers of users, from a few hundred
to a few hundred thousand, but when it comes to counting the 2.5 million it
seems to have trouble. I'm not sure if the servers bottling out or if
something else is failing causing the problem. The app seems to hit the total
I expect (as i'm writing a count out to a label) then keeps on counting and
eventually freezing/hanging.

Any one got any ideas on how to tackle such problems with ADO, anyways to
tweak ADO to best handle large RSs. I've applied paging to the connection etc
to handle 1000 records at at time so this I think should be fine. Would my
problem be with the ADOCommand Searchscope property being set to 2 (SCOPE AD
SUBTREE) ??

I've included my code below, any ideas/comments welcome (sorry to multi-post),

Thanks,
Stephen

.

    On Error Resume Next

    Dim lCount: lCount = 0
    Dim oCon As New ADODB.Connection
    Dim oCom As New ADODB.Command
    Dim oRS As New ADODB.Recordset
    Dim sSQL As String: sSQL = txtSQL
    Dim sErProgress As String
    
    Set oCom = CreateObject("ADODB.Command")
    Set oCon = CreateObject("ADODB.Connection")
    oCon.Provider = "ADsDSOObject"
    oCon.Open "Active Directory Provider"
    sErProgress = sErProgress & "Create db conn:" & Err.Number & vbCrLf
    
    Set oCom.ActiveConnection = oCon
    sErProgress = sErProgress & "Set ActiveConnection:" & Err.Number & vbCrLf
    
    oCom.Properties("Page Size") = 1000
    oCom.Properties("Searchscope") = 2 'SCOPE SUBTREE
    sErProgress = sErProgress & "Set command properties:" & Err.Number &
vbCrLf
    
    oCom.CommandText = sSQL
    sErProgress = sErProgress & "Set command text:" & Err.Number & vbCrLf

' oCom.CommandText = _
' "SELECT CN FROM
'LDAP://SERVER:PORT/OU=Dv2,OU=organizations,OU=UIv,CN=Dv2,O=3GN' WHERE
objectCategory='user'"
    
    Set oRS = oCom.Execute
    sErProgress = sErProgress & "Set record set:" & Err.Number & vbCrLf
    
    If Err.Number = 0 Then
        oRS.MoveFirst
        sErProgress = sErProgress & "RE.MoveFirst:" & Err.Number & vbCrLf
        Do Until oRS.EOF
            lCount = lCount + 1
            lblCount.Caption = "Count: " & Format$(lCount, "###,###,###")
            
            oRS.MoveNext
            
            ' Refresh the form and access to it
            Me.Refresh
            DoEvents
            
            If bCancelCount Then Exit Do
        Loop
    Else
        MsgBox "Error executing query: " & Err.Description & " " & Err.Number
    End If
    
    sErProgress = sErProgress & "after loop:" & Err.Number & vbCrLf
    
    MsgBox "Done counting in '" & C_DefaultContainerADsPath & "', " &
Format$(lCount, "###,###,###") & " objects returned!"
    MsgBox Err.Description & " : " & Err.Number
    MsgBox "sErProgress:" & sErProgress

    Set oRs = Nothing
    Set oCom = Nothing
    Set oCon = Nothing

Relevant Pages

  • Large ADO RecordSet Issues
    ... I'm performing a query on Active Directory using ADO, ... seems to have trouble. ... Dim oCon As New ADODB.Connection ...
    (microsoft.public.vb.database)
  • Re: mailmerge and sql
    ... means that you will not be able to see them in a database you open using the ... I believe you may have to use DAO instead of ADO to ... then creates a View containing a UNION query. ... Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)
  • Requery of Listbox does not display new data
    ... add a record to the database. ... Then the Lisbox control's requery method is ... The ADO command is run using a connection string to the mdb containing the ... Dim cmd As ADODB.Command ...
    (microsoft.public.access.formscoding)
  • Re: LongText not supported as output parameter
    ... Text and nText and to not use varcharor nvarcharat first; ... don't use automatic instantiation with ADO; ... Dim cnn as new adodb.connection ... Dim prm as New adodb.parameters ...
    (microsoft.public.sqlserver.programming)
  • Re: Member or Data Member not Found
    ... you've removed the reference to ADO. ... your declaration for tdf is incorrect. ... 2002 only references ADO, but you can add a reference to DAO. ... You must disambiguate as Dim rst As DAO.Recordset. ...
    (microsoft.public.access.formscoding)