This post originated from an RSS feed registered with Java Buzz by dion. Original Post: Recursion gone wrong OR Too Much SQL Feed Title: techno.blog(Dion) Feed URL: http://feeds.feedburner.com/dion Feed Description: blogging about life the universe and everything tech Latest Java Buzz Posts Latest Java Buzz Posts by dion Latest Posts From techno.blog(Dion)

One action on the admin side of an application that I am working on was really slow. It would take up to 20 seconds to load.

This action is part of existing legacy software, so again I had to dig in and found the bad performer instantly.

The bad code was building a tree of categories, and worked just fine for tens of categories, or maybe even hundreds, but now the system had THOUSANDS of categories.

As I looked into the code that generated the tree, it didn't take a genius to work out the issue. The function was recursively calling itself as it got to each node in the tree (now build the subtree, now build its subtree, etc etc).

Recursion itself was not the problem, but each time the function was called it was running a new SQL query against the DB to get subtree information. This meant that many, many queries were running against the DB when one could be used.

By changing the action so that it:

1. gets all of the category info first
2. write out the tree using this info

It went subsecond.

Then we migrated from having it ask for the categories from the DB, to grabbing them from cache, and it became insignificant.