SQLAlchemy

Recently, while working on the RockClimbing.com codebase, I encountered several situations where data needed to be pre-calculated/cached because on-the-fly calculations were too slow. For example, RockClimbing.com has over 100,000 climbing routes scattered across 35,000 locations.The locations are essentially a tree structure, ranging in specificit... y from general area to a specific wall: North America California Yosemite El Capitan North Face. As you browse different areas, itd be nice to see the total number of climbing routes within each area. For example, if youre viewing North America, on each state wed like to display the total number of climbing routes in that state. In other words, for each node, find all child nodes and then sum the routes attached to those child nodes. Theres a number of ways to map a tree structure to SQL tables. For simplicity, I store the locations as an adjacency list. Adjacency lists make it easy to insert and reorder nodes, although traversing the tree (especially downwards) can be slow because you have to walk through all the nodes in order to identify the children. An index helps, but its still not fast, especially when you need to traverse multiple levels deep. Trying to solve it with Python: My initial prototype looked something like this: def recursive_child_locations(location): child_l = [] if len(location.children) 0: for child in location.children: child_l.extend(recursive_child_locations(location)) return child_l recursive_child_route_count = db.session.query(Routes).filter(Routes.location. \ in_(location, recursive_child_locations(self))).count() This worked fine with my normal test dataset of 100 locations, but became too slow once I increased the test dataset to 50,000 locations. Switching to a while loop would improve things slightly since Python doesnt support tail recursion. But the bigger problem is that accessing location.children is expensive. My location model is configured with lazy='dynamic' which forces SQLAlchemy to issue a new query for every ...Read More