The Value of Testing

This is one of those posts where I wish I could show actual code snippets, but since it involves a 3rd party vendor for one of my clients and I don’t have permission, I can’t.

So, I’m forced unfortunately to talk about the issue in a roundabout way.

My client uses a 3rd party tool to track documents. I’ve mentioned this before. They’ve been growing fairly fast and running into performance issues. I suppose growing fast is a good thing, but having performance issues is not.

In any case, using Query Store, I was able to send the vendor a list of queries and stats about them for them to review and to ideally improve the queries that needed work.

Yesterday they got back to me. The email was essentially we took this first query (let’s call it Doubly-Joined) and rewrote it as this second query (let’s call it Singly-Joined). I looked at the two queries, which join 4 tables. They’re very similar to each other, but the first one did join in the main table a second time (hence why I’m calling it Doubly-Joined). It’s not clear why this was done. The second query basically removed the second join and in the select clause, changed the aliases to the second join to the first join. This does give them a slightly different query plan, but ultimately, they return the same number of rows.

The first query plan
The second query plan

As you can see, the 2nd query plan is definitely a bit simpler (ignore the one warning, it’s not something that appears to be fixable here).

So, a naive take would be “we removed an unnecessary join, so of course it should be faster!” But is it?

Sometimes intuition can be correct, sometimes not so much. In this case though, it’s easy to confirm by seeing exactly how many rows are being read in each query.

I wrapped each query in a

Set Statistics IO ON/OFF
Set Statistics TIME ON/OFF

block and ran it. Here are the results

The Doubly-Joined

Table 'Table1'. Scan count 0, logical reads 337264, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table2'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table3'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table4'. Scan count 1, logical reads 396, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The Singly-joined

Table 'Table1'. Scan count 0, logical reads 337260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table2'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table3'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Table4'. Scan count 1, logical reads 396, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I’ve highlighted the relevant change. The single-joined query consistently performed with 4 fewer logical reads. Now, if the original number had been 8 and had dropped in half to 4, I’d be happy. But the change from 337264 to 337260 leaves me a bit underwhelmed. Furthermore, under multiple runs, the second query did not consistently use less CPU time, sometimes it took faster to run. Further testing was consistent in the lack of apparent improvement.

Needless to say, I don’t think this query improvement will help much. I’ve reached out to the vendor to see if they can provide more details, but honestly, I’m not hoping for much.

Goals

A week ago an email alerting me to a new post from Steve Jones showed up in my in-box. I had to chuckle a bit because the topic was one, Goal Progress, that I had seriously thought about writing about for last week’s blog.  But I decided to write about teaching instead and put off a post on goals. There was a reason for that. I thought it would make a better post for this week.

There are some goals I’m fairly public about. I’ve started to write about my yearly goals and my results at the start and end of each year. But often I have more private goals. These are usually of a more personal nature. This post is about one such goal.

My Biking History

Ever since I got my first 10 speed, on I think my 10th birthday, I’ve loved to bike. Learning to read gave me intellectual freedom, but learning to bike gave me physical freedom. Growing up in a small town meant that to go any place interesting, biking was often the fastest and easiest way to do so. I could cover miles in fairly short order. Before I knew it, I was biking everywhere.

In high school, in the spring sports season I continued my relationship with the outdoors with the Outdoor Experience (OE) program. Part of that time was spent biking. I quickly upgraded my original 10 speed to one I bought from my original OE coach, Ben Kaghan. It was a beautiful Italian bike I continued to ride through high school and college until I was in a serious accident with it where I ended up going head over the handle bars and destroying the front forks. Twice in high school, as part of the OE program I managed to get in a Century ride, once my freshman year and once my senior year. They were nice bookmarks to my high school experience. Surprisingly to me, my senior year ride was a bit tougher, I think the route selection was a bit worse and we ended up with more hills.

