Recursive calls and SQL queries are very slow

[nmxnmx]

Hi

I need fill up the nodes of a treeView with data from a Data base table. The data in the database table are in hierarchycal form like this:

nodeID    nodeParentID
   1                0
   2                1
   3                2
   4                1
   5                1
   6                5

I fill up the treeView with a recursive function, making reiterative queries. It works, but it's extremely slow!

There is another way to speed this up?

I show you my recursive function:

'The function fill up a treeView of products (supplies) that belong to certains departments(depto) in a store.
Private Sub addSupplies(dbConn As ADODB.Connection, _
                                   codeParent As String)
    Dim tmpRs As ADODB.Recordset
    Dim tmpPrm As ADODB.Parameter
    Dim tmpCmd As ADODB.Command

   'treeViewSupplies is the name of the treeView Control

    Set tmpRs = New ADODB.Recordset
    Set tmpPrm = New ADODB.Parameter

    'The queries are stored in an Access database

    With tmpPrm
        .Name = "codeParent"
        .Type = adVarChar
        .Size = Len(codeParent)
        .Direction = adParamInput
        .Value = codeParent
    End With

    Set tmpCmd = New ADODB.Command
    With tmpCmd
        Set .ActiveConnection = dbConn
        .CommandText = "qrySearchDepto"
        .CommandType = adCmdStoredProc
        Call .Parameters.Append(tmpPrm)
    End With

    ' This is the SQL code for this stored query: _
      SELECT deptoCode, deptoDesc FROM tblDepto WHERE deptoIdParent=[codeParent]
    Call tmpRs.Open(tmpCmd, , adOpenStatic)

   'First the departments are put in the treeView
    Do While Not tmpRs.EOF
        Call treeViewSupplies.Nodes.Add(codeParent, _
                                    tvwChild, tmpRs.Fields("deptoCode").Value, _
                                    tmpRs.Fields("deptoDesc").Value)

        ' The recursive call is done here
        Call addSupplies(dbConn, tmpRs.Fields("deptoCode").Value)

        Call tmpRs.MoveNext
    Loop
    Call tmpRs.Close

    ' Once a department doesn't have "children", the treeView is filled up _
       with products that belong to it.
    With tmpCmd
        .CommandText = "qrySearchSuppliesByParent"
    End With

    ' This is the SQL code for this stored query: _
      SELECT supplyCode, supplyDesc FROM tblSupplies WHERE deptoIdFK=[deptoCode]
    Call tmpRs.Open(tmpCmd, , adOpenStatic)
    set tmpPrm = nothing
    Do While Not tmpRs.EOF
        Call treeViewSupplies.Nodes.Add(codeParent, tvwChild, _
                                        tmpRs.Fields("supplyCode").Value, _
                                        tmpRs.Fields("supplyDesc").Value)
        Call tmpRs.MoveNext
    Loop
    Call tmpRs.Close

    Set tmpRs = Nothing
End Sub

I've tried to change the parametrized queries by SQL code in VB but there is no diference.

Sorry for my bad english

Thanks

[Hack]

How many records are returned in your recordset?

[nmxnmx]

I've found the solution.

The problem was that the tables are linked from access to an Oracle database. I tested the same code but with the table completely in access without links and it run fast

Thank you

[Hack]

Thanks for posting your solution.

Using Linked tables will definately slow things down.