Saturday, June 14, 2008 6:43:12 PM (GMT Standard Time, UTC+00:00)

Just a little whoopsie. I knew I had a small bottleneck somewhere in the code where I 'crunch' and validate the pending data submitted by users. It was taking 100ms for each item submitted and that is just not acceptable at all when you're hoping to support a few hundred users at least on the current machine.

I'm currently re-designing the database so it can build itself over the course of page requests, because the page request speed is slow. But I decided to give the crunching process a quick profile because there was no way it should be taking so long per item!

Whoops, it turns out that every single item was being followed by a "DELETE FROM [X] WHERE [Id] = Y" statement. Instead of storing up my deletes until the end and doing one delete statement to delete 100 items, I was doing 100 delete statements and boy are they expensive!!

Problem solved and with the new indexing system in place, it's down to taking 6ms per item crunched. 100ms to 6ms - there is no real contest here is there? The moral of the story? If you think there is a problem in performance, don't just hope you'll find it later on - get the profiler out and see why it is being so slow - it's quite motivating, if only a little embarassing

Just as a footnote, that means my six hour migration process outlined below now takes just under 20 minutes. 20 minutes to crunch a year's data for a single user? If only I could get page generation this fast... now there is something to push towards I guess.

With novice mistakes like this still being present in my database code, it's not beyond the stretch of my imagination to think that it might be possible.