Recursive calls and SQL queries are very slow


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Recursive calls and SQL queries are very slow

  1. #1
    Join Date
    Oct 2005
    Posts
    33

    Recursive calls and SQL queries are very slow

    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:

    Code:
    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:

    Code:
    '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
    Last edited by nmxnmx; 11-07-2008 at 10:45 AM.

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

  3. #3
    Join Date
    Oct 2005
    Posts
    33
    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
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    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, 10:42 AM
  2. SQL Connectivity with Linked MS Access DB
    By David Croft in forum Database
    Replies: 0
    Last Post: 08-13-2003, 01:45 PM
  3. Blocking problem with DSN-less connection
    By Adam Dawes in forum VB Classic
    Replies: 3
    Last Post: 12-21-2000, 12:50 PM
  4. Replies: 3
    Last Post: 11-20-2000, 10: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
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

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