Blogged by buffi

Whenever I develop anything really, I often start out by tackling problems by doing them with a really simple approach to avoid premature optimization, since that often makes stuff messy, as well as increasing the development time. When the project finally works the way it’s supposed to you can get always get your hands dirty and optimize it then.

With the new pici in django I realized that showing pictures was sloooooow, and sought out where the problem could be. I then found out that I did a whole lot of unneeded SQL-queries in the view for showing pictures.

Each picture has a link to the next/previous picture on pici, if it’s in a gallery it has a link to the next/previous picture in that gallery and if it’s not an anonymous upload then it also has links to the next/previous picture from that user. Finally there is a link to display a random picture from pici.

My first approach was, whenever displaying a picture, get the previous and next picture in each category (that was represented for that picture) and build the links to the URL’s that way. That meant that pic http://pici.se/25 got links to http://pici.se/24 and http://pici.se/26, a random link to http://pici.se/xxxxx and also other links depending if it was in a gallery or uploaded by a user. This is obviously really stupid. Since at most one link can be clicked the SQL-queries for the other links are unneeded.

The obvious fix is first to remove all of the queries from the “show picture view”. Then change all of the links to stuff like http://pici.se/25/next , http://pici.se/25/next_in_gal and so on. When a link is clicked, the view handles the SQL needed and redirects to the new picture. This avoid unneeded queries and increased performance a whole lot. Adding a http://pici.se/random URL for the randomizer was also quite nice… since there previously wasn’t a way to get a random picture without first visiting a picture, let the view query up a random picture and then click the link. Now just visiting the URL is enough

There is however a downside. Without running the premature SQL, there is no way to check if a picture is the last one in a gallery and so on. One could create a hybrid solution that simply does this check beforehand, but I chose to ignore this issue since it is seldom an issue in this case, and the performance boost was a whole lot. I will go back and revisit the view code for this in a while though to check for more improvements, but right now the pictures show up really fast again, and that is the priority

buffi Programming & scripting, Python, Uninteresting