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,666
    How many records are returned in your recordset?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  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,666
    Thanks for posting your solution.

    Using Linked tables will definately slow things down.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

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