Rebuilding SQL Server Indexes

I've always used cursors to rebuild indexes in SQL Server, like so:

USE YourDatabase

DECLARE @tableName varchar(255)
DECLARE cur CURSOR FOR

SELECT table_schema + '.' + table_name 
FROM information_schema.tables
WHERE table_type = 'base table'

OPEN cur

FETCH NEXT FROM cur INTO @tableName
WHILE @@FETCH_STATUS = 0
BEGIN
	DBCC DBREINDEX(@tableName,' ',90)
	FETCH NEXT FROM cur INTO @tableName
END

CLOSE cur
DEALLOCATE cur

However, I discovered a much easier way to do it today:

sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

This is an undocumented stored procedure that I didn't even know existed until this morning. The cursor route is still useful for when you want to exclude tables, but dang....I wish I'd known of this earlier.

Playing with the TPL

I did a quick investigation on the Task Parallel Library (TPL) this morning (part of the System.Threading library which comes included in the .NET 4.0 framework or is available as a download from 3.5), and I’m really amazed at how easy it was to implement, and the apparent benefits of using it.  Basically, this library allows you to benefit from the multiple cores found in modern computers, without the pain of actually coding for multiple cores yourself. 

I wrote a very simple C# application that fired off 1,000 web requests to a web application on my local machine and read the response stream.  This was done in three ways:

1. sequentially making the web request in a for loop
2. using a thread pool to make the web requests
3. using Parallel.Invoke to make the web requests with the TPL library

Well, option 1 was obviously very simple to implement, but it was SLOW.  1,000 requests took in the region of 20 seconds to run.

Option 2 was the most difficult to implement, but obviously far more efficient.  I always find coding for multiple threads a little tricky, as I don’t do it often and I never remember offhand where to put the wait handlers.  Anyway, it resulted in 1,000 requests being done in approximately 11 seconds.

Option 3 was, to my pleasant surprise, a piece of cake to implement.  Create an Action list, and call Parallel.Invoke – that was it.  Incredibly, this took approximately 6.5 seconds to run on my 4-core machine.  I’ve pasted the code below, and hopefully my implementation is correct, but it’s so easy to use I don’t see why you wouldn’t use it when you have a large number of tasks to do at one time.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Diagnostics;
using System.Net;
using System.IO;
using System.Threading;

namespace TplSample
{
    class Program
    {
        static string url = "http://dev.mysite.co.za/";
        static int testCount = 1000;

        static void Main(string[] args)
        {


            // fire off one request so we aren't skewed by a sleeping application pool
            SendWebRequest();

            // fires off requests in sequential fashion
            SingleRequests();
            // fires off requests using a thread pool
            MultiThreaded();
            // fires off requests using parallel processing
            ParallelRequests();

            Console.WriteLine("Done.");
            Console.ReadLine();
        }

        static void SingleRequests()
        {
            // fire off the web request 1,000 times, and time it
            Stopwatch sw = new Stopwatch();
            sw.Start();
            for (int i = 0; i < testCount; i++)
            {
                SendWebRequest();
            }
            sw.Stop();
            Console.WriteLine("Single requests: {0} seconds", sw.Elapsed.TotalSeconds);

        }

        static void ParallelRequests()
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            List actions = new List();
            for (int i = 0; i < testCount; i++)
            {
                Action a = new Action(SendWebRequest);
                actions.Add(a);
            }
            Parallel.Invoke(actions.ToArray());
            sw.Stop();
            Console.WriteLine("Parallel requests: {0} seconds", sw.Elapsed.TotalSeconds);
        }

        static void MultiThreaded()
        {
            // ThreadPool.SetMaxThreads(10, 10);
            Stopwatch sw = new Stopwatch();
            sw.Start();

            List doneEvents = new List();
            for (int i = 0; i < testCount; i++)
            {
                ManualResetEvent resetEvent = new ManualResetEvent(false);
                doneEvents.Add(resetEvent);
                ThreadWrapper tw = new ThreadWrapper(resetEvent);
                WaitCallback callBack = new WaitCallback(tw.ThreadPoolCallback);
                ThreadPool.QueueUserWorkItem(callBack);

                if (doneEvents.Count == 64)
                {
                    WaitHandle.WaitAll(doneEvents.ToArray());
                    doneEvents.Clear();
                }
            }

            if (doneEvents.Count > 0)
            {
                WaitHandle.WaitAll(doneEvents.ToArray());
            }
            sw.Stop();
            Console.WriteLine("Thread pool requests: {0} seconds", sw.Elapsed.TotalSeconds);
        }

