Be sure to have a look at my new project Bingy Bongy our first interactive children's book app is going feature a Bat and sign up.
Ok I finally got round to updating the facemash.com.au website I created over a year ago. I thought I'd just checkup on the code as I noticed the site was running very slow.
If you don't know the history of my facemash its a site that I created in a day just as a "proof of concept" and for a bit of a laugh after watching the Social Network Movie.
Far to often I go back to my code and say wow what was I thinking this was a major what was I thinking (I'm blaming the fact that I smashed it up in a day). The Facemash voting log has grown, grown alot! At last count to around 1 184 713, yeah thats right over 1.1millon votes have been casted! I'm surprised its so many although I shouldn't be because the site gets around 400 visits per day and on some days will peak at over 1000.
So I went on a quest to find out why the site was so slow I knew it was something todo with the Facemash Log table since it had over 1.1 million records, turns out I was doing not 1 but 2 SQL Count queries and this was being done for each display picture being display.
At the time I probably though "ah it'll be fine" it won't get that much traffic. Well this is proof, that the KISS principle isn't always the best.
Here is the offending code
public static int GetWinCount(Face face)
{
int count = 0;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString))
{
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandText = "SELECT COUNT(ID) FROM FaceLog WHERE (FaceID1 = @FaceID AND FaceID1Winner = 1) OR (FaceID2 = @FaceID AND FaceID2Winner =1) ";
command.Parameters.Add(new SqlParameter("@FaceID", face.ID));
command.Connection.Open();
count = (int)command.ExecuteScalar();
connection.Close();
}
return count;
}
What started all this code optimisation is over the past weeks, I've been trying out a few different hosting providers as I'm currently paying $99US a month for a VPS server in the US which I feel is too much (I've got several different blogs and websites on not just this one). I've finally settled on Amazon AWS, and I'm quiet pleased Volume snap shots are a nice feature. So it just shows if your like me and you'll got a couple of sites ticking away take 10 minutes to have a look at the old code and make sure you weren't trying to KITS (Keeping It Too Simple)
