title: What is the fastest way to do database queries in a recursive function?
body: <p>A piece of my program queries a database to find all the children of a given elements, and all of their children, etc, until there are no more children and I get to the leaf elements.  The database uses two columns to store the information I want.  One column with the id of the element, another column with the id of the child element.  An average use case results in a tree with over 500 elements, and it takes over 30 seconds to finish all the queries.  Is there something I could do to optimize the queries and reduce the bottleneck on the server?  </p>
  
  <pre><code>cursor = conn.cursor()
  rootNode = Node(initialId)
  nodeList.append(baseNode) # all nodes go into a list for debugging output
  buildTree(baseNode, cursor)
  
  def buildTree(self, node, cursor):
      query = "SELECT childId FROM Pairs WHERE parentId=?"
      cursor.execute(query, node.value)
      results = cursor.fetchall()
      for entry in results: # for each result returned, make a new node for each child
                            # and then query each child to see if it has children
          if entry[0] is not None:
              newNode = Node(entry[0], parent=node)
              nodeList.append(newNode)
              buildTree(newNode, cursor)
  </code></pre>
  
  <p>Node is short class that acts as a data structure to store a node's id, any children it might have, and the parent node.  I'm using <code>pyodbc</code> to connect to SQL Server '08.    </p>