        static void SendWebRequest()
        {
            HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);
            req.Method = "GET";

            HttpWebResponse webResponse = (HttpWebResponse)req.GetResponse();
            using (StreamReader responseStream = new StreamReader(webResponse.GetResponseStream()))
            {
                string response = responseStream.ReadToEnd();
                responseStream.Close();
            }
            webResponse.Close();
            //Console.WriteLine("1");
        }

        public class ThreadWrapper
        {

            private ManualResetEvent doneEvent;


            public ThreadWrapper(ManualResetEvent doneEvent)
            {
                this.doneEvent = doneEvent;
            }

            public void ThreadPoolCallback(Object threadContext)
            {
                SendWebRequest();
                this.doneEvent.Set();
            }


        }
    }
}

Prettify

Following stackoverflow’s example, I’ve started using prettify for syntax highlighting on this blog.  I’ve used the Google syntax highlighter in the past, but prettify just seems easier as I don’t need to mark it with class names or anything – all I need to do is wrap code in <pre> and <code> tags, and it just works.  It’s clever enough to work out the language.

Getting it working was really simple.  I downloaded the source, combined all the .js files into one using Google’s Closure Compiler, and update the .css file to the styles I like (stolen without shame from stackoverflow – I love that site).

To get the styles to apply to my blog, I also added an initialisation script to make the prettify call, and it works.  Hopefully this should save me some time in the future.

I found a really good article here that showed me how to set this all up.  I’ll update old blog posts at some stage, for now they’re going to appear without styling.

.NET – Detecting Modem COM Ports

We have a project at the moment where we’re using GSM modems attached to the local machine to send an SMS.  One of the issues we’ve had is when the modem is not actually connected before our service starts up: our service sometimes attaches to the COM Port that is required by the modem, and this prevents the modem from ever connecting.

There is a simple way to detect which COM ports are assigned to modems installed on your machine:

using System.Management;

ManagementObjectSearcher mos = new ManagementObjectSearcher("SELECT * FROM Win32_POTSModem");
foreach (ManagementObject mo in mos.Get())
{
    string s= mo["AttachedTo"].ToString();
}

Other properties available can be looked up here: http://msdn.microsoft.com/en-us/library/aa394360%28VS.85%29.aspx

Using the WiX Installer

I’ve been playing with the Wix Installer for the last few days, and I’m absolutely amazed by it.  It’s a real bastard to get into, but once you’re up and running it’s pretty incredible how much can be customised.  There is a LOT of documentation out there, but the most useful link I found was this tutorial – it really is worth your while to read the entire tutorial BEFORE trying to create your own installation project.

Instead of creating another tutorial, I thought I’d list the items that tripped me up.

Visual Studio 2010 Integration

I downloaded Wix 3.0 (64 bit), and after installation, it wasn’t available as a project within Visual Studio.  Download the beta version of 3.5 – it seems pretty stable and integrates with Visual Studio,.

No Wizard

No wizard!?  WTF!?  It turns out it’s XML-based – you end up doing a lot of the setup by hand.  Deal with it.

References in Visual Studio

You will probably require extensions so you can customise the UI, or perhaps ensure the user has a .NET Framework installed.  I’m working in Visual Studio 2010, and I wasn’t sure how to do this so I added compiler and linker options in the project properties.  You do not need to do this.  All you need to do is click “Add Reference” on the project, and select the extension from the list that appears.  For example, if you want to add the .NET Framework prerequisites, right-click References, and select WixNetFxExtension.  That is all you need to do.

Default UI

The default installation dialog really sucked, and it took me a while to work out that you need to specify a different UI type.  That’s what you get for not reading the above tutorial before creating your project.  You need to create a <UI> element, and specify the UIRef you want to use.

