Luke 16

Luke 16 is a fascinating chapter. Jesus starts off with a parable in which, at a casual read, he seems to be commending devious behaviour, then he tosses in a single sentence about divorce before launching into a story with a very metaphysical flavour which differs radically from his normal down-to-earth parables.

We studied this passage at Bible study this week and our rector John preached on it today. I struggled a bit to understand what Jesus was really saying about money in the first parable: is he really saying that using money to win friends is good, regardless of whose money it actually is? Or is he using hyperbole: even a dishonest manager can use his resources to make friends: how much more should we do this? Another alternative is that the manager was cutting his commission. Or perhaps these were bad debts and he figured by cutting them, they’d be more likely to be paid, and at the same time he’d earn the friendship of the debtors, and the commendation from the rich man for making the best of a bad debt. Interestingly my wife had no problem with the parable. She read the final interpretation.

Whatever the meaning intended by Jesus, we can be sure that he was not condoning dishonest behaviour. The Pharisees reacted badly to this parable — I guess they read it as an attack on their high regard for material wealth.

The second parable is curious because it uses Jewish pictures of heaven and hell in a way that I can’t recall in any other parables. Jesus’ other recorded parables used scenarios from life: banquets, journeys, weddings and so on. So why did Jesus choose this imagery?

He makes a very pointed comment at the end of the parable of course: “If they do not hear Moses and the Prophets, neither will they be convinced if someone should rise from the dead.” The message of the parable is also about the motivations of the heart: is it God or money that you are following? I like the picture and scenario: it’s very vivid! But I would still like to know why Jesus chose this imagery.

And finally, I wish I knew why Luke chose to sandwich the divorce passage between these two parables!

My first road race: Longford – Campbell Town

Today I raced my first ever road race, a 64km flat course from Longford to Campbell Town, in Tasmania. As part of my preparation I looked up the course profile (flat, bar one small climb about 10km from the finish), and prepared a comprehensive list of excuses, which I understand are an essential part of a road rider’s preparation. Two of my friends, Phil and Rob, were also racing. We’d all ridden a lot together in Hobart and Rob and Phil had raced in Hobart. But we had heard tales that riders from the North of the state (Longford is in the North) were tough and fast — there are 5 or 6 riders in the Pro peleton from those parts — but I don’t think we really appreciated just how fast and hard they were!

Phil and I are roughly comparable in racing ability, as far as we could tell, and as Phil raced B grade in Hobart, I naively decided that was the grade for me. Rob decided to stick with D grade — a sensible choice I think! The lower grades started out a few minutes ahead, with 1 minute between C and B, and 3 minutes to A. I snuck a look at the registration sheet before the race and there were only 6 or 7 riders in B, and 20 or more in each of D, C and A. I was starting to get worried because that meant much more work in the wind, with less recovery time drafting other riders. All too soon our grade was up to start and immediately we hit 40+km/h. It seemed that some of the riders wanted to catch C grade before A grade caught us. Within 5km, we were sitting on 45-50km/h and my heart rate was on 175 as I frantically tried to hold my position in the bunch. I glanced at Phil and by the look on his face he was in trouble too. All too soon the inevitable happened — we started to drift off the back of the B grade group, and my heart rate way up high I knew I didn’t have that reserve to claw my way back on.

One other B grader dropped back with Phil and I and no more than a couple of km later we heard a toot from the chase car informing us that the A grade pack were flying up behind us. We jumped on the back of that bunch and the shelter from the wind helped a bit with recovery, as it was a much larger group. My heart rate dropped below 150 and I started to think I might have a chance to hold on. Phil and I sat on the back of the group and stayed out of the paceline. Then I made a big mistake… I thought I was doing okay and so thought I’d give the paceline a go… Oops. As I worked my way up to the front, my heart rate went right up again and I was struggling. One of the other riders glanced at me and suggested that I should just sit on the back of the bunch. I was inclined to agree…

So back I drifted and then we passed some other riders, who left a gap, and I couldn’t get over the gap with my previous effort and off the back I went. And that was that. For the next 45km I rode with one other rider, a C grade girl who had had the same trouble as I had. We worked well together and it helped… About 15km from the finish we caught Rob and 4 of us crossed the line all together at the end. It was still the fastest 60km I have ever ridden, by a long way!

