Category Archives: Development

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 ,
        ActivityCodeNumber
FROM    OrderedAccounts
WHERE   ( @Limit IS NULL
          OR RowNumber <= @Limit
        )
OPTION  ( OPTIMIZE FOR UNKNOWN )

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:
freezetableexample

Step 1: The HTML

<table id="mytable">
  <colgroup>
    <col class="column-make" />
    <col class="column-display" />
    <col class="column-country" />       
  </colgroup>
  <thead>
    <tr>
      <th>make_id</th>
      <th>make_display</th>
      <th>make_country</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>abarth</td>
      <td>Abarth</td>
      <td>Italy</td>
    </tr>
  </tbody>
</table>

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 () {
    $('#mytable').freezeTable({
        'autoHeight': false,
        'height': 160
    });
});

Demo
AutoHeight Demo
Fixed height Demo
Interactive Demo on JSFIDDLE

Download
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';
        $(element).text(valueUnwrapped.toString(pattern));
    }
}

Here is the binding in page:

Thursday, April 05, 2012
0.00

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)
{
    try
    {
        AutoDataBind = false;
        OnCancel();
    }
    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') {
        jQuery("#searchButton").click();
        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;
ddlStatus.DataBind();

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

<asp:DropDownList ID="ddlStatus" runat="server"
    DataSource='<%#Model.StatusList%>'
    DataTextField="Description"
    DataValueField="Id"
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.

Offsetting the time zone in MS SQL 2005

I needed to offset the current time in MS SQL 2005 for a quick fix. I wanted to make sure the solution would not have to be maintained when daylight savings time changed. Taking the difference between the GETUTCDATE() and GETDATE() functions will generate the server’s current time zone offset from UTC time.

DECLARE @timezoneOffset INT
SELECT @timezoneOffset = (DATEDIFF(hh,GETDATE(),GETUTCDATE())) * -1;
SELECT DATEADD(hh, @timezoneOffset, GETUTCDATE()), GETDATE()