Dialog Sequence

Creating custom dialogs is pretty easy once you get the hang of it, but I struggled a little with navigation between dialogs.  Eventually I found the reference on the Wix site to a full list of Wix dialogs that you can navigate between at http://wix.sourceforge.net/manual-wix3/WixUI_dialogs.htm.

Using the IIS Extension (For deploying web sites)

This foxed me a little too.  Mostly, using the extensions just worked in Visual Studio, but the IIS extension doesn’t.  You need to add the namespace reference, something along the lines of:

<wix xmlns="http://schemas.microsoft.com/wix/2006/wi" xmlns:iis="http://schemas.microsoft.com/wix/IIsExtension" xmlns:netfx="http://schemas.microsoft.com/wix/NetFxExtension">

You can then use the extension elements as follows:

<iis:WebSite Id='DefaultWebSite' Description='Default Web Site'>
       <iis:WebAddress Id='AllUnassigned' Port='80' />
     </iis:WebSite>

Unit Testing and DateTime Comparisons

 

One thing I’ve found annoying for the long time, is trying to assert DateTime equality when unit testing, particularly when the DateTimes have been parsed from strings in the underlying method.  Two DateTime constructs, despite being identical in terms of their values, often won’t assert as being equal and your unit test fails.  As such, you end up doing other sorts of tests, for example checking the individual values.

To help test dates, what I’ve done is created a helper class for asserting two dates are “equal”, to a specifed level of precision:

public static class AssertHelper
    {
        /// 

        /// Asserts that two dates are equal by checking the year, month, day, 
        /// hour, minute, second and millisecond components.
        /// 
        /// The current date
        /// 
        public static void AreDatesEqual(DateTime expected, DateTime actual, DateTimePrecision precision)
        {
            if (precision >= DateTimePrecision.Year && expected.Year != actual.Year)
            {
                throw new NUnit.Framework.AssertionException("Year in dates do not match as expected.");
            }
            if (precision >= DateTimePrecision.Month && expected.Month != actual.Month)
            {
                throw new NUnit.Framework.AssertionException("Month in dates do not match as expected.");
            }
            if (precision >= DateTimePrecision.Day && expected.Day != actual.Day)
            {
                throw new NUnit.Framework.AssertionException("Day in dates do not match as expected.");
            }
            if (precision >= DateTimePrecision.Hour && expected.Hour != actual.Hour)
            {
                throw new NUnit.Framework.AssertionException("Hour in dates do not match as expected.");
            }
            if (precision >= DateTimePrecision.Minute && expected.Minute != actual.Minute)
            {
                throw new NUnit.Framework.AssertionException("Minute in dates do not match as expected.");
            }
            if (precision >= DateTimePrecision.Second && expected.Second != actual.Second)
            {
                throw new NUnit.Framework.AssertionException("Second in dates do not match as expected.");
            }
            if (precision >= DateTimePrecision.Millisecond && expected.Millisecond != actual.Millisecond)
            {
                throw new NUnit.Framework.AssertionException("Millisecond in dates do not match as expected.");
            }


        }
    }

    public enum DateTimePrecision
    {
        Year = 0,
        Month = 1,
        Week = 2,
        Day = 3,
        Hour = 4,
        Minute = 5,
        Second = 6,
        Millisecond = 7
    }

This effectively allows me to unit test that the dates were close enough, without worrying to much about the exact equality. This solution, although working, somehow feels dirty. I'd love to know if anyone else has any better solutions for unit testing date equality in .NET.

ILMerge

I Used ILMerge today for the first time, and it's great. Aspnet_merge.exe is actually based on this tool, and it allows you to combine all your assemblies into a single exe or dll - great for simple deployment.

It gets installed by default to C:\Program Files (x86)\Microsoft\ILMerge, with fairly decent documentation. To consolidate a fairly simple Windows exe with three dlls into a single exe, all I did was the following:

ILMerge.exe /t:winexe /out:Output.exe /ndebug Program.exe Lib1.dll Lib2.dll Lib3.dll