My wife and daughters were there at the finish to cheer me over the line but I think they were a little surprised by how far back was!

Edit: Strava ride is now online:

I’ve got a new 10 minute power average: 355 watts…  I am not surprised.  At 22km, we had to walk across a wooden bridge (instant disqualification if you rode across…)  You can spot that in the speed graph pretty easily!

Mount Wellington Challenge

Today I raced the Mount Wellington Challenge time trial, billed as Australia’s toughest time trial.  It’s a 21km course from Longley to the summit of Mount Wellington, with a total climb of 1141m.  I had been wanting to do this time trial for some years, but always had something come up – a cold, travel, family commitments.  This year, I registered in early February to make sure I could not back out!  This meant I took it all far too seriously.  You can just keep reading to see that for yourself!
Mount Wellington from Ridgeway
My training for the ride was really just my usual commute – with just one hard ride, the Gordon Dam Doddle, thrown in for good measure a few weeks earlier.  In that ride, we climbed 3800m over 160km, in rain and drizzle.  It was a really tough ride, definitely the hardest ride I have ever done – and very, very good preparation.  In reality, I only joined the middle day of the ride – some of the really tough boys did a day on either side of it as well!
A couple of days before the ride I jumped onto my Strava account to look up my best times for various segments of the ride.  Strava makes it brilliantly simple, using a Garmin GPS, to identify climbs and compare performance over time and against peers.  I ended up with a table of my personal best times for each segment, which apart from the first little ‘warm up’, were all between 3.5km and 4km long.   I used this table to try to understand how hard to ride each section of the climb, on which sections I could make up the most time, and where I could maybe recover a little:
Segment
Distance
Gradient
Best Time
Best Speed
VAM
1.7 km
5.7%
5:43
17.4 km/h
1039
3.9 km
5.7%
10:01
22.1 km/h
1282
3.6 km
-1.8%
5:21
40.1 km/h
N/A
4.0 km
6.8%
14:37
16.6 km/h
1126
3.5 km
8.5%
16:36
12.5 km/h
1063
3.5 km
7.3%
14:50
14.3 km/h
1048
1:07:08
(Note that the actual course is a few hundred metres longer – the segments do not all start and end at precisely the same points.)  The first thing I picked was that I was not going to match my best time from Leslie Rd – Neika.  The VAM of 1282 for this segment was clearly way above average, and so trying to match that would probably cost me too much for the remainder of the ride.  Next I looked at how to best make up time, and realised that I would be better off saving some energy and recovering on the Neika – Fern Tree segment, which is slightly downhill, because cutting 6km/h off my speed would cost me less than a minute overall.  Conversely, working harder on some of the steep sections could well help me to improve my overall time:
Segment
Gradient
Best Time
Best Speed
Speed vs Time
Longley – Leslie Rd
5.7%
5:43
17.4 km/h
Leslie Rd – Neika
5.7%
10:01
22.1 km/h
Neika – Fern Tree
-1.8%
5:21
40.1 km/h
-6.4 km/h = +1:00
Fern Tree – Springs
6.8%
14:37
16.6 km/h
-1.0 km/h = +1:00
Springs – Chalet
8.5%
16:36
12.5 km/h
-0.7 km/h = +1:00
Chalet – Summit
7.3%
14:50
14.3 km/h
-0.9 km/h = +1:00
I did not expect to match the 1:07:08 overall sum of the individual segments – these personal bests were made over the year in various shorter rides, and to get a personal best on each segment in a single ride was definitely unrealistic.  After looking at the numbers, I realised that a 1:08:00 – 1:10:00 goal was probably a good target.  Of course I told all my mates that I was aiming for 1:15:00 – no need to set expectations from them as well!
To ride 1:08:00, I had the following targets in mind:
  • 18 min to Neika (achievable)
  • 6 min to Fern Tree (within reach)
  • 44 min to Summit (ambitious!)
