Sergey Maskalik

Sergey Maskalik's blog

Focusing on improving tiny bit every day

Coming back to ADO.NET, I sometimes forget of different ways of accessing values from the SqlDataReader, cost of a type unboxing and which method is to use when. For example, here are different ways we can access values:

  • using a typed accessor SqlDataReader.GetInt32(int columnOrdinal)
  • geting a value directly with named column like this SqlDataReader[“columnName”] or with column ordinal SqlDataReader[0] and then casting it
  • call SqlDataReader.GetOrdinal(“columnName”) to get column position and then combine the result and call the typed accessor method like SqlDataReader.GetInt32(SqlDataReader.GetOrdinal(“columnName”)).

For performance benefits using Typed Accessors like reader.GetInt32 or reader.GetDateTime is always faster since there is no type conversion or unboxing occurs. But these typed accessors only accept column ordinal or column position and most of the times you don’t want to hard code your column position since that could change at any time and break your application.

I wrote a series of tests that test each scenario in the context of:

  1. Getting one record 100000 times, that emulates retrieving of one row.
  2. Getting 20k records 1000 times, or simulating retrieving large amount of rows

The results are as follows:

Getting one row over 100k times:

one row

There is really no difference or any significant performance advantage when accessing one row. When you run the test multiple times numbers change slightly and there is no clear winner. So we can say if you are only getting one record you can use any one of those accessors and cast as you please.

Getting 20k records over 1000 times

data reader multiple rows

Typed accessor proves to be the fastest. Right behind is casting by using column orderinal accessor. Casting with column name is probably 30% slower than casting typed casting. And finally if we call reader.GetOrdinal once and cache the column position we get as fast performance as typed accessor alone.

Summary

If your query returns only one row or small amount of rows it doesn’t matter which method you use. It would only make sense if you have large number of rows and using reader.GetOrdinal to cache column number and then using typed accessors would yield the best performance.

If you want to see for yourself you can download the source code and run it against the AdventureWorks database.

I’ve been getting a ridiculous amount of spam on my blog and even though FunnelWeb checks with akismet, a lot of spam messages are still getting through. Most spam comments are placed by bots that look for input fields and then submit garbage in hopes that it somehow becomes a link. We can make it a little harder for it by including a simple question that humans can easily answer, which should reduce amount of spam.

It was very easy to do in FunnelWeb since it embraces ASP.NET MVC. All I had to do is to create an extra property in the PageModel model and annotate it with regular expression validation that looks for the key answer to the question I’ve provided. (?i) in the regular expression annotation means case insensitive comparison.

   [Required]
   [StringLength(50)]
   [DisplayName("Spam check")]
   [HintSize((HintSize.Medium))]
   [Description("What colour is grass?")]
   [RegularExpression("(?i)^green$", ErrorMessage = "Please confirm that you are human and enter a correct answer. What colour is grass?")]
   public string SpamCheck { get; set; }

We don’t add any extra code in our controllers since it already checks if model is valid and if not it would return back with validation error messages. One more thing we need to do is to modify our view to display the spam check answer. We customize our views in the FunnelWeb by coping them into our themes folder and then making changes there keeping originals intact. So I’ve copied _EditComments.cshtml from Views/Shared folder and placed under my Theme folder in the Views/Shared folder and added the following to display the spam check.

<div class="editor-label">
  @Html.LabelFor(m => m.SpamCheck)
</div>
<div class="editor-field">
  @Html.EditorFor(m => m.SpamCheck, Html.AttributesFor(m => m.SpamCheck))
  @Html.ValidationMessageFor(m => m.SpamCheck) @Html.HintFor(m => m.SpamCheck)
</div>

And that’s it, with minimal modification to our blogging platform we eliminated all those annoying spam bots posting garbage.

Update 11-26-12

Since putting up that spam filter I haven’t gotten a single spam comment in last 24 hours. Nice, no more need to administer your comments daily, one less thing to worry about!

iTunes is my least favorite software when it comes to managing music library. However sometimes you have no choice and have to deal with it. Couple times in the past I had to completely recreate my iTunes library from scratch since I could not move it to the newly installed system. Recently when I’ve build a new PC I was a bit smarter and did the research and mapped my library to the exact same folder and everything worked and everything transferred perfect.

This time however I’ve decided to move out my music library from home server to the media server and was stuck since all my music library tracks were mapped to the UNC \hpserver and new location of my music would be something like M:\Music. Unfortunately you can’t do that with iTunes and you are stuck with going one by one and relocation your track.

Luckily few days ago I’ve stumbled across Scott Hanselman’s post on how to remove dead tracks from iTunes. After looking at the code it was pretty easy to add functionality to relocate tracks automatically and I’ve modified the sample project he provided to do just that. Thanks to him for bringing iTunes api to light!

Itunes relocate tracks

Now I still have my library with play counts and all the data just like it was before but under a new location!

Here is the link to the modified source code if you ever want to move your itunes library to a new location.