Entries from September 2005 ↓

You’ve seen the title; it’s true.

I’ve been running an “administration” query to modify a table with around 190,000 records in. This table, segments, contains a status column called Coded. This value is derived from another table, faults. If any faults belonging to a segment has a coded status of 0 then the segment coded value is also 0.

My query did an update on each row in the segments table, retrieving a Count of how many faults there are with a coded status of 0 that belong to the current segment. If the Count is greater than 0 then the segment coded is set to 0, otherwise to 1.

It ran for over 2 hours without any sign of finishing.

I changed the query to do a simple case statement with an Exists on the faults table where Coded = 0 and it’s just finished in 4 minutes. Unbelivable.

Update

I thought I should give a bit of an explanation as to why this is happening. It basically all boils down to the way the two functions operate that makes the biggest difference. A Count statement counts every row that matches the where statement, so even if you only need to know about one record (like in my case) it’ll still read every other row too. On the other hand the Exists function will return as soon as its conditions are met, so if the first row it finds matches it’ll only read one row.

I’m not a big fan of using the Microsoft.VisualBasic assembly, I avoid it at all costs basically.

As we all know, the visual basic assembly comes with a large collection of built-in functions one of which is the DateDiff function. This takes two dates and a comparison value (e.g. “d” for day) and then spits out the difference.

Here’s the same thing just minus the visual basic usage:

DateTime firstDate = DateTime.Now;
DateTime secondDate = new DateTime(2005, 8, 20);
TimeSpan difference = secondDate.Subtract(firstDate);

// days: difference.Days

Launch problems, they always occur. Yesterday (1st September) was no exception, we launched this years Swift Leisure website; a bloody huge site. As soon as it got up there it fell over, which was unfortunate. After some investigation we narrowed it down to the Url Rewriter that we have in place (unfortunately, solely my work!).

The big problem with it was that it replaces the Request.ApplicationPath in any requested path with a blank string to remove any unnecessary parts of the path; “SwiftLeisure2006/Caravans/Abbey” became “Caravans/Abbey”. This worked fine on our local setup but we didn’t account for possible changes in the Request.ApplicationPath when the site is situated directly in the root instead of a sub-folder. The Request.ApplicationPath became just “/” which means “/Caravans/Abbey” became “CaravansAbbey” instead of “Caravans/Abbey”. Simple fix once we figured this out and found any similar sections throughout our controls.

Live and learn, live and learn.

I’ve just got back from two holidays, with a three day gap between the two. While it is, of course, nice to have holidays. It’s always a royal pain in the arse when you get back and find that you have a pile of bugs to fix that people found while you’re away. It also doesn’t help that the launch of our biggest website, Swift Leisure, landed two days after my return from my second holiday. So you could cut the tension with a knife when I arrived. Luckily everything went just about to plan, the finished product is at http://www.swiftleisure.com.

For my first holiday, I went down to Cornwall with my girlfriend Sara. Lovely, lovely place and we had gorgeous weather too. Stayed on a nice little converted farm called Badham Farm, really quiet and relaxing. We were only about 20 minutes from the coast and near by towns and only a hour and a half from Lands End too. We visited the Eden Project which is absolutely amazing, unbelievable that they’ve been able to achieve it all. My favorite little excursion was to a cider farm, where you could see all the workings of how they produce cider and various other alcoholic drinks (brand, wine etc…). We brought back a fair few bottles to say the least.

The second holiday was 4 days in a field at Leeds Festival. Once again, fantastic. The bands were varied but mostly good. Iron Maiden were fantastic, as were the Pixes, Queens of the Stone Age and the Foo Fighters. Not really into the “newer” bands that were there though I’m really annoyed I managed to miss Death From Above 1979. I can’t praise those two guys enough… If you haven’t heard of them, get their CD! NOW!