Now the weather for the two weeks prior to the race was atrocious – it is rare to have snow on the mountain in February and March, but we had 3 days with snow on the mountain, and plenty of other days with miserable weather.  So I was watching the weather with some trepidation – Mount Wellington is a hard enough climb in good weather, let alone in freezing rain!  Fortunately for all, the day dawned as a beautiful, crystal clear, calm and sunny – a great Hobart March day.
I hopped on my bike at 7am and rode from our house in Ridgeway gently down to the start line in Longley.  I was keen not to wear myself out before the time trial.  While waiting for the event to start, I bumped into a good number of friends – Ant, Phil, Iain, Piers, Dave, Jonno, Les – and we all ragged about how sick we were and how we probably would get atrocious times, and how it would probably be blowing a gale on top, and the usual stuff that road cyclists use to try and manage their own expectations!  Surprisingly enough, the event started on time.  Riders were going off every 15 seconds, starting at 8:45, and I was number 161, which meant I had quite a wait.  About 20 minutes before my start, my mate Ant and I started warming up on the hills behind Longley, along with many other riders.  We rolled back into Longley just a few minutes before the start.
This was the first time I’ve had an assisted start – where a steward held my bike so I could start with my feet clipped into the pedals, and not waste “precious” seconds clipping in.  Fun!  Phil was starting a minute before me, and Ant 30 seconds ahead of him.  I knew I was not going to see Ant at all, but I had some vague hope of seeing Phil in the far distance at some point on the climb.  For some reason, there were 2 riders missing between me and the rider ahead of me, so I left 45 seconds after him.  I started off pretty gently – aiming to keep my heart rate below 165 for the whole first half of the ride (my maximum HR is about 185).  This I nearly managed, with my HR just nudging over 170 for the last kilometre of the initial climb.  Once I reached the gradual descent from Neika to Fern Tree, I eased back a bit, rehydrated, and sucked down another gel.  At Fern Tree, my wife and daughters waved and cheered me on, which was fantastic!
After Fern Tree the real climbing started.  A brutal ramp greets you at the start of Pillinger Drive, which is agonising after the previous gentle section – bumpy roads from tree roots eat up your momentum, and narrow road with a car coming past meant I had nowhere to manoeuvre.  Fortunately, this rough stuff does not last long: all too soon the climb sets off in earnest, sitting on 7% up to the Springs.  From Fern Tree to the Springs the road winds through the heavily forested lower sections of the mountain, beautiful, gum trees overhead, birds calling.  Not that I noticed!  A short respite in the gradient at the Springs ends nastily with the hardest part of the climb averaging 8.5% for 3.5km.  This section is the most feared section of the climb, across the face of the mountain, exposed, with a rough surface, and long straights punctuated by gentle corners that you negotiate only to be greeted by yet more long straight steep climbing.  Plenty of agony here in bottom gear, just trying to keep cadence up, trying to stay motivated to keep pushing the pedals as hard as you can.

On the mountain top
Once I reached the Chalet, at 1000m, things got a little easier.  A section of smoother road gave me the encouragement to go up a gear.  Soon after that, the roads curves onto the top of the mountain.  Should be flatter, right?  Unfortunately not.  It stays steep until the very end, not quite as steep as the previous section, but enough to be debilitating.  A gentle northwesterly wind proved to not be too big a hindrance, and just gave a little push for the last kilometre.  I finally crossed the line with a result of 1:10:43 – far exceeding my expectations! [edit: as many riders have noted, official timekeeping appears to have been out by 3 minutes so am using my Garmin time, not official time now]

At the summit on another, more typical day!
Looking at the individual segments, I rode the first section significantly faster than I ever had before, without pushing myself.  I was pleased that I managed to make a personal best also on the hardest part of the climb, without losing too much time on other sections.  My average heart rate for the whole time trial was 164.  Note that there is a few hundred metres missing in the segments which adds up to an additional 1:20 in the overall time, and that’s why the numbers don’t add up exactly – see the ride on Strava for full details.  Campbell Flakemore, the fastest rider of the day got a sub-53 [query: this may be out by 3 minutes as well] minute time.  I’m a long way short of that!
Segment
Distance
Gradient
Best Time
Yesterday
Heart Rate
Target
Achieved
Longley – Leslie Rd
1.7 km
5.7%
5:43
5:06
163
18:00
16:48
Leslie Rd – Neika
3.9 km
5.7%
10:01
11:24
167
Neika – Fern Tree
3.6 km
-1.8%
5:21
6:04
154
6:00
6:04
Fern Tree – Springs
4.0 km
6.8%
14:37
15:08
165
44:00
46:15
Springs – Chalet
3.5 km
8.5%
16:36
16:33
166
Chalet – Summit
3.5 km
7.3%
14:50
15:08
164
Segment total
1:07:08
1:09:23
1:10:43
After a rip-roaring descent back to Longley (in which I made another best time and topped the charts on Strava, yay!) I managed to pick up two spot prizes – a Genesys jersey, and a Malvern Star coffee mug set!  After the presentations, I carted my haul of goodies back up the hill, tagging along with a bunch of TIS riders, through Neika and thence back home.  77km, 1800m.  All in all, a great day!

