DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005

    Recursive calls and SQL queries are very slow


    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, _
            ' The recursive call is done here
            Call addSupplies(dbConn, tmpRs.Fields("deptoCode").Value)
            Call tmpRs.MoveNext
        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, _
            Call tmpRs.MoveNext
        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

    Last edited by nmxnmx; 11-07-2008 at 09:45 AM.

  2. #2
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    How many records are returned in your recordset?

  3. #3
    Join Date
    Oct 2005
    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

  4. #4
    Join Date
    Apr 2007
    Sterling Heights, Michigan
    Thanks for posting your solution.

    Using Linked tables will definately slow things down.

Similar Threads

  1. SQL Calls in VB.net
    By MOWS in forum .NET
    Replies: 4
    Last Post: 08-22-2006, 09:42 AM
  2. SQL Connectivity with Linked MS Access DB
    By David Croft in forum Database
    Replies: 0
    Last Post: 08-13-2003, 12:45 PM
  3. Blocking problem with DSN-less connection
    By Adam Dawes in forum VB Classic
    Replies: 3
    Last Post: 12-21-2000, 11:50 AM
  4. Replies: 3
    Last Post: 11-20-2000, 09:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center
Latest Articles
Questions? Contact us.
Web Development
Latest Tips
Open Source

   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.