For college graduation, I received a beautiful Trek 520 from my mom. I continued to ride, though not as much as I’d like. But several times, in September or October, I would ride to an annual event that my college outing club hosted known as “Fall Lake George”. This was an IOCA event where as many as a dozen colleges would canoe, sail, swim (yes a few have over the years) or powerboat to Turtle Island and camp for the weekend. I’d send my gear up in another vehicle and arrange for a car-ride back. Depending on factors this was generally about a 65-70 mile ride. The last time I did this was in 2015, the day before my father died. For various reasons, including changing college policies which have resulted in cancellations of this event, I haven’t done it since.

That said, most years I’ve routinely gotten in at least a few rides over 30 miles, with a few 50 mile rides snuck in there. But the elusive 100 miles had not been accomplished.

Due in part to COVID and wanting to get out more, and long stretches of good weather, I’ve found myself riding more this year than any year in memory. As of this morning my mileage for the year is over 1200 miles in the saddle. That was sort of a personal goal I had set for myself without publicizing it.

But there was one more goal. One I finally managed to realize this past weekend: the Century ride.  I had actually already accomplished 2 half-Century rides this year which I had felt good about, including one with some significant altitude gain. So I felt good about my chances. But I still wasn’t 100% sure.

Near Lake George is an amusement park, The Great Escape. My entire family has season passes and while I enjoy a day or two there, the rest of the family loves it there. My original goal was bike there, join them for a ride or two, and then bike home. And if I felt my legs weren’t up to it, toss the bike on the back of my wife’s car and ride home with them. Alas, due to Covid-19, The Great Escape has not been open at all this year.

But, I still sort of wanted a bail-out option. So I came up with a back-up plan. Right across from The Great Escape is a really incredible ice-cream place known as Martha’s Dandee Creme, which is a traditional stop for the family after a day at The Great Escape. So, about a month ago, I figured, if the weather worked, this past Labor Day weekend would be a good weekend to attempt my ride. And I’ve got to say the weather was nearly perfect for it. Not to warm nor too cold (though the first few miles from the house which was mostly downhill so I had lots of wind, but little muscle action, and still early in the morning were noticeably chilly.)

So at 7:45 AM I set out. The first 2-3 miles are basically coasting downhill from my house to the Hudson River and then the next 5 or so are avoiding potholes and city traffic as I make my way north to the first river crossing.

20200905_094609

32 miles in and going strong, but fuel and bio break.

I find that on longer rides, after about 2 hours, I need generally need some sort of refueling stop. Fortunately, in my area there’s lots of Stewart’s Shops. It’s generally easy to plan a break around them and I did.

20200905_095543

Milk (and a brownie) does the body good!

At this point I was more than half-way to my turn-around point, which was actually at about 55 miles, not 50. Once I had taken care used the facilities and refueled, I was back into the saddle for another stint. This was a shorter stint, but finally I would be gaining some altitude. Up until now the ride was basically along the Hudson, but in about 15 miles, I’d pass through Fort Edward and Hudson Falls where the river turns west and I would have to do some, albeit minor hill-climbing.  

My original goal had been to hit Martha’s by 12:30 PM. I had given myself lots of time because I had no idea how fast or slow I’d be. Well at this point I texted my wife to say that my new goal was now Noon.

20200905_113408-1.jpg

11:45 at Martha’s Dandee Creme!

I beat even that goal and that included a stop to make sure I hadn’t missed a turn.

One of the peculiarities I’ve found with endurance events like this is that my appetite essentially disappears. I knew I needed calories and as such ordered a soda, some chicken fingers and fries. The soda I had no problem drinking. Of the 5 chicken fingers, I managed to down 2 and of the fries, not even 1/2 of them. I saved the rest for my family.

Once they showed up, we ordered ice cream. I mean, what’s the point of biking to an ice cream place if you don’t get ice cream?

A very surreal experience, The Great Escape on Labor Day weekend, empty, and soundless

Finally it was time to get back in the saddle and head southbound. I felt strong about it, but wasn’t sure about the wind and all. And the wind at spots was definitely a factor and it definitely slowed me down.

But….

