Speeding up trac's response time

I’ve been trying to speed up an installation of trac over the last few days. The web interface took ages to display each of the directories or files within the subversion repository. But this one wasn’t too big. The only change to the subversion repository is that we started using a vendor branch imported into our main repository using svm

So, after a few hours trying different solutions, and reading trac’s source code, I think I got where the bottleneck was.Well, it was http://www.sqlite.org/download.html which was causing the bottleneck. Trac uses an object CachedRepository to access the repositories. Whenever we want to get the chagesets, a function to synchronize the repository is called:

class CachedRepository(Repository):
  def get_changeset(self, rev):
    if not self.synced:
      self.sync()
      self.synced = 1
      return CachedChangeset(self.repos.normalize_rev(rev), self.db, self.authz)

and such method, sync(), makes a call to:

youngest_stored = self.repos.get_youngest_rev_in_cache(self.db)

which is all this:

def get_youngest_rev_in_cache(self, db):
    """Get the latest stored revision by sorting the revision strings
    numerically
    """
    cursor = db.cursor()
    cursor.execute("SELECT rev FROM revision ORDER BY -LENGTH(rev), rev DESC LIMIT 1")
    row = cursor.fetchone()
    return row and row[0] or None

And that SQL query was taking around 1-2 seconds each time it was executed. It happened that we were running an old version of sqlite and pysqlite, so a ./cofigure && make && make install using the recommended installation saved my day :-)

Hope it is useful to anybody if it gets indexed by Google.