Descending!

Paying for Parking at Hobart Airport, or Confusion For The Common Driver

Recently Hobart Airport ‘upgraded’ their parking payment system with some fancy new machines. This new, modern, streamlined payment system allows the driver to pay before returning to their vehicle, or pay with credit card at the exit… Well, sorta.

I’ve been wanting to get pictures of these machines for a while but I’ve always been at the airport at night or in the rain. Finally today I managed to get a picture of the fantastic machines!

Ok, so you’ve decided to pay before you return to your vehicle, here’s the payment machine.


Wow. Instructions all over it. Let’s see, wait a moment, there’s a list of steps there.


Okey dokey, maybe it’s not so bad after all, where’s number 1 on the machine…?


Hey, there’s a long queue of grumpy travellers behind you, hurry up. There’s number 2, there’s number 3 … and number 3 again … and again … and step 4’s over there, but where’s step 1?!?


Oh. I guess they decided to do that one differently. Now where’s step 2 again? Maybe it’d be easier to do this graphically:


Maybe not.

Eventually you will get there but don’t forget to press the receipt button before the ticket pops back out again!


So which button is it? I think it is the middle button but I couldn’t give you any guarantees!

Step 5 curiously shows you walking to one of the four exits but I’m sure you’ll figure out that you are actually supposed to drive out of the car park…

Next time

Next time, you will probably decide that that machine is ridiculous and instead you will pay with a credit card at the exit gate. Let’s see how you go.


Seems easy enough. Put the ticket in, now where does your credit card go?


Wrong.

It goes here.


I learned that one by painful experience. Swiping my credit card 3 times in the marked slot before I gave up and pressed the Assistance button…

Never believe the instructions.

Bike for Bibles Ride – Penguin to Cradle Mountain Loop (250km)

Will you sponsor me to join in the Tasmanian Bike for Bibles Cradle Mountain ride?

On November 20 and 21, I will be participating in my first Bike for Bibles ride here in Tasmania, Australia. We will be riding a total of about 250km from Penguin to Cradle Mountain return, with an overnight stay at Cradle Mountain.

Cradle Mountain, Tasmania

Cradle Mountain

Hellyer Gorge, Tasmania

Hellyer Gorge – I don’t think we’ll be riding down this track 🙂

This is the approximate route we’ll be taking.

And for you cyclists out there this is the elevation profile for the ride (via Bikely).

So why are we doing this ride?

Bike for Bibles rides are run all over the world; in Australia they are a vehicle for raising funding for literacy projects by the Bible Society. This particular project is raising funding for literacy projects in Mozambique and Kenya.

Island of Mozambique - Makuti Town

Makuti Town in Mozambique (from Wikimedia)

So will you sponsor my ride?

You can donate any amount: it is not linked to the distance travelled. How can you sponsor me? PayPal is the easiest method, with a handy payment button below.

If you don’t know me, do feel free to trust this plea as far as you can throw it 🙂 If you do know me, I hope that you can trust me to pass all the collected donations to the Bible Society!

Other alternatives of donating include contacting the Bible Society directly and sending a donation that way (just let them know what it’s for), or you can contact me directly at [email protected].

Finally, we are keen for more riders for this trip so if you are interested, please get in touch!

An examination of a handwritten document from the early 21st Century

This document was recently unearthed in southern Tasmania, Australia and has proven to be a curious example of early 21st century writing. It is believed that it is one of the last examples of writing on paper known before the great move to “computerised” text that characterisedthe era.

