Author Archives: Aaron Jackson

Is your stored procedure slower than an plain query?

On occasion when a query is wrapped in a store procedure the execution time is horribly slow.  This is due to a common issue known as “parameter sniffing” in SQL Server.  A couple of solutions are commonly recommended;

  1. Create local variables inside the stored procedure and assign the store procedure variables to the local ones.
  2. Add WITH RECOMPILE to the stored procedure right before AS.

However, with one of my stored procedures neither of these options fixed the performance problem.  I discovered another method using the query hint OPTIMIZE FOR UNKNOWN.   This hint causes the query optimizer to use the statistical data instead of the procedures initial values when then query is compiled.

Add the OPTIMIZE FOR UNKNOWN query hint using the OPTION keyword after the WHERE clause.

SELECT  FundNumber ,
        DepartmentNumber ,
FROM    OrderedAccounts
WHERE   ( @Limit IS NULL
          OR RowNumber <= @Limit

Use jQuery FreezeTable to build a scrollable table

Here we’ll use jQuery FreezeTable plugin to freeze the header and add a vertical scroll bar to a lengthly table.

The end result will look like this:

Step 1: The HTML

<table id="mytable">
    <col class="column-make" />
    <col class="column-display" />
    <col class="column-country" />       

The table markup needs to adhere to the HTML 4.01 Specification for Tables. The row groups THEAD and TBODY are used to determine which sections of the table should be frozen. jquery.freezetable clones the table and removes the TBODY from the cloned copy to ‘freeze’ the header. It then removes the THEAD from the original table to make the scrollable portion of the table. jquery.freezetable also depends on COLGROUP to maintain consistent column styling between the orginal and cloned header table.

Step 2: The JavaScript

$(document).ready(function () {
        'autoHeight': false,
        'height': 160

AutoHeight Demo
Fixed height Demo
Interactive Demo on JSFIDDLE

Download FreezeTable from GitHub

Formatting Dates with Knockout.js

I am working on a couple of Silverlight projects using Microsoft Prism and found the MVVM pattern very powerful. The team at work is developing a single page HTML/Javascript application that is using Knockout as the MVVM framework. I wanted to bind a date using a custom format ‘dddd, MMMM dd, yyyy’. Since JavaScript doesn’t have great support for formatting dates I pulled in Date.js.

I am familiar with using IValueConverter in Silverlight to convert values during binding. I was really happy to find a the custom binding feature in Knockout called ‘bindingHandlers‘. I was able to write a quick bindingHandler to format the date using date.js.

Checkout the Demo

Here is the custom binding for formatting dates using date.js:

ko.bindingHandlers.dateString = {
    update: function(element, valueAccessor, allBindingsAccessor, viewModel) {
        var value = valueAccessor(),
            allBindings = allBindingsAccessor();
        var valueUnwrapped = ko.utils.unwrapObservable(value);
        var pattern = allBindings.datePattern || 'MM/dd/yyyy';

Here is the binding in page:

Thursday, April 05, 2012

JavaScript: Using setInterval Within an Object

I had a need to use window.setInterval(code, delay) to a call a method on a JavaScript object every few seconds to rotate some content. However, I found that once setInterval called the code in the callback ‘this’ referenced ‘window’. However, I needed ‘this’ to reference the object that was called by setInterval.

ContentTabs = {
  init: function () {
    setInterval(this.rotate, 5000);
  rotate: function (){
    // this === window

After some research I found I was having a closure issue. The solution was to use a ‘self’ variable whose scope is within the closure of the object and wrap the callback code, so it referenced the ‘self’ variable.

ContentTabs = {
  init: function () {
    var self = this;
    setInterval(function() {
    }, 5000);
  rotate: function (){
    // this === ContentTabs

DotNetNuke MVP Module Development: Tip #2 Disable AutoDataBind

In DotNetNuke MVP Module Development: Tip #1 The AutoDataBind Property I suggested to use AutoDatBind to automatically bind up the user control.  However, there are times when no binding should occur.  For example, when a Cancel button is clicked the view should not be bound.  AutoDataBind should be disabled when no model is need.

protected void cmdCancel_Click(object sender, EventArgs e)
        AutoDataBind = false;
    catch (Exception exc)
        Exceptions.ProcessModuleLoadException(this, exc);

Quick Tip: Use JQuery to submit a TextBox.

imageSubmitting a search box when <Enter> is pressed is a nice usability feature to provide.  Unfortunately, ASP.NET Web Forms and its requirement for a single <form> tag breaks this expected behavior when there is more than one submit button on the page.   This can be easily fixed with a JavaScript and JQuery.   Listening to the keydown event on the TextBox the Go submit button can be triggered when <Enter> is pressed.

jQuery("#searchTextBox").keydown(function (event) {
    if (event.keyCode && event.keyCode == '13') {
        return false;
    } else {
        return true;

DotNetNuke MVP Module Development: Tip #1 The AutoDataBind Property

The DotNetNuke.Web.Mvp.ModuleViewBase sets a property called AutoDataBind = true. As a result the DataBind() method for the page is always called in the Page_PreRenderComplete event.

If you use the following familiar snippet of code you find that your controls will be data bound twice. Once when your code calls DataBind() and a second time when it is called during the Page_PreRenderComplete event.

ddlStatus.DataSource = Model.StatusList;

Here is an example of how to properly bind a DropDownList for an MVP based module.

<asp:DropDownList ID="ddlStatus" runat="server"
SelectedValue='<%#Model.Post.PostStatus %>'> </asp:DropDownList>

It is important to point out again that you should not call ddlStatus.DataBind() from the code behind. Calling this is a waste because the AutoDataBind property is going to force everything to rebind again during Page_PreRenderComplete.

How BOI2REX Became BOI2CotM


What is BOI2CotM?  It is Boise to Craters of the Moon and that is as far as I got on my cycling trip across Idaho.  At 8 PM on day two I made a reluctant phone call to Tory. 

“Hey, babe.”, I announced. 

“Hi'”, she replied.  I knew she knew why I was calling.

“That’s it.  It’s too dark to continue today,” I declared. 

Up to that point in my ride I had stopped at every historical marker on the side of the road.  It was something I never do while driving.  The historical marker I was now sitting under has no name for me.  I just realized I never bothered to read it. 

I ended the phone call and unloaded my gear.  The first thing to come off was the CamelBak I had been lugging across the dessert.  I started with 2 – 2L water bladders and the added 8.4 lbs. of weight had been contributing to a lower back ache.  I then pulled off my helmet and my HeadSweat (a bandana designed to fit under a helmet).  My HeadSweat was stiff from all the sweat that had evaporated.  I stopped my heart rate monitor at 9 hours 13 minutes.  It was reporting I had consumed 6,570 calories.   Finally, loosened my cycling cleats and I sat down under the sign with no name to wait.

Sitting under the sign I watched the final glow of dusk disappear behind one of the hills I had just rode over.  Oddly, I am often asked what I think about while riding these long distances and that question was the first thing that came to my mind.  On the bike the majority of my self talk is small talk.

“Have I finished my water for this hour?”

“I need to eat before I am hungry.”

“Make it to the top of this next pass and you’ll get a break.”

“My butt kills!  Stand up you fool.”

“Aaron, you have not seen another cyclist all day.  Are you crazy!”

“Come back here you jerk and let me beat you with my frame pump! You and I are the only people on this road! Use the passing lane next time you pass a cyclist.”

“Here comes some more road kill (on a bike you can smell road kill well before you see it).”

Looking back, “I cannot believe I just road my bike up that!”


“I haven’t peed in a long time.  That is not good.”

“Hey, I am ridding as fast as that hawk!”

“Just another 10 miles and you can take a break.”

“Die, grasshopper, die!” Going through the dessert the grasshoppers would jump on you and give you a pretty good scare.

One would think I would contemplate the big questions in life, but I don’t.  Maybe it’s because I am already satisfied with the answers or maybe cycling  is it’s own distraction.   Either way cycling leaves me feeling satisfied and my mind clear afterward.

I looked back the hill had now fully hide the sun and had was feeling mixture of satisfaction, sadness, and exhaustion.  I was satisfied with what I had accomplished in my last two days. 

On the first day I rode to the edge of the Snake River basin and had my first view of the adventure I was about to embark on.

Most of the Snake River basin consists of wide, arid plains and rolling hills, bordered by high mountains. (

What Wikipedia doesn’t tell you, is that there was going be a 15 – 20 mph wind blowing out of the southeast that day.  I took on the wind like a long climb and settled into a rhythm.  The Snake River basin had some beautiful views, new pavement, and only a couple places to stop for water.  I arrived in Mountain Home 1.5 hours later than I expected and a slight headache from dehydration.  I knew I was already in trouble for the next day, but thought if I hydrated that night I would be okay.

I meet Tory and the boys at Mountain Home RV park.  Being with my family after a long hard day was amazing.   I showered off the grime of the day, setup the tent trailer, and we went out to eat.  We planned on cooking tin foil dinners in the fire that night, however, we discovered that “RV parks are NOT campgrounds.”    Once we got back from dinner and we played one of our families favorite board games, Small World, and I drank water until my headache finally drifted away.

We slept in until 8 AM that next morning and opened the trailer door to another windy day.  I looked across the horizon and in the distance saw a large American flag waving vertically over Mountain Home’s Walmart.  My heart sank.  This was already going to be a long day and the wind was going to make it event longer.  I knew this was a possibility and my legs were feeling pretty good, so I prepared for the day.  I started by drinking a packet of VAAM, a supplement recommended by a nutrition specialist at George’s that was made from hornet extract.    It’s purpose was to help the body clear lactic acid more efficiently.   Placebo effect or not I felt much better starting the second day’s ride.  Knowing that the first place to stop for water was 60 miles away in Fairfield I loaded up 2 water bladders in the CamelBak, two 24 oz. water bottles, and my food. 

Mountain Home’s name is appropriate, because the climbing started right away.  It’s a much better name than ‘Rattlesnake Station’, which I learned from a historical marker was the name of the town prior to the railroad coming through.  Near the top of the first mountain pass I overtook the one and only person on my entire ride (everyone else were in cars passing me). 

“How are things going?” I asked.

“I have had better days.” said the grayed haired man in cowboys boots.

“That must be your trailer back there.” I stated.

“Yea, two blown tires.  I am hiking up the hill to get cell service.” he replied.

“Can I help?”, I questioned.

“Not unless you have a spare trailer tire in that pack,” he said with a smile.

“Fair enough.  Good luck!” I wished as I road on.

I crested the hill and had a short decent into Camas Prairie – a plateau valley at 5,000 feet.  This was by far the best part of day 2’s ride.  Exhausted from climbing the flat terrain of the prairie was a welcome break.  The prairie is surrounding on all side by mountains, which makes for some beautiful scenery.  It is also where Fairfield is located, the half way mark, and the first place to get water.    I was feeling really good at this point and took the opportunity to have an ice cream sandwich, muffin, and banana.  I refilled all of my water using the tap on the outside of the Camas Creek Country Store and was back on the road.

After Fairfield the prairie slowly changed to Idaho dessert and a really long, straight road.  I was still feeling pretty good and was confident I would be able to finish the day.  I rode across the rest of the valley and over the pass at the other end.  I took a short break at the top of the pass to look back across the valley.  I could hardly make out the mountains at the opposite end and felt impressed with what I had accomplished.

I then descended a short distance down through Picabo and then took another break in Carey.  I made what I though would be my final phone call to Tory to let her know I was feeling strong and would see her at Craters of the Moon at around 7:30 PM.   With only 25 miles remaining I just filled my two water bottles and got back on the road. 

Leaving Carey the road started to gradually climb up to Craters of the Moon.  I expected some climbing at the end, but didn’t realize how much was actually left.   It’s one thing to see the elevation gain on the map and quite another to actually travel over that same terrain. My average across the Camas Prairie had been between 18 – 20 mph with a 145 heart rate, but as the climb became steeper my speed began to drop.  I was now going 13 mph with a 185 heart rate.  I was working hard because I knew I was losing the sun.

I finally realized that I couldn’t maintain the present pace for much longer and would need to slow down some more.  Normally, I would just settle in at a comfortable, slow pace, but I was in the middle of the Idaho dessert on small, windy two lane highway with cars going 70 mph, almost no shoulder to ride on, and almost no daylight left.   The ETA (estimated time of arrival) on my GPS reported 9:14 PM. 

I found my sign and made the phone call. 

I guess I’ll need to ride by that historical sign some day, so I know what it says.

BOI2REX – Routes Have Been Finalized

Day 1.  Nampa to Mountain Home.

I’ll start in Nampa and head south through Melba and end up at the Mountain Home RV Park in Mountain Home.  I’ll end the day with 2,605 feet of elevation gain and 88 miles on the bike.  There aren’t many places to get water, so I am going to bring a CamelBak in addition to my water bottles.

View Interactive Map on

Day 2. Mountain Home to Craters of the Moon. (a.k.a.  The Test)

Starting in Mountain Home I’ll climb toward the heavens for 4,787 feet and by the time the day is done I’ll have 126 miles under my belt.  There is a big flat section in the middle, so that should help.  The first part of this day doesn’t have a lot of places to get water, so it will be another day with the CamelBak and I will have Tory drop a gallon of water part way.

View Interactive Map on

Day 3. Craters of the Moon to Rexburg

Hopefully my legs are still working and I can enjoy a 107 mile day with 1,800 feet of elevation loss.  I’ll go through Arco, Mud Lake, and the sand dunes.

View Interactive Map on