The big 100!

I made it. There was just one problem. As I had mentioned, my turnaround point was about 55 miles from my house. This meant I was still over 10 miles from home. I made a quick pit stop just south of here (again at a Stewart’s) and then raced home. Honestly, the about 2 miles near the house was the worst, not because of the distance per se, but because I finally had to regain all the altitude I had lost from my house to the Hudson 9 hours previous.

But I pulled into my driveway right before 5:00 PM.

Finally home, 111.6 miles later

I had, for the first time in 35 years, finally completed another Century ride. Actually a bit more than a Century ride. Goal accomplished.

Final stats:

  • 9:44 Door to door including stops
  • 111.6 miles covered
  • Crossing the Hudson, 4 times
  • Altitude changes: 515->13 feet and then slowly back up to 476 feet. And then all that in reverse
  • 1 Stewart’s Chocolate Milk consumed
  • 1 Stewart’s large brownie devoured
  • 2 chicken fingers digested
  • Some french fries eaten
  • 1 small (which is actually quite large at Martha’s) salted-caramel soft-serve cream in a cone ingested
  • 7:16:12 actual riding time
  • 29.20 top speed on some random hill.
  • 15.3 mph average speed overall (down from 16.3 mph turn around and 15.8 at 100 mile mark, the hills at home and city traffic killed me)

Conclusion

So, the first question that comes to mind, “would I have written this blog post had I failed to achieve my private goal or always kept the failure private?” – Good question. I think it depends on the reason for the failure.

“How did you feel the next day?” – Honestly? Pretty good. Other than my knees, I find if I’m in shape, long rides like this don’t really leave me overly sore the next day. And taking doses of ibuprofen definitely helps with the knees and more.

And of course, “Would you do it again?” – Well I probably won’t wait another 35 years. We’ll see what happens next year or the year after that.

“What other private goals do you have?” – That would be telling! 🙂

Seriously, it was a great time for me, I’m SO glad I did it and am feeling great. Not to bad for someone my age in a year of Covid.

Learning and Teaching

This past weekend was the first of 3 weekends I’ll be spending in teaching a cave rescue class. As I’ve written before, I usually spend at least 1 week a year teaching students how to help rescue folks out of caves. I don’t get paid money, and in fact have to pay for my own travel and sometimes other expenses. But, I love it. Unfortunately, the large event we had planned for NY this year had to be postponed due to Covid-19.

A Little Background

Fortunately, New York is one state where folks have been very good about social distancing and wearing masks, so that gave me the opportunity to try something new: teaching what we call a “Modular Level 1” class. Instead of taking an entire week off to teach, we spread the teaching out over three weekends and several nights. This can often better accommodate peoples schedules. After a lot of planning and discussions I finally decided to go ahead and see if I could host a class. Through a series of fortunate events4, by the time I was ready to close registration, I actually had more than enough students. What makes this class different from other classes I’ve taught is that more than 1/2 the students have never been in a cave. However, most of those are in medical school and a goal of mine has been to get more highly trained medical folks into cave rescue. So, we greenlighted the class.

Teaching

The first day of class is really mostly about “check-ins”. Each student must demonstrate a certain set of skills. When I teach the Level 2 class, this generally goes quickly because the students have already gone through Level 1 and the students tend to be more serious in general about their caving skills. But for Level 1, we get a broader range of students with a broader range of skills. And in this case, some folks who were just entering the community of being knot tying and SRT (Single Rope Technique).

There’s a mantra, I first heard among the medical education community, but is hardly unique to them, “See one, do one, teach one.” There’s a logic to this. Obviously you have to see or learn a skill first. Then obviously you need to be able to do it. However, the purpose and goal of that last one eludes some people.