It is believed that the document is written in the lingua franca of the period, Modern English. We know from other historical records that the following alphabet is commonly recognised in the era and locality.

From this alphabet, we can determine that the document was originally photographed upside down! It is believed that the original photographer correctly identified the “address” area of the document (see below) but then wrongly assumed that this “address” should be at the top left of the document.

The darker ink sections at the top of the document do not appear to have been written by human hand and we believe them to be insignificant in the context of the document.

With a quick initial review we broke the document down into a two significant areas which we have called the “address” and the “letter”. The bottom right ruled section appears to be a form of address, perhaps denoting the author of the missive and the recipient.

It is curious to note a repetition of a particular word shape in this section. It is possible that this is a method of emphasising the sender or recipient.

It is perhaps useful to transcribe the letters and words we can recognise from the two sections of the document. The well known document “A Dictionary of the English Language” proved very useful here. The query symbol “?” has been used where the letters are unclear. [Editor’s Note: this transcription is written with “Unicode” and may require additional fonts on your system to be legible.]

MOU?D T YOU ?
IH TO BIRTHDAY
B ? ?
A ?????
M?
DORA

And in the “address” section:

HAMMAH
AUMTI B ANN B
HAMMAH IDE
MC??MA

We quickly spotted the ideographic symbol at the base of the document which we believe to be a “rabbit” – a form of animal life common in Tasmania in the early 21st century AD.

One of the sociological researchers on our team was excited to identify the “DORA” letter group (pronounced roughly as “Daw-ra”), which seems to have been a common meme at the time, perhaps the name of a famous entertainer.


Initially, we were puzzled by a shape that did not appear to be representative of any of the English alphabet, until we realised that the shape was merely an inverted L:

Note the difference between the inverted L and the R in DORA (we believe the R
to be a close match to the “lower case” version of the R):

This allowed us to identify a number of other words, so we thought:

MOULD T YOU L
IH TO BIRTHDAY
B ? ?
ALL ???
M?
DORA

Using the aforementioned Dictionary, we were then able to identify several words quickly, but this left us with a very puzzling message [Editor’s note: translations have been provided from the Dictionary by the team]:

MOULD The earth of the burying ground
T
YOU Second person personal pronoun
L IH
TO A function word used to indicate direction or action
BIRTHDAY The day of a person’s birth
B ? ?
ALL Encompassing everything
???
M?
DORA [The name of a famous entertainer?]

The use of the word associated with death together with the word associated with birth did lead us down the wrong track for some time. This lead to an entertaining but not particularly useful discussion as to possible interpretations of the document – perhaps it was a record of an important event, or an invitation to an event, or even a threat:

“On the day that the
entertainer Daw-ra is born,
you will be consigned to the burial ground” (!)

At this point we abandoned this path of inquiry, not realising how close we were to the true interpretation – and yet so far!

It was clear that we would have to work a bit harder to get to the true meaning of this document. A closer look at the M letters in the document showed a variation which we had not initially identified.

The first M had 3 humps and not 2. Perhaps it was not an M at all. Eventually we hit upon the idea of inverting the letter, in the same way as we had done with the L. Immediately it became clear that this was indeed an invitation – perhaps as mentioned to an event celebrating the birth
of DORA.

WOULD A question word implying desire
T
YOU Second person personal pronoun
L IH
TO A function word used to indicate direction or action
BIRTHDAY The day of a person’s birth
B ? ?
ALL Encompassing everything
???
M?
DORA [The name of a famous entertainer?]

The next letter to get an extended examination was the star shape next to the final M in the document:

By closely comparing each letter in the English alphabet to this star shape, we were able to bring the options down to K X and Y. In this context, it seemed that Y made the most sense and was the closest match, giving the well known Modern English word MY. This led us back to the start of the document where the L IH grouping (over two lines) was still puzzling us. Again, we compared the letters of the Modern English alphabet to the H letter, and realised that the K was possibly a closer match than the H. That gave us this message:

WOULD A question word implying desire
T
YOU Second person personal pronoun
LIK Perhaps an alternate spelling of LIKE, when linked with WOULD, indicating a pleasurable desire
TO A function word used to indicate direction or action
BIRTHDAY The day of a person’s birth
B ? ?
ALL Encompassing everything
???
MY First person personal pronoun
DORA [The name of a famous entertainer?]

Even from our admittedly limited understanding of English grammar, there was something that didn’t quite work in the ordering of the words. It seemed almost that there was a “word-picture” with some of the remaining letters – a letter apparently on its side, and some circular letters that seemed to be written and rewritten.


This symbol gave us trouble until it was suggested that perhaps it was a stylized C:

With a deep breath we proceeded to break apart the word picture:

This gave us something else to play with: B A LL E ? and COM. We postulated that perhaps the order of words in the document was less significant then than commonly thought by researchers today:

WOULD A question word implying desire
T
YOU Second person personal pronoun
LIK Perhaps an alternate spelling of LIKE, when linked with WOULD, indicating a pleasurable desire
TO A function word used to indicate direction or action
COM Perhaps COME, to move towards something
MY First person personal pronoun
BIRTHDAY The day of a person’s birth
B ALL E ? Perhaps BALLET, a ceremonial dance
DORA [The name of a famous entertainer?]

At this point we were sure we had the message (roughly translated):

Would you like to come [to] my
birthday Daw-ra
‘ballet’?

We were on a roll and started to tackle the address area. It was now clear that HAMMAH was a name. Again, applying the power of the Dictionary to the task, we came up with:

HAMMAH “Hama” (a name, rough pronounciation)
AUMTI
B
ANN “An” (a name, rough pronounciation)
B
HAMMAH
I DE
MC ? LMA

Again, stylistic variations came into play. It is clear from this document, that although other documents from the same era have suggested a regular and standardized alphabet, a wide latitude of variability in letter shapes and word spellings was in fact both permissible and common. We believe that this is an exciting area for further study. While our analysis could not identify any common words starting with “AUMTI”, a close match was made with “AUNTIE”, which both fit in the context and clarified the remainder of the address:

HANNAH “Hana” (a name, rough pronounciation)
AUNTIE
ANNE “An” (a name, rough pronounciation)
HANNAH
I DE
MC ? LMA

The names “HANNAH” and “ANNE” have been found in other literature from the period.

Thus, we believe this document to have been an invitation from Hana to her parent’s sibling An, asking her to come to her ceremonial dance celebration of the entertainer Daw-ra’s birthday.

The remainder of the address remains a puzzle. We have not been able to decipher it and we welcome any suggestions.

My new cycle commute

We just moved into our new house in a small, semi-rural suburb of Hobart called Ridgeway on the side of Mount Wellington. We are really enjoying the new house but I no longer have an office at home. So I now ride my bike to work 5 days a week.

Three days a week I ride ‘down’ the mountain to Kingston and back home up the mountain again in the evening. The other two days, I pop into Hobart instead. Why is ‘down’ in quotes? Because even the downhill route involves 250m (800ft) of climbing. There are many possible routes but I have selected three on the basis of traffic and road surface: the shortest route is a very rough, steep dirt road and I’m not keen on riding it on my road bike; I also try and avoid heavy traffic routes. The route I take depends on just how tired I am feeling:

  • Red – short but steep (over 15% in parts) – 21km (13 mi) – only when I’m feeling crazy!
  • Blue – intermediate and scenic – 23km (14.3 mi) – my favourite route
  • Green – longer but easier on the legs – 27km (16.5 mi) – save it for when I’m exhausted (Fridays?)


The total ascent is actually almost the same for all three routes – 200-250m (650-800ft) in the mornings and 500-600m (1650-2000ft) in the evenings.

In a normal week, I’ll be riding roughly 200km (120mi) and climbing 3500m (11,500ft) in my commute. Right now I feel the same as I did when I first started cycle commuting 2 days a week a couple of years ago — rather tired…

UPDATE STATISTICS and hints in SQL Server

I was working today on a slow query in SQL Server — it was a simple query on a well-indexed table, and I
could not initially see why it would be so slow. So I did some tests, and was surprised by the results.

I have reproduced the situation I was working through with a test table, with the following structure:

CREATE TABLE HintTest (
 HintTestID INT NOT NULL IDENTITY(1,1),
 Col1 INT NULL,
 Col2 INT NULL,
 Col3 INT NULL,
 CONSTRAINT PK_HintTest PRIMARY KEY (HintTestID)
);

CREATE NONCLUSTERED INDEX HintTest_Col1 ON HintTest (Col1)
-- column 2 was not indexed
CREATE NONCLUSTERED INDEX HintTest_Col3 ON HintTest (Col3)

Then I copied a set of records from the original data I was working with. The following code will
not reproduce this (and you’ll see the reason later) but does very roughly mimic the distribution of the data:

I used a quick and dirty test harness:

DECLARE @v INT, @StartTime DATETIME
SET @StartTime = GETDATE()
SET @v = 0

DECLARE @prmRecordFound BIT, @prmCol1 INT, @prmCol2 INT, @prmCol3 INT

SET @prmCol1 = 50
SET @prmCol2 = 3
SET @prmCol3 = 1750

WHILE @v < 10000
BEGIN
 SET @v = @v + 1

 -- Test Case Here
END

PRINT DATEDIFF(ms, @StartTime, GETDATE())

And here are the tests I wrote:

  -------------------------------------------
  -- TEST CASE 1: SELECT primary key and separate IF
  -------------------------------------------

  SET @prmRecordFound = 0

  DECLARE @HintTestID INT

  SELECT TOP 1 @HintTestID = HintTestID
  FROM
    HintTest
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3

  IF @HintTestID IS NOT NULL
    SET @prmRecordFound = 1

  -------------------------------------------
  -- TEST CASE 2: SELECT COUNT and separate IF
  -------------------------------------------

  SET @prmRecordFound = 0

  DECLARE @Count INT

  SELECT @Count = COUNT(*)
  FROM
    HintTest
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3

  IF @Count > 0
    SET @prmRecordFound = 1

  -------------------------------------------
  -- TEST CASE 3: SELECT COUNT nested in IF
  -------------------------------------------

  SET @prmRecordFound = 0

  IF (SELECT COUNT(*)
  FROM
    HintTest
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3) > 0
    SET @prmRecordFound = 1

  -------------------------------------------
  -- TEST CASE 4: SELECT COUNT with hint nest in IF
  -------------------------------------------

  SET @prmRecordFound = 0

  IF (SELECT COUNT(*)
  FROM
    HintTest WITH(INDEX(HintTest_Col1, HintTest_Col23))
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3) > 0
    SET @prmRecordFound = 1

  -------------------------------------------
  -- TEST CASE 5: EXISTS SELECT * in IF
  -------------------------------------------

  SET @prmRecordFound = 0

  DECLARE @Count INT

  IF EXISTS(SELECT *
  FROM
    HintTest
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3)
    SET @prmRecordFound = 1

  -------------------------------------------
  -- TEST CASE 6: EXISTS SELECT * with hint in IF
  -------------------------------------------

  SET @prmRecordFound = 0

  DECLARE @Count INT

  IF EXISTS(SELECT *
  FROM
    HintTest WITH(INDEX(HintTest_Col1, HintTest_Col23))
  WHERE
    Col1 = @prmCol1 AND
    Col2 = @prmCol2 AND
    Col3 = @prmCol3)
    SET @prmRecordFound = 1

The first run results reproduced the situation quite well, and returned the following surprising statistics (10,000 iterations):

1 (SELECT primary key and separate IF)  846 ms
2 (SELECT COUNT and separate IF)        203 ms
3 (SELECT COUNT nested in IF)           3523 ms
4 (SELECT COUNT with hint nested in IF) 226 ms
5 (EXISTS SELECT * in IF)               3460 ms
6 (EXISTS SELECT * with hint in IF)     263 ms

I was puzzled why there would be such a difference between cases 2 and 3, given that they were so similar,
so I looked at the execution plan for the query. It turns out that the query optimizer was selecting a very
non-optimal plan (a clustered index scan) when the SELECT statement was nested in an IF statement, whereas for
case 2 it was using two index seeks followed by a merge join. Adding hints overrode the query optimizer, and
the results can be seen in cases 4 and 6.

I ran the following statement to refresh the data the query optimizer uses:

UPDATE STATISTICS HintTest

