SemanticScuttle performance optimizations

This week I worked on lowering the number of queries that SemanticScuttle needs to deliver a bookmark listing page.

Just looking at the front page of a SemanticScuttle instance with 10 or more bookmarks, not logged in, caused 37 SQL queries to be fired to the database. If you are logged in, doing the same action took SQL 54 queries!

After my optimizations, it only takes 8 queries for the front page for anonymous people and 22 for users that are logged in. The logged-in count can easily be lowered to 13 by combining the watch queries that are sent for each single bookmark to be displayed into one.

I also tried running SemanticScuttle with 32000 bookmarks which actually worked, but I got response times of over 1 second. Problem here seems to be that SemanticScuttle uses COUNT() and DISTINCT for some queries. This cannot be easily fixed, only by i.e. putting those counts as hard-coded values into the bookmark rows and updating them whenever the database changes, or doing that via a cron job.

So the next feature release of SemanticScuttle (0.97) should feel a bit snappier, especially with many concurrent users requesting pages on your server.

Written by Christian Weiske.

Comments? Please send an e-mail.