Monthly Archives: December 2008

nvarchar vs. varchar in SQL Server, BEWARE

Posted on by

I discovered one of the scariest performance problems with SQL Server I have ever seen today. This problem is so easy to over look, yet extremely detrimental to database performance.

Consider the following table.

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Users
     (
     id INT NOT NULL,
     username VARCHAR(50) NOT NULL
     )
GO
ALTER TABLE dbo.Users ADD CONSTRAINT
     PK_Users_id PRIMARY KEY CLUSTERED
     (
     id
     )
 
GO
CREATE UNIQUE NONCLUSTERED INDEX IX_Users_username ON dbo.Users
     (
     username
     )
GO
COMMIT

Now look at the query plans of the following queries, that differ by a single character.

If you look closely, the query that uses an nvarchar parameter does an index scan while the one that users varchar does an index seek. This is very important, because an index seek is orders of magnitude faster than a scan.

The reason this occurs is because the parameter and column have different collation sets.

Stop pulling your hair out, use Process Explorer

Posted on by

How many times have you tried to open a file in Windows, only to see it is already in use by some another program? Thus begins the witch hunt of process destruction, until you can open the file.

Process Explorer gets rid of this problem. Just search for a file name, and it tells you which process has a hold of it. This is only a small piece of its functionality, but a huge piece of my sanity. Why doesn’t Windows Ship with all the SysInternals stuff?

Using Powershell for MD5 Checksums

Posted on by

The older I get, the more paranoid I get about things I download. Today I downloaded a freeware program from a 3rd party download site, since the publisher doesn’t actually allow downloading from their site. All they give is an MD5 checksum.

Well, I decided to write a quick Powershell script to do the checksum for me. (Got some pointers from another blog writing a cmd-let to do the same thing)

param
(
    $file
)
 
$algo = [System.Security.Cryptography.HashAlgorithm]::Create("MD5")
$stream = New-Object System.IO.FileStream($file, [System.IO.FileMode]::Open)
 
$md5StringBuilder = New-Object System.Text.StringBuilder
$algo.ComputeHash($stream) | % { [void] $md5StringBuilder.Append($_.ToString("x2")) }
$md5StringBuilder.ToString()
 
$stream.Dispose()

I probably should add a try/catch for the stream disposal and file existence validation. But, you get the picture.

The Welcome Screen

Posted on by

Why would I ever want to see this welcome screen again?

Beautiful designs start with the needs of the user, not the the requirements of the business. This welcome screen probably started because someone said “We should welcome users every time they start the application, lots of other apps do it!”.

The problem is, this welcome screen is counter-productive and adds no value to me the user.

One thing I have learned is this type of thing is all too easy to happen to an application. Designers and developers should be in constant communication with each other, and users. Simple usability tests are the easiest way to make this happen.

The trouble with Big Up Front Design

Posted on by

Agile methodologies say that big up front design (BUFD) is bad. What happens when requirements change? What happens when scalability issues are encountered? How can you know solution X is better than Y? BUFD fails in these cases because unpredictable issues changed the design.

For the most part, I agree in that it is impossible to plan for everything. But I don’t believe it to mean that design is dead.

For example, if you are designing Windows Vista, would you want to have the Windows XP architect involved? Absolutely. Windows XP has proven itself in some ways and failed in others. Learning from how it currently works is a huge advantage to starting from scratch.

Why do most web applications use a 3-tier design? Because it is proven to work. I don’t know if it is the best, but it has always worked for me.

The trouble with BUFD isn’t the design, but the prediction. No matter how hard you try, you can’t predict how something you have never done before will work. However, designing based on proven concepts is something worth spending time on. It will change over time but starting on the right foot is not to be taken lightly.

The best way to explain this is to think of how it would apply to me in real life. If I was told to create a new webmail application, I would spend a few weeks just talking with Mike. Mike has been the lead developer on Noteworthy Webmail for nearly 4 years. I would create a huge design document based on his thoughts and ideas about how to create this application. Even though I know this design would change over time, at least it would provide an awesome foundation for me to start.