Without getting too technical, let me give an example: in SRT, cavers and rescuers need the ability to climb the rope and, while attached to the rope, successfully change-over to be able to descend the rope. I’ve literally done this 100s of times in my life. I obviously have the first two parts of that mantra down I’ve seen it, and and done it. But teaching it is a whole other ball game. Being able to DO something, doesn’t mean you can successfully teach it. We do many things based strictly on experience and muscle memory. If you think about walking, you may realize you do it naturally without any real thought. But imagine trying to teach someone how to do it. You probably can’t, unless you’re a trained physical therapist.

Much is the same with the aforementioned change-over. Just because I could do it, didn’t mean I could successfully teach it. However, over the years, as I’ve taught it more and more I’ve come to recognize certain mistakes and certain areas I need to focus on. I’ve gotten better at teaching it. So by teaching more, I’m learning to become a better teacher. By being able to teach it, I also understand it and know it better. The “teach one” part of the mantra is important because it means you can give forward the skills you’ve learned, but also means you have a better understanding of them in the first place. You can’t effectively teach what you don’t understand.

In addition to learning how to teach better, I’ve also realized that some approaches work better than others for people. There’s a common knot we tie in the rope community called an “alpine butterfly”. There are at least four ways I’m aware of to teach it. One method involves looping the rope over your hand 3 times in a certain pattern and then pulling on the right loop in the right way through the others, the knot “magically” appears.  I’ll admit I’ve never been able to master this and as a result, obviously don’t teach this way. The method I use is a bit more off-color in its description. Writing it down it comes down to:

  1. Take a bight of the rope
  2. Put two twists in it
  3. Take the loop, aka head, pass it between the legs of the rope
  4. Shove the head through the asshole formed between the two twists
  5. Pull tight and dress

At the end of that, you have a beautiful alpine butterfly. On Saturday night I was helping a student perfect her butterfly. She was having trouble with the 3 loops over the hand method. I showed her the asshole method. She almost instantly got it. Now, that’s NOT to say the asshole way is the better way, it’s simply the way that worked better for her.

Learning

Besides learning how to teach better, I actually learn a lot from my students. For example, one of the students who does have extensive alpine rescue experience was asking about our use of what are known as Prusik loops to tie Prusik Knots. In her training and experience she uses something similar called a VT Prusik. I had seen these before in previous training, but had not had a chance to see them in action or play with them. She did a quick demonstration and then on Monday sent me a link with more information. Needless to say, by the end I was ordering a pair so I could start to play with them myself. I can already see where I might use them in certain cases.

Another example of learning is that I’m starting to adopt a different way of tying what’s known as a Münter hitch. I’ve been tying these successfully for decades, but started noticing another method that’s fairly common and in my mind, if not more intuitive, it is at least a bit more of a visual mnemonic. I think it’ll reduce my chances of tying one poorly so I’ve started using it more and more. And this is because I saw how quickly students would pick it up.

Gelling

By Saturday night most of the students had passed their check-offs, but not in what I’d call a solid fashion. They were still at the stage where they were simply reproducing what they saw. This is common in the early stages of learning. As a result, I decided to adjust the Sunday morning schedule and spend a bit more time on simply practicing and honing their skills. What we really want at some point is for the skills to “gel” (i.e. go from a liquid state where their ability is in flux to a state where there abilities are more solid). What can be interesting about this is for some folks, this can be a fairly quick process and in fact I noticed by lunchtime for a number of students, their abilities had gone from simple rote reproduction to an actual more gelled state. After lunch we put in some more time and with some of the students I’d simply walk up, call out a knot for them to tie, walk away, give them a minute or so and come back to see what they had done. In most cases, they were successful. The night before that would not have worked. They’re still a long way to go from being as good as I or they might like, but they were no ready to go out in the field and safely put a patient over the edge.

Level 1 students pull a patient up over a cliff

Safely getting a patient over the edge

Concluding

So we have two more weekends to go before they can call themselves trained as Level 1 students and hopefully they’ll keep learning and improving beyond that. For me, as long and tiring as the weekend was (I think I got about 5-6 hours of sleep each night, at most) it was rewarding because I got to see students learn skills we taught AND because I got to learn stuff too. It was a great weekend and I look forward to the next two.20200829_134511