UPDATE STATISTICS will update information about the distribution of keys for indexes on the table, which is then used by the query optimizer. Then I re-ran the test (100,000 iterations shown below). Dramatic difference. Now the hinted queries were among the slowest:

1 (SELECT primary key and separate IF)  2266 ms
2 (SELECT COUNT and separate IF)        2313 ms
3 (SELECT COUNT nested in IF)           2500 ms
4 (SELECT COUNT with hint nested in IF) 2546 ms
5 (EXISTS SELECT * in IF)               2656 ms
6 (EXISTS SELECT * with hint in IF)     2706 ms

For me, the big lesson learned out of this was this:

Always run UPDATE STATISTICS before trying to optimize a query!

The second thing I learned was that the fastest query is often unexpected. I would have expected the
EXISTS condition (case 5) to be optimal for a simple boolean result but instead the fastest query
was consistently case 1 – the SELECT primary key method.

The case of the hidden exception

I was building a new installer for an application today using WiX v3, and ran into a problem when trying to extract the COM SelfReg from a DLL using heat.

The error that heat returned was not very helpful.

heat.exe : warning HEAT5150 : Could not harvest data from a file that was expected to be a SelfReg DLL: PrinterBvr.dll. If this file does not support SelfReg you can ignore this warning. Otherwise, this error detail may be helpful to diagnose the failure: Exception has been thrown by the target of an invocation..

No tracing options were available in heat to figure out what actually caused the exception or even what the exception was! Time to pull out one of my favourite debugging tools: Procmon. Procmon traces activities on your system and records registry, file, network and similar activities to a log file.

So, I ran Procmon and added a filter to restrict the log to processes named “heat.exe”:

Then I started the trace and re-ran my command. It failed (as expected) with the same error. I came back to Procmon, stopped the trace, and scanned through looking for a failure point. Near the bottom of the trace, I found the issue. Part of the work heat does is to redirect registry actions to a special location in the registry so it can capture the work the DLL’s DllRegisterServer call does. Unfortunately, printerbvr.dll was depending on a specific key in HKLM already existing, which of course it did not in the redirected registry keys.

At this point I was able to understand the cause of the problem. But how could I work around this to get heat to do its work? I only needed to do this once to capture the registry settings. I could have used Procmon to watch all the activity and manually built the .wxs source file from that. But I was loath to do so much work.

Looking at the trace a bit closer I noted that the key that heat used as a temporary root for its registry redirection was based on its process ID. So I couldn’t create the keys beforehand because I wouldn’t know what the process ID was until after heat had already started — and the process only took a fraction of a second to run.

Windbg to the rescue! I fired up windbg, and started a new debug session with heat.exe and the appropriate command line parameters for heat. I didn’t want to create the registry key based on the PID before heat did that itself because I figured heat might complain if the registry key was already there. So I quickly peeked at the handy stack trace that Procmon had captured for the event in question:

From that I discerned that I could add a breakpoint when PrinterBvr.dll + 0x18BB (subtracting 6 bytes for the size of the ‘call’ opcode), and continued execution. I could probably have added the breakpoint at printerbvr.dll!DllRegisterServer, but this worked for me:

bu printerbvr.dll+0x18bb
g

At the right time, we hit the breakpoint and I then jumped into the registry (after quickly looking up the PID for this instance of heat.exe) and created my new registry key:

This time, everything completed happily, I had my .wxs output file, and I was able to get on with some real work (i.e. writing this blog post).

Phone call routing worthy of Sneakers

“I’ll bounce this call through nine relay stations around the world and off two satellites. It’ll be the hardest trace they’ve ever heard.”
— Whistler, Sneakers (1992)

I received a phone call today from a customer in the Middle East. They were working with a language in Africa and preferred to communicate in French, but never mind about that…

They made an international call to our US number (1), which routed via SkypeIn (2) to our Skype support account (3), then was forwarded through SkypeOut (4) to our listed Australian phone number (5), which forwarded through to our new office number (6), which, as no one was in the office today, forwarded through to the office mobile phone (7), and which, as I didn’t get to it in time, forwarded through to the mobile phone voicemail (8).

All that routing and still the poor customer ended up talking to a machine…