Sunday, August 26, 2007

Levenshtein Distance

Levenshtein Distance

Levenshtein Distance, in Three Flavors

by Michael Gilleland, Merriam Park Software

The purpose of this short essay is to describe the Levenshtein distance algorithm and show how it can be implemented in three different programming languages.

What is Levenshtein Distance?
Demonstration
The Algorithm
Source Code, in Three Flavors
References
Other Flavors


What is Levenshtein Distance?

Levenshtein distance (LD) is a measure of the similarity between two strings, which we will refer to as the source string (s) and the target string (t). The distance is the number of deletions, insertions, or substitutions required to transform s into t. For example,

  • If s is "test" and t is "test", then LD(s,t) = 0, because no transformations are needed. The strings are already identical.
  • If s is "test" and t is "tent", then LD(s,t) = 1, because one substitution (change "s" to "n") is sufficient to transform s into t.
The greater the Levenshtein distance, the more different the strings are.

Levenshtein distance is named after the Russian scientist Vladimir Levenshtein, who devised the algorithm in 1965. If you can't spell or pronounce Levenshtein, the metric is also sometimes called edit distance.

The Levenshtein distance algorithm has been used in:

  • Spell checking
  • Speech recognition
  • DNA analysis
  • Plagiarism detection

Demonstration

The following simple Java applet allows you to experiment with different strings and compute their Levenshtein distance:


The Algorithm

Steps

Step Description
1 Set n to be the length of s.
Set m to be the length of t.
If n = 0, return m and exit.
If m = 0, return n and exit.
Construct a matrix containing 0..m rows and 0..n columns.
2 Initialize the first row to 0..n.
Initialize the first column to 0..m.
3 Examine each character of s (i from 1 to n).
4 Examine each character of t (j from 1 to m).
5 If s[i] equals t[j], the cost is 0.
If s[i] doesn't equal t[j], the cost is 1.
6 Set cell d[i,j] of the matrix equal to the minimum of:
a. The cell immediately above plus 1: d[i-1,j] + 1.
b. The cell immediately to the left plus 1: d[i,j-1] + 1.
c. The cell diagonally above and to the left plus the cost: d[i-1,j-1] + cost.
7 After the iteration steps (3, 4, 5, 6) are complete, the distance is found in cell d[n,m].

Example

This section shows how the Levenshtein distance is computed when the source string is "GUMBO" and the target string is "GAMBOL".

Steps 1 and 2

G U M B O
0 1 2 3 4 5
G 1
A 2
M 3
B 4
O 5
L 6

Steps 3 to 6 When i = 1

G U M B O
0 1 2 3 4 5
G 1 0
A 2 1
M 3 2
B 4 3
O 5 4
L 6 5

Steps 3 to 6 When i = 2

G U M B O
0 1 2 3 4 5
G 1 0 1
A 2 1 1
M 3 2 2
B 4 3 3
O 5 4 4
L 6 5 5

Steps 3 to 6 When i = 3

G U M B O
0 1 2 3 4 5
G 1 0 1 2
A 2 1 1 2
M 3 2 2 1
B 4 3 3 2
O 5 4 4 3
L 6 5 5 4

Steps 3 to 6 When i = 4

G U M B O
0 1 2 3 4 5
G 1 0 1 2 3
A 2 1 1 2 3
M 3 2 2 1 2
B 4 3 3 2 1
O 5 4 4 3 2
L 6 5 5 4 3

Steps 3 to 6 When i = 5

G U M B O
0 1 2 3 4 5
G 1 0 1 2 3 4
A 2 1 1 2 3 4
M 3 2 2 1 2 3
B 4 3 3 2 1 2
O 5 4 4 3 2 1
L 6 5 5 4 3 2

Step 7

The distance is in the lower right hand corner of the matrix, i.e. 2. This corresponds to our intuitive realization that "GUMBO" can be transformed into "GAMBOL" by substituting "A" for "U" and adding "L" (one substitution and 1 insertion = 2 changes).


Source Code, in Three Flavors

Religious wars often flare up whenever engineers discuss differences between programming languages. A typical assertion is Allen Holub's claim in a JavaWorld article (July 1999): "Visual Basic, for example, isn't in the least bit object-oriented. Neither is Microsoft Foundation Classes (MFC) or most of the other Microsoft technology that claims to be object-oriented."

A salvo from a different direction is Simson Garfinkels's article in Salon (Jan. 8, 2001) entitled "Java: Slow, ugly and irrelevant", which opens with the unequivocal words "I hate Java".

We prefer to take a neutral stance in these religious wars. As a practical matter, if a problem can be solved in one programming language, you can usually solve it in another as well. A good programmer is able to move from one language to another with relative ease, and learning a completely new language should not present any major difficulties, either. A programming language is a means to an end, not an end in itself.

As a modest illustration of this principle of neutrality, we present source code which implements the Levenshtein distance algorithm in the following programming languages:


Java

public class Distance {

//****************************
// Get minimum of three values
//****************************

private int Minimum (int a, int b, int c) {
int mi;

mi = a;
if (b < mi) {
mi = b;
}
if (c < mi) {
mi = c;
}
return mi;

}

//*****************************
// Compute Levenshtein distance
//*****************************

public int LD (String s, String t) {
int d[][]; // matrix
int n; // length of s
int m; // length of t
int i; // iterates through s
int j; // iterates through t
char s_i; // ith character of s
char t_j; // jth character of t
int cost; // cost

// Step 1

n = s.length ();
m = t.length ();
if (n == 0) {
return m;
}
if (m == 0) {
return n;
}
d = new int[n+1][m+1];

// Step 2

for (i = 0; i <= n; i++) {
d[i][0] = i;
}

for (j = 0; j <= m; j++) {
d[0][j] = j;
}

// Step 3

for (i = 1; i <= n; i++) {

s_i = s.charAt (i - 1);

// Step 4

for (j = 1; j <= m; j++) {

t_j = t.charAt (j - 1);

// Step 5

if (s_i == t_j) {
cost = 0;
}
else {
cost = 1;
}

// Step 6

d[i][j] = Minimum (d[i-1][j]+1, d[i][j-1]+1, d[i-1][j-1] + cost);

}

}

// Step 7

return d[n][m];

}

}

C++

In C++, the size of an array must be a constant, and this code fragment causes an error at compile time:

int sz = 5;
int arr[sz];

This limitation makes the following C++ code slightly more complicated than it would be if the matrix could simply be declared as a two-dimensional array, with a size determined at run-time.

In C++ it's more idiomatic to use the System Template Library's vector class, as Anders Sewerin Johansen has done in an alternative C++ implementation.

Here is the definition of the class (distance.h):

class Distance
{
public:
int LD (char const *s, char const *t);
private:
int Minimum (int a, int b, int c);
int *GetCellPointer (int *pOrigin, int col, int row, int nCols);
int GetAt (int *pOrigin, int col, int row, int nCols);
void PutAt (int *pOrigin, int col, int row, int nCols, int x);
};

Here is the implementation of the class (distance.cpp):

#include "distance.h"
#include
#include

//****************************
// Get minimum of three values
//****************************

int Distance::Minimum (int a, int b, int c)
{
int mi;

mi = a;
if (b < mi) {
mi = b;
}
if (c < mi) {
mi = c;
}
return mi;

}

//**************************************************
// Get a pointer to the specified cell of the matrix
//**************************************************

int *Distance::GetCellPointer (int *pOrigin, int col, int row, int nCols)
{
return pOrigin + col + (row * (nCols + 1));
}

//*****************************************************
// Get the contents of the specified cell in the matrix
//*****************************************************

int Distance::GetAt (int *pOrigin, int col, int row, int nCols)
{
int *pCell;

pCell = GetCellPointer (pOrigin, col, row, nCols);
return *pCell;

}

//*******************************************************
// Fill the specified cell in the matrix with the value x
//*******************************************************

void Distance::PutAt (int *pOrigin, int col, int row, int nCols, int x)
{
int *pCell;

pCell = GetCellPointer (pOrigin, col, row, nCols);
*pCell = x;

}

//*****************************
// Compute Levenshtein distance
//*****************************

int Distance::LD (char const *s, char const *t)
{
int *d; // pointer to matrix
int n; // length of s
int m; // length of t
int i; // iterates through s
int j; // iterates through t
char s_i; // ith character of s
char t_j; // jth character of t
int cost; // cost
int result; // result
int cell; // contents of target cell
int above; // contents of cell immediately above
int left; // contents of cell immediately to left
int diag; // contents of cell immediately above and to left
int sz; // number of cells in matrix

// Step 1

n = strlen (s);
m = strlen (t);
if (n == 0) {
return m;
}
if (m == 0) {
return n;
}
sz = (n+1) * (m+1) * sizeof (int);
d = (int *) malloc (sz);

// Step 2

for (i = 0; i <= n; i++) {
PutAt (d, i, 0, n, i);
}

for (j = 0; j <= m; j++) {
PutAt (d, 0, j, n, j);
}

// Step 3

for (i = 1; i <= n; i++) {

s_i = s[i-1];

// Step 4

for (j = 1; j <= m; j++) {

t_j = t[j-1];

// Step 5

if (s_i == t_j) {
cost = 0;
}
else {
cost = 1;
}

// Step 6

above = GetAt (d,i-1,j, n);
left = GetAt (d,i, j-1, n);
diag = GetAt (d, i-1,j-1, n);
cell = Minimum (above + 1, left + 1, diag + cost);
PutAt (d, i, j, n, cell);
}
}

// Step 7

result = GetAt (d, n, m, n);
free (d);
return result;

}

Visual Basic

'*******************************
'*** Get minimum of three values
'*******************************

Private Function Minimum(ByVal a As Integer, _
ByVal b As Integer, _
ByVal c As Integer) As Integer
Dim mi As Integer

mi = a
If b < mi Then
mi = b
End If
If c < mi Then
mi = c
End If

Minimum = mi

End Function

'********************************
'*** Compute Levenshtein Distance
'********************************

Public Function LD(ByVal s As String, ByVal t As String) As Integer
Dim d() As Integer ' matrix
Dim m As Integer ' length of t
Dim n As Integer ' length of s
Dim i As Integer ' iterates through s
Dim j As Integer ' iterates through t
Dim s_i As String ' ith character of s
Dim t_j As String ' jth character of t
Dim cost As Integer ' cost

' Step 1

n = Len(s)
m = Len(t)
If n = 0 Then
LD = m
Exit Function
End If
If m = 0 Then
LD = n
Exit Function
End If
ReDim d(0 To n, 0 To m) As Integer

' Step 2

For i = 0 To n
d(i, 0) = i
Next i

For j = 0 To m
d(0, j) = j
Next j

' Step 3

For i = 1 To n

s_i = Mid$(s, i, 1)

' Step 4

For j = 1 To m

t_j = Mid$(t, j, 1)

' Step 5

If s_i = t_j Then
cost = 0
Else
cost = 1
End If

' Step 6

d(i, j) = Minimum(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + cost)

Next j

Next i

' Step 7

LD = d(n, m)
Erase d

End Function

References

Other discussions of Levenshtein distance are:

Other Flavors

The following people have kindly consented to make their implementations of the Levenshtein Distance Algorithm in various languages available here:

  • Eli Bendersky has written an implementation in Perl.
  • Barbara Boehmer has written an implementation in Oracle PL/SQL.
  • Rick Bourner has written an implementation in Objective-C.
  • Chas Emerick has written an implementation in Java, which avoids an OutOfMemoryError which can occur when my Java implementation is used with very large strings.
  • Joseph Gama has written an implementation in TSQL, as part of a package of TSQL functions at Planet Source Code.
  • Anders Sewerin Johansen has written an implementation in C++, which is more elegant, better optimized, and more in the spirit of C++ than mine.
  • Lasse Johansen has written an implementation in C#.
  • Adam Lindberg and Fredrik Svensson have written an implementation in Erlang.
  • Alvaro Jeria Madariaga has written an implementation in Delphi.
  • Lorenzo Seidenari has written an implementation in C, and Lars Rustemeier has provided a Scheme wrapper for this C implementation as part of Eggs Unlimited, a library of extensions to the Chicken Scheme system.
  • Steve Southwell has written an implementation in Progress 4gl.
  • Lukasz Stilger has written an implementation in JavaScript which illustrates the algorithm in operation (well worth seeing). Note that "wyraz" is Polish for "word". A separate page with the source code as text is here.
  • Jorge Mas Trullenque points out that "the calculation needs O(n) memory, so using a two-dimensional matrix in a practical implementation is wasteful." He has written an implementation in Perl that uses only one one-dimensional vector.
  • Joerg F. Wittenberger has written an implementation in Rscheme.

Other implementations outside these pages include:

  • An Emacs Lisp implementation by Art Taylor.
  • A Python implementation by Magnus Lie Hetland.
  • A Tcl implementation by Richard Suchenwirth (thanks to Stefan Seidler for pointing this out).
  • A PHP implementation (thanks to Dan Tripp for pointing this out).
  • A Scheme implementation by Neil Van Dyke.

Tame the Beast by Matching Similar Strings



















Tame the Beast by Matching Similar Strings
Contributed by Simon White
2004-02-09












My interest in string similarity stems from a desire for good user interface design. Computers are seen by many as unfriendly, unforgiving beasts that respond unkindly to requests that are almost meaningful. In this article, I demonstrate how computers can be programmed to be more forgiving of their users' mistakes, with no additional burden on the user such as learning a special query format. Moreover, the techniques described are very widely applicable and often easy to implement.

Although my interest is in the user-interface, it is not the only place where such techniques can be employed. For example, the Hamming distance (described later) was traditionally used to recover from low-level bit transfer errors in electronic communications. In the future, I believe some of the techniques could be used to aid communication among independently acting computer programs (intelligent agents) as they try to make sense of what another agent 'said'. But for now, I would like you to think of realigning an 'unexpected' input string with an input that is expected, or known to be valid, in the context of a user-interface.


Let me be a little more concrete. When you enter a search string to look for a book at Amazon.com, your input is matched against the descriptions of known products held in a database. It is quite likely that your input does not exactly match any of the 'expected' inputs (that is, book titles or authors) in the database. For example, if you enter the string 'Web Database Applications', you would like the search to return the book with the title 'Web Database Applications with PHP and MySQL', even though it is not an exact match. And you might also expect to see the same book listed if you entered 'PHP Web Applications', or even the misspelling 'Web Aplications'. The task is therefore to find which of the expected strings (in this case product descriptions) are similar, or perhaps most similar, to the user's input.


There are two main classes of algorithms for matching string similarity, equivalence methods and similarity ranking methods.


Equivalence methods compare two strings and return a value of true or false according to whether the method deems those two strings to be, in some sense, equivalent. In terms of user-interface design, your application can be more forgiving of user inputs if it accepts equivalent strings instead of only exact matches. A simple example of equivalence is to treat 'Tweetle-Beetle Battle' the same as 'TWEETLE BEETLE BATTLE' despite the differences in case, and the replacement of a hyphen with a space in the second string.


Word Stemming


Word stemming is a technique that reduces closely related words to a basic canonical form or 'stem'. For example, the user inputs 'swims' and 'swimming' can be reduced to the basic stem 'swim' before performing an exact match against expected inputs. Stemming makes use of a suffix dictionary that contains lists of possible word endings. However, such a list is clearly language-dependent and even regional differences of the same language must be considered (for example, compare British spelling 'standardise' with American spelling 'standardize'). Also, not all languages lend themselves to such treatment, although it has been demonstrated for most languages of the Indo-European family (which includes Latin-based and Germanic languages).


Deriving stemming algorithms is a difficult, time-consuming and error-prone activity. Therefore, for application building, I can only recommend using tools such as Snowball, with its suite of existing stemming algorithms for many languages.


Synonyms


In this approach, synonyms of expected inputs are stored explicitly. For example, with the string 'television', you might also store 'TV' and 'televisions'; and with the string 'license' you might also store 'licence'. As with word stemming, user inputs are converted to a canonical form before any further processing.


This mechanism can provide a forgiving user-interface, and is also language independent. Unfortunately, it does mean that many synonym strings must be prepared in advance to anticipate every possible user input. You could argue that the approach simply increases the number of expected inputs, rather than providing a better algorithm to find the strings that are of real interest. However, if you reconsider the problem of retrieving product descriptions from a database, you should see that there are other advantages. Firstly, as synonyms are resolved close to the user-interface, you can index your products in the system's back end using a small, controlled keyword vocabulary. Secondly, the architecture provides a clean separation between these user-interface concerns and the integrity of the data.


Wildcards and Regular Expressions


When you search for a file on your hard disk, you might use a search pattern with a wildcard such as '*.txt' (anything with the suffix '.txt'). In a similar way, applications that perform information retrieval can also employ pattern matching to improve the chances of finding the information of interest. One approach is to expose the full power of regular expressions in the user-interface, but the complex functionality and cryptic syntax usually confuses more than it helps. What we need is a way that harnesses the power of pattern matching without exposing it to the user. One idea is to prepend and append the user's input with the wild card character, and then use regular expression matching instead of exact matching. This has the effect of searching for all strings that contain the user's input. Another idea is to take each word (that is, space-separated token) of the input and apply the same wild card prepending and appending. In this case, the input 'go fish' would become '*go* *fish*', which matches 'gone fishing' as well as 'go fishing'.


The Soundex algorithm is an attempt to match strings that sound alike. The idea is that you take the two strings of the comparison, map each of them to a new string that represents their phonetics, and then compare those strings for an exact match. The algorithm is only intended to work with English pronunciation, and there are plenty of counter-examples, even in English, where it doesn't work. However, it is easy to implement and, even better, is already available as a pre-programmed function in the Oracle Database Management System. There's also a good chance that you are able to find an implementation in your favorite programming language by a quick web search.


The algorithm works as follows. When mapping the original strings to their phonetic strings, the first letter is always retained, and the rest of the string is processed in a left to right fashion. The subsequent letters of the string are compressed to a three digit code according to the scheme shown in Table 1. Since the first letter is always retained, the algorithm always generates a 4 digit string. The code '0' is used as padding if there are not enough letters in the input string, and any excess letters are disregarded.






































LetterPhonetic Code
B,F,P,V1
C,G,J,K,Q,S,X,Z2
D,T3
L4
M,N5
R6
A,E,I,O,U,Y,H,Wnot coded

Table 1: Phonetic Codes in the Soundex Algorithm


For example, the strings 'LICENCE', 'LICENSE' and 'LICENSING' all map to the same Soundex string, 'L252'. Additionally,



  1. adjacent pairs of the same consonant are treated as one

  2. adjacent consonants from the same code group are treated as one

  3. a consonant immediately following an initial letter from the same code group is ignored

  4. consonants from the same code group separated by W or H are treated as one


The Soundex algorithm is interesting because it addresses the pronunciation of words, rather than raw lexical similarity. Its main drawbacks are that it is language dependent, and there are many examples of similar strings that nevertheless produce different Soundex codes. And of course it only provides for comparisons of alphabetic characters - anything outside of the range 'A'-'Z' will simply be ignored.


The Soundex algorithm is also very old (it is documented in Donald Knuth's "The Art of Computer Programming", from 1973, but attributed to 1918 and 1922 U.S. Patents by Margaret K. Odell and Robert C. Russell). A more recent attempt at the same problem, called MetaPhone, dates from 1990 and allegedly gives better results. There is a description of MetaPhone on the web, and you can also test the algorithm online against databases of names and place names.


Similarity ranking methods compare a given string to a set of strings and rank those strings in order of similarity. To produce a ranking, we need a way of saying that one match is better than another. This is done by returning a numeric measure of similarity as the result of each comparison. Alternatively, you can think of the distance between two strings, instead of their similarity. Strings with a large distance between them have low similarity, and vice versa.


Two very common methods for ranking similarity are the Longest Common Sub-string and Edit Distance.


Longest Common Substring


The longest common substring between two strings is the longest contiguous chain of characters that exists in both strings. The longer the substring, the better the match between the two strings. This simple approach can work very well in practice.


A disadvantage of this approach is that the position of an 'error' in the input affects the computed similarity between the two strings. If the error occurs in the middle of the string, then the distance between the two strings will be greater than if the error occurred at one end. For example, suppose we make a simple typing error on the keyboard by pressing the key adjacent to the one intended. With a word such as 'PINEAPPLE', typing 'PINESPPLE' gives a longest common substring of length 4, whereas 'OINEAPPLE' gives a value of 8. The problem is that 'PINESPPLE' is deemed to be just as good a match with 'PINEAPPLE' as the string 'PINE', which is probably not what you want.


Edit Distance


This method focuses on the most common typing errors, namely character omissions, insertions, substitutions and reversals. The idea is to compute the minimum number of such operations that it would take to transform one string into another. This number gives an indication of the similarities of the strings. A value of 0 indicates that the two strings are identical


The algorithm can be described more generally by associating a cost with each of the operations, and deriving the distance between two strings as the minimum cost that transforms one string into another. There are two widely recognized variations of the edit distance. The Levenshtein Edit Distance is the most common variation and allows insertion, deletion or substitution of a single character where the cost of each operation is 1. The Damerau Edit Distance is identical to the Levenshtein edit distance, except that it also allows the operation of transposing (swapping) two adjacent characters.


Implementations of the Levenshtein edit distance can be found on the web.


Hamming Distance


Although I do not recommend the Hamming distance for the majority of string-based information retrieval tasks, I should mention it for completeness. The Hamming distance between two character strings is the number of positions in which the characters of the two strings are different. So, for example, the distance between 'REPAIR' and 'REPOSE' is 3, whereas the distance between 'WORK' and 'REST' is 4. (According to the literature, strings of different lengths have an infinite Hamming distance between them, so when comparing strings of different lengths, you may decide to 'cheat' by padding the shorter of the two strings on the right with extra spaces before comparing the strings.)


The problem with this metric is that apparently very similar strings can be given a high Hamming distance. Consider that there are no two pairs of characters that match positionally in the following two strings:



  1. 'THE QUICK BROWN FOX JUMPED OVER THE LAZY DOG'

  2. 'MY QUICK BROWN FOX JUMPED OVER THE LAZY DOGS'


Many information retrieval systems use a string-based query, often just a single string, to find the information of interest to its user. The ability of the system to find relevant information based on the user's input is key to a successful system. This ability can be significantly enhanced by employing an approximate string matching algorithm (which need not be invoked until it is known that no exact matches exist). Conversely, failure to find relevant information (particularly when the user knows it to be present) serves only to frustrate, and perpetuate the myth of the cantankerous computer.


I described the algorithms in two classes: equivalence methods and similarity ranking methods. Equivalence methods return a Boolean result, whereas the similarity ranking methods return a numeric similarity measure or distance metric. In information retrieval systems, it is possible to mix methods to produce a faster hybrid approach. A typical approach is to employ a two-pass mechanism in which an equivalence method is used by the database as a first pass filter, and a ranked similarity method is applied to the filtered entries for the second pass. Ranked similarity methods tend to be algorithmically more complex than equivalence methods, so are usually implemented as custom code outside of the database.


When choosing an algorithm to use, there are several criteria that will influence your choice of algorithm. For example, what kinds of mismatch are you attempting to recover from? Are you trying to recover from typing errors? Or are you trying to find 'sound-alike' or look-alike strings? Do the users of the system all speak the same language, or does the method need to be language independent? Do the results need to be ranked in order of similarity? How many strings will the algorithm have to compare, and how fast must it run? Is a two-pass mechanism appropriate or necessary?


Lastly, you might imagine that this area of computing has been so well explored that the best algorithms have already been found and are well-known. However, it is still a research area and I also wouldn't be at all surprised if the teams at Google are working on novel approximate string-matching algorithms right now!









DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.





Technorati :

Friday, August 24, 2007

How to use YUI JS Compressor inside a NAnt build script

this article describes to yuse YUI JS Compressor inside a NAnt build script.

read more | digg story

Thursday, August 23, 2007

13 disasters for production web sites and their solutions

http://www.codeproject.com/install/13disasters.asp (Codeproject)

When we first went live with Pageflakes back in the year 2005, most of us did not have experience with running a mass consumer high volume web application on the Internet. We went through all types of difficulties a web application can face as it grows. Frequent problems with software, hardware, and network were part of our daily life. In the last 2 years, we have overcome a lot of obstacles and established ourselves as one of the top most Web 2.0 applications in the world. From a thousand user website, we have grown to a million user website over the years. We have learnt how to architect a product that can withstand more than 2 million hits per day and sudden spikes like 7 million hits on a day. We have discovered under the hood secrets of ASP.NET 2.0 that solves many scalability and maintainability problems. We have also gained enough experience in choosing the right hardware and Internet infrastructure which can make or break a high volume web application. In this article, you will learn about 13 disasters than can happen to any production website anytime. These real world stories will help you prepare yourself well enough so that you do not go through the same problems as we did. Being prepared for these disasters upfront will save you a lot of time and money as well as build credibility with your users.

13 disasters

We have gone through many disasters over the years. Some of them are:

  1. Hard drive crashed, burned, got corrupted several times
  2. Controller malfunctions and corrupts all disks in the same controller
  3. RAID malfunction
  4. CPU overheated and burned out
  5. Firewall went down
  6. Remote Desktop stopped working after a patch installation
  7. Remote Desktop max connection exceeded. Cannot login anymore to servers
  8. Database got corrupted while we were moving the production database from one server to another over the network
  9. One developer deleted the production database accidentally while doing routine work
  10. Support crew at hosting service formatted our running production server instead of a corrupted server that we asked to format
  11. Windows got corrupted and was not working until we reinstalled
  12. DNS goes down
  13. Internet backbone goes down in different part of the world

These are some of the problems that usually happen in dedicated hosting. Let's elaborate some of these and make a disaster plan:

Hard drive crash

We experienced hard drive crashes frequently with cheap hosting providers. They used cheap SATA drives that were not reliable. So far we found Western Digital SATA drives to be most reliable. If you can spend money, go for SCSI drives. HP has a lot of variety of SCSI drives to choose from. Better always, go for SCSI drives on web and database server. They are costly, but they will save you from frequent disasters.

Screenshot - HdTachScreenshot.jpg

The above figure shows a benchmark of a good hard drive. You need to pay attention to disk speed rather than CPU speed. Generally processor and bus are standard and their performance does not vary much. Disk IO is generally the main bottleneck for production systems. For database server, the only thing you should look at is Disk Speed. Unless you have some really bad queries, CPU will never go too high. Disk IO will always be the bottleneck for database servers. So, for database you need to choose the fastest disk and controller solution.

Controller malfunction

This happens when the servers do not get tested properly and handed over to you in a hurry. Before you accept any server, make sure you get written guaranty that they passed all sorts of exhaustive hardware tests. Dell server's BIOS contain test suites for testing controller, disks, CPU etc. You can also use BurnInTest from www.Passmark.com to test your server's capability under high disk and CPU load. Just run the Benchmark Test for 4 to 8 hours and see how your server is doing. Keep an eye on temperature meters of CPU and Hard drives and ensure they do not get overheated.

RAID malfunction

RAID combines physical hard disks into a single logical unit either by using special hardware or software. Hardware solutions often are designed to present themselves to the attached system as a single hard drive and the operating system is unaware of the technical workings. Software solutions are typically implemented in the operating system, and again would present the RAID drive as a single drive to applications.

In our case, we had RAID malfunction which resulted in disks in the RAID controller corrupt data. We used Windows 2003's built in RAID controller. We learnt never to depend on software RAID and paid extra for hardware RAID. Make sure when you purchase a server that it has a hardware RAID controller.

Once you have chosen the right disks, the next step is to choose the right RAID configuration. RAID means multiple hard disks working together to serve as a single logical drive. For example, RAID 1 takes two identical physical disks and represents them as one single disk to the operation system. Thus every disk write goes to both of them simultaneously. If one disk fails, the other disk can take over and continue to serve the logical disk. Nowadays servers support "hot swap" enabled disks where you can take a disk right out of server while the server is running. The controller immediately diverts all requests to the other disk. This way you can take out a disk, do repair or replacement, then put it back again. Controller synchronizes both disks once the new disk is put into the controller. RAID 1 is suitable for web servers. Here are the pros and cons for RAID 1:

Pros
  • Mirroring provides 100% duplication of data
  • Read performance is faster than a single disk; if the array controller is capable of performing simultaneous reads from both devices of a mirrored pair. You should make sure your RAID controller has this ability. Otherwise Disk Read will become slower than having a single disk
  • Delivers the best performance of any redundant array type during a rebuild. As soon as you put back in a replacement disk after repairs, it quickly synchronizes it with the operational disk
  • No re-construction of data is needed. If a disk fails, copying on a block by block basis to a new disk is all that is required
  • No performance hit when a disk fails; storage appears to function normally to outside world
Cons
  • Raid 1 writes the information twice, because of this there is a minor performance penalty when compared to writing to a single disk
  • I/O performance in a mixed read-write environment is essentially no better than the performance of a single disk storage system
  • Requires two disks for 100% redundancy; doubling the cost. However, disks are cheap now

For database servers, RAID 5 is a better choice because it is faster than RAID 1. RAID 5 is more expensive than RAID 1 because it requires a minimum of 3 drives. But one drive can fail without affecting the availability of data. In the event of a failure, the controller regenerates the lost data of the failed drive from the other surviving drives.
By distributing parity across the arrays member disks, RAID Level 5 reduces (but does not eliminate) the write bottleneck. The result is asymmetrical performance, with reads substantially outperforming writes. To reduce or eliminate this intrinsic asymmetry, RAID level 5 is often augmented with techniques such as caching and parallel multiprocessors.

Pros
  • Best suited for heavy read applications like Database Servers where SELECT operation it lot higher than INSERT/UPDATE/DELETE
  • The amount of useable space is the number of physical drives in the virtual drive minus 1
Cons
  • A single disk failure reduces the array to RAID 0
  • Performance is slower than RAID 1 when rebuilding
  • Write performance is slower than read (write penalty)

CPU overheated and burned out

We had this only once that one of the server's CPU burnt out due to overheat. We were partially responsible for this because we had a bad query that made the SQL Server go 100% CPU. So, the poor server ran around 4 hours on 100% CPU and then died.

Servers should never burn out on high CPU usage. Generally servers have monitoring systems in place where the server turns itself off when CPU is about to burn out. This means the defective server did not have the monitoring system working properly. So, you should run tools to push your servers to 100% CPU for 8 hours and ensure they can withstand this. In the event of overheating, the monitoring systems should turn off the servers and save the hardware. However, if the server has a good cooling system, then the CPU will not overheat in 8 hours.

Screenshot - Stress_Test_CPU.png

Whenever we move to a new hosting provider we run stress test tools to simulate 100% CPU load on all our servers for 8 to 12 hours. The above figure shows 7 of our servers running on 100% CPU for hours without any problem. In our case, none of the servers got overheated and turned themselves off which means we got a good cooling system as well.

Firewall went down

Our hosting providers Firewall once malfunctioned and exposed our web servers to the public Internet unprotected. We soon found out they were infected and they were starting to shutdown automatically. So, we had to format them, patch them and turn on Windows Firewall. Nowadays, as best practice, we always turn on Windows Firewall on the external network card which is connected to the hardware Firewall. In fact, we purchase redundant firewall just to be on the safe side.

You should turn off File Sharing on the external network card. Unless you have a redundant firewall, you should turn on Windows Firewall as well. Some might argue this will affect performance. We have seen that Windows Firewall has near zero impact on performance.

Screenshot - Public_Network_Configuration_1.png

You should also disable NetBIOS protocol because you should never need it from an external network. You server should be completely invisible to the public network besides having port 80 and port 3389 (for remote desktop) open.

Screenshot - Public_Network_Configuration_2.png

Remote Desktop stopped working after a patch installation

This happened several times that after installing latest patches from Windows Update, Remote Desktop stopped working. Sometimes doing a restart of the server fixed it, sometimes we had to uninstall the patch. In such a case, the only ways you can get into your server are:

  1. use KVM over IP, or

  2. call a support technician

KVM over IP (Keyboard, Video, Mouse over IP) is a special hardware which connects to servers and transmits server's screen output to you. It also takes keyboard and mouse input from you and simulates it on the server. KVM works as if a monitor, keyboard and mouse is directly connected to the server. You can use regular Remote Desktop to connect to KVM and work on the server as if you are physically there. Benefits of KVM:

  • Access to all server platforms and all server types
  • A "Direct Connect Real Time" solution with no mouse delays due to conversion of signals. Software has to convert signals and this causes delays
  • Full use of GUI's
  • Full BIOS level access even when the network is down
  • The ability to get to the command line and rebuild servers remotely
  • Visibility of server boot errors and the ability to take action e.g. "Non-system disk error, please replace system disk and press any key to continue" or "power supply failure press F1 to continue"
  • Complete security from "hacking" - a physical connection is required to access the system

If Remote Desktop is not working or your firewall is down or the external network card of your server is not working, you can easily get into the server using KVM. Ensure your hosting provider has KVM support.

Remote Desktop max connection exceeded. Cannot login anymore to servers

This happens when users don't log off properly from remote desktop by closing the Remote Desktop Client. Disconnected sessions exceed the maximum number of active sessions and prevent new sessions. Thus no one can get into the server anymore. Incase this happens, go to Run and issue "mstsc /console" command. This will launch the same old Remote Desktop client you use every day. But when you will connect to remote desktops, it will connect you in Console Mode. Console Mode means connecting to the server as if you are right in front of the server and using the server's keyboard and mouse. Only one person can be connected in console mode at a time. Once you get into the console mode, it shows you the regular Windows GUI. There's nothing different about it. You can launch "Terminal Service Manager" and see the disconnected sessions and boot them out.

Database got corrupted while we were moving the production database from one server to another over the network

Copying large files over the network is not safe. You can have data corruption anytime, especially over the Internet. So, always use WinRAR in Normal compression mode to compress large files. Then copy the RAR file over the network. RAR file maintains CRC and checks the accuracy of the original file while decompressing. If WinRAR can decompress a file properly, you can be sure that there's no corruption in the original file. One caution about WinRAR compression modes: do not use Best compression mode. Always use Normal compression mode. We had seen large files getting corrupted on Best compression mode.

One developer deleted the production database accidentally while doing routine works

In early stages, we did not have a professional Sys Admin taking care of our servers. We, the developers, used to take care of our servers ourselves. This was not ideal. It was disastrous when one of the developers accidentally deleted the production Database thinking it's a backup database. It was his shift to cleanup space from our backup server. So, he went to the backup server using Remote Desktop, logged into SQL Server using "sa" user name and password. He needed to free up some space. So, he deleted the large "Pageflakes" database. SQL Server did warn him the database is in use. But as he never reads any alert which has an "OK" button in it, he clicked the OK button. We were doomed.

Here's what we did wrong which you should make sure you never do:

Sys Admin became too comfortable with the servers. There was lack of seriousness while working on remote desktop. It became routine monotonous absent minded work to him. This is a real problem with a sys admin. On the first month, you will see him very serious about his role. Every time he logs into remote desktop on production or maintenance servers, there's a considerable amount of curves on his forehead. But day by day, concentration starts to slip off and he starts working on production server as if he is working on his own laptop. At some point, someone needs to make him realize what the gravity of his actions is. He should wash his hands before sitting in front of remote desktop and then say his prayer: "O Lord! I am going to work on remote desktop. Grant me tranquility and absolute concentration and protect me from the devil who lures me to cause great harm to production servers".

All databases had the same "sa" password. If we had a different password, at least while typing the password, sys admin could realize where he is connecting to. Although he did connect to remote desktop on the maintenance server, but from SQL Server Management Studio, he connected to the primary database server as he did last time. SQL Server Management Studio remembered the last machine name and user name. So, all he did was enter password and hit enter and delete the database. Now that we have learnt our lessons, we have put the server's name inside the password. So, while typing the password, we know consciously which server we are going to connect to.

Don't ignore confirmation dialogs on remote desktops as you do on your local machine. Nowadays, we consider ourselves super experts on everything and never read the confirmation dialog. I myself don't remember the last time I have read any confirmation dialog seriously. Definitely this attitude must change while working on servers. When Sys Admin tried to delete the database, there was a confirmation that there are active connections on the database. SQL Server tried its best to inform him that this is a database being used and don't delete it, please. But as he does hundred times per day on his laptop, clicked OK without reading the confirmation dialog.

Don't put the same administrator password on all servers. This makes life easier while copying files from one server to another, but don't do it. You will accidentally delete a file on another server just like we used to do often.

DO NOT use Administrator user account to do your day to day work. We started using a Power User account for our day to day operation which has limited access on a couple of folders only. Using Administrator account on remote desktop means you are opening doors to all possible accidents to happen. If you use a restricted account, there's limited possibility of such accidents.

Always have someone beside you when you work on production server and do something important like cleaning up free space or running scripts, restoring database etc. Make sure the other person is not taking a nap on his chair beside you.

Support crew at hosting service formatted our running production server

We told the support technician to format Server A, he formatted Server B. Unfortunately Server B was our production database server which runs the whole site.

Fortunately, we had log shipping and there was a standby database server. We brought it online immediately, changed the connection string in all web.config and went live in 10 mins. We lost around 10 mins worth data as the last log ship from production database to standby database did not happen.

Windows got corrupted and was not working until we reinstalled

Web Server's Windows 2003 64bit got corrupted several times. Interestingly, the database servers never got corrupted. The corruption happened mostly on servers when we had no Firewall device and used Windows Firewall only. So, this must have something to do with external attacks. The corruption also happened when we were not installing patches regularly. Those Security Patches that you see Microsoft delivering every now and then are really important. If you don't install them timely, your OS will get corrupted for sure. Nowadays we can't run Windows 2003 64bit without SP2.

When the OS gets corrupted it behaves abnormally. You will see sometimes that it's not accepting inbound connections anymore. Sometimes you will see this error "An operation on a socket could not be performed because the system lacked sufficient buffer space or because a queue was full". Sometimes you will see login and logoff taking a lot of time. Sometimes Remote Desktop will stop working randomly. Sometimes you will see Explorer.exe and IIS process w3wp.exe crashing frequently. All these are good signs of the OS getting corrupted and time for patch installation.

We found that once the OS gets corrupted, there's no way you can install the latest patches and bring it back. At least for us, it rarely happened that installing some patch fixed the problem. 80% of the time we had to format and reinstall Windows and install the latest service pack and patches immediately. This always fixed such OS level issues.

Patch management is something you don't consider with high priority unless you start suffering from these problems frequently. First of all, you can never turn on Automatic Update and Install on production servers. If you do, Windows will download patches, install them and then restart itself. This means your site will go down unplanned. So, you will always have to manually install patches and bring out a server from Load Balancer, restart it and then put it back to Load Balancer.

DNS goes down

DNS providers sometimes do not have a reliable DNS server. We took hosting and DNS from GoDaddy.com. Their hosting is fine, but DNS hosting is crap. It went down 7 times so far. When DNS goes down, your site goes down as well for all new users and majority of the old users. When visitors enter www.pageflakes.com, the request first goes to the DNS server to get the IP of the domain. So, when the DNS server is down, IP is unavailable and the site becomes unreachable.

There are some professional DNS hosting companies which only do DNS hosting. For example, UltraDNS (www.ultradns.com), DNSPark (www.dnspark.com), Rackspace (www.rackspace.com). You should go for commercial DNS hosting instead of relying on Domain Registration companies to give you a complete package. However, UltraDNS turned out to be negative in DNSStuff.com test. It reported that their DNS hosting has a single point of failure which means both primary and secondary DNS were actually the same server. If that's really true, then it's very risky. So, when you take DNS hosting service, test their DNS servers using DNSStuff.com and ensure you get positive report on all aspects. Some things to ensure are:

  • Resolve IP of the primary and secondary DNS server. Make sure you get different IP
  • Ensure those different IP are actually different physical computer. I believe the only way to do it is to check with the service provider
  • Ensure DNS resolution takes less than 300ms. You can use external tools like DNSStuff.com to test it

Internet backbone goes down in different part of the world

Internet backbones connect the Internet of different countries together. They are the information superhighway that span the oceans connecting continents and countries. For example, UUNet is an Internet backbone that covers USA and also connects with other countries.

Screenshot - uunet.jpg

There are some other Internet backbone companies like British Telecom, AT&T, Sprint Nextel, France Télécom, Reliance Communications, VSNL, BSNL, Teleglobe (now a division of VSNL International), Flag Telecom (now a division of Reliance Communications), TeliaSonera, Qwest, Level 3 Communications, AOL, and SAVVIS.

All hosting companies in the world are either directly or indirectly connected to some Internet backbone. Some hosting providers have connectivity with multiple Internet backbones.

At an early stage, we used a cheap hosting provider which had connectivity with one Internet backbone only. One day, the connectivity between USA and London went down on a part of the backbone. London was the entry point to the whole of Europe. So, entire Europe and part of Asia could not reach our server in USA. This is a really rare bad luck. Our hosting provider happened to be on that segment of the backbone which was defective. As a result, all websites hosted by that hosting provider were unavailable for one day to Europe and some part of Asia.

So, when you choose a hosting provider, make sure they have connectivity with multiple backbones and do not share bandwidth with telecom companies and do not host online gaming servers.

Tracert can reveal important information about hosting provider's Internet backbone. The following figure shows very good connectivity between hosting provider and Internet backbone.

Screenshot - Tracert_to_carpathia.png

The tracert is taken from Bangladesh connecting to a server in Washington DC, USA. Some good characteristics about this tracert are:

  • Bangladesh and USA are in two parts of the world. Still there are only 9 hops which is very good. This means the hosting provider has chosen very good Internet backbone and has intelligent routing capability to decide the best hops between different countries.
  • There are only three hops from pccwbtn.net to the firewall. Also the delay between these hops is 1ms or less. This ensures they have very good connectivity with the Internet backbone.
  • There's only one Backbone Company, which is the pccwbtn.net. This means they have direct connection with the backbone and there's no intermediate connectivity.

The following figure shows an example of a bad hosting company with bad Internet connectivity.

Screenshot - bad_Tracert.png

Some bad characteristics about this tracert are:

  • Total 16 hops compared to 9. It also has 305ms latency compared to 266ms. So, the network connectivity of the hosting provider is bad.
  • There are two providers: pccwbtn.net and cogentco.com. This means the hosting provider does not have connectivity with tier-1 providers like pccwbtn.net. They go via another provider in order to save cost. Thus they introduce additional latency and point of failures.
  • Cogentco.com gave us trouble several times. We were using two hosting providers connected to cogentco.com and both of them had latency and intermittent connectivity problems.
  • There are four hops from the backbone to the web server. This means there are multiple gateways or firewall. Both are sign of poor network design.
  • There are too many hops on cogentco.com itself which is an indication of poor backbone connectivity. This means traffic is going between several networks in order to reach the destination web server.
  • Traffic goes through 5 different network segments 63.218.x.x, 130.117.x.x, 154.54.x.x, 38.20.x.x before reaching the final destination network XX.41.191.x. This is sign of poor routing capability within the backbone.

Choosing the right hosting provider

Our experience with several bad hosting companies gave us valuable lessons on choosing the right hosting company. We started with very cheap hosting providers and gradually went to one of the most expensive hosting providers in USA – Rackspace. Rackspace is insane when it comes to cost and good service quality. Their technicians are very well trained and their Managed Hosting plan offers onsite Sys Admin and DBA to take care of your servers and database. They can solve SQL Server 2005 issues as well as IIS related problems that we frequently had to solve ourselves. So, when you choose a hosting provider, make sure they have Windows 2003 and IIS 6.0 experts as well as SQL Server 2005 experts. While running production systems, there's always probability that you will fall into trouble which is beyond your capability. Having onsite skilled technicians is the only way for you to survive such disasters.

I have built a check list for choosing the right hosting provider from my experience:

  • Test ping time of a server in the same data center where you will get a server. Ping time is less than 40ms within USA and around 250ms average from several other countries including London, Singapore, Brazil, and Germany.
  • Ensure multiple backbone connectivity and intelligent routing capability that can choose the best hop from different countries. Do tracert from different countries and ensure the server is available within 10 to 14 hops from anywhere in the world. If your hosting provider is in USA, from anywhere in USA the server should be available within 5 to 8 hops.
  • Ensure there are only 3 hops from the Internet backbone to your server. You can verify this by checking the last 3 entries in tracert. The last entry should be your server IP and two entries back should be the Internet backbone provider. This ensures there's only one gateway between your server and the Internet backbone. If there's more hops, that means they have a complex network and you will waste latency within the internal network of your hosting provider.
  • 24x7 Phone support to expert technicians. Call them on a Weekend night and give them a complex technical problem. If the crew says he/she is only filling in for the real experts until they get back to office on Monday, discard them immediately. A good hosting provider will have expert technicians available at 3 AM on Saturday night. You will mostly need technicians on Saturday and Sunday late night in order to do maintenance and upgrade.
  • Live Chat support. This helps immensely when you are travelling and you cannot make a phone call.
  • You can customize your dedicated servers as you want. They do not limit you to predefined packages. This will give an indication that they have in-house technicians who can build and customize servers.
  • They can provide you all kinds of software including SQL Server Enterprise edition, Microsoft Exchange 2007, Windows 2003 R2, Windows Longhorn Server etc. If they can't, they do not have a good software vendor. Don't bet your future on them.
  • They must be able to provide you with 15K RPM SCSI drives and SAN (Storage Area Network). If they can't, don't consider them. You won't be able to grow your business with the provider if they don't have these capabilities.
  • Before you setup a whole data center with a hosting provider, make sure you get one server from them. While ordering the server, order something that you will need in 2 years from now on. See how fast and how reliably they can hand over a server like this to you. This will be an expensive experiment to do. But if you ever get into a bad hosting provider without doing this experiment, the time and money you will lose to get out of them is much more than the cost of the experiment.

Make sure their Service Level Agreement (SLA) ensures the following:

  • 99.99% network uptime. Deduction in monthly rent in case of outage in hour and number of occurrence unit.
  • Max 2 hours delay in hardware replacement for defective hard drives, network card, mother board equipment, controllers and other input devices
  • Full cooperation if you want to get out of their service and go somewhere else. Make sure they don't put you in trap for lifetime.
  • In case of service cancellation, you will get to delete all data stored in any storage and backup storages
  • Max 2 hours delay in responding to support tickets

This is not an exhaustive list. There can be many other scenarios where things can go wrong with hosting providers. But these are some of the common killer issues that you must try to prevent.

Web site monitoring tool

There are many online website monitoring tools that pings your servers from different locations and ensures the servers and network are performing well. These monitoring solutions have servers all around the world and in many different cities in USA. They scan your website or do some transaction to ensure the site is fully operational and critical functionalities are running fine.

We used www.websitepulse.com which is a perfect solution for our need. We have set up a monitoring system which completes the Welcome Wizard at Pageflakes and simulates a brand new user visit every 5 mins. It calls web services with the proper parameters and ensures the returned content is valid. This way we can ensure our site is running fine. We have also put a very expensive webservice call in the monitoring system in order to ensure the site performance is fine. This also gives us an indication whether the site has slowed down or not.

Screenshot - Websitepulse_Site_Scan.png

This figure shows the response time of the site for a whole day. You can see around 2:00 PM the site was down. It was getting timed out. Also from 11:00 AM to 3:00 PM there's high response time which means the site is getting big hit. All these give you valuable indications:

  • There might be some job running at 2:00 PM which produces very high response time. Possible suspect – database full backup.
  • There can be some job running at 11:30 AM which is causing high response time or there can be a traffic surge which is causing site wide slowdown.

Screenshot - Website_pulse_hit_details.png

Detail views show the total response time from each test, number of bytes downloaded and number of links checked. This monitor is configured to hit the homepage, find all links in it and hit those links. So, it basically gives the majority of the site one visit on every test and ensures that the most important pages are functional.

Screenshot - Websitepulse_Individual_Hits.png

Testing individual page performance is important to find out resource hungry pages. Figure 8-22 shows some slow performing pages. The "First" column shows the delay between establishing connection and getting the first byte of response. This means the time you see there is the time the server takes to execute the ASP.NET page on the server. So, when you see 3.38 sec, it means the server took 3.38 seconds to execute the page on server, which is very bad performance. Every hit to this page makes the server go high CPU and high disk IO. So, this page needs to be improved immediately.

Using such monitoring tools you can not only keep an eye on your sites 24x7 but also find out your site's performance at different times and see which pages are performing poorly.

Conclusion

Developing a mass consumer web site is a lot of fun. It's even more fun when it goes live on a massive scale and you start hitting production challenges that you never dreamt of. It's a completely different world out there with production systems compared to our development environment. So, being prepared for such production challenges helps the company prevent common disasters and keep up the user confidence in the long run.

13 disasters for production web sites and their solutions - The Code Project - Installation

Friday, August 17, 2007

Smokin' Aces: AMANGO.de DVD


Eine Million Dollar für das Herz von Kartenhai Buddy "Aces" Israel! Mit diesem Angebot lockt Mafia-Boss Primo Sparazza eine ganze Armada von Profikillern mit Kettensägen, Granaten und modernsten Feuerwaffen nach Lake Tahoe. Dort genießt "Aces", der gegen Sparazza aussagen will, seine FBI-Schutzhaft mit heißen Callgirls, Alkohol und Drogen im ...

read more | digg story

SQL Server 2005 secrets

There's plenty of hype about the new SQL Server 2005. Here's a list what's important about the pending release, and what you can plan on using SQL Server for in the near future.

read more | digg story

Sunday, August 5, 2007

Crosstab Pivot-table Workbench

Crosstab Pivot-table Workbench

There comes a time with many Database Developers charged with doing management reports when the process of doing it properly gets very tedious. By 'doing it properly', I mean the 'best practice' of having to do the basic reporting in SQL and relying on a front-end application to do the presentation. This is particularly true where the management want simple aggregate reports, or 'Pivot-table' reports. Presentation is so closely tied with the data that splitting the process can sometimes lead to more problems than it solves. Of course, we have Reporting Services, Analysis Services and other external tools, but there are times when a simple solution based in TSQL has the upper hand.

Anyone who was weaned on Excel knows that these pivot tables are dead simple. You select your data, bang the button and with a bit of dragging and dropping, there it is. Why, they ask, is it so hard to get it out of the database? Why so hard to make changes?

What they want to see is something like this (using NorthWind so those stuck with SQL 2000 can join in)

No. Sales per year

1996
1997
1998
Total

Margaret Peacock
31
81
44
156

Janet Leverling
18
71
38
127

Nancy Davolio
26
55
42
123

Laura Callahan
19
54
31
104

Andrew Fuller
16
41
39
96

Robert King
11
36
25
72

Michael Suyama
15
33
19
67

Anne Dodsworth
5
19
19
43

Steven Buchanan
11
18
13
42

Sum
152
408
270
830

Now, you'll notice that we've taken a bit of trouble to add some formatting. In the average business, they're fussy about such things as the alignment of numbers and the clear delineation of totals, and summary lines. It also is easier on the eye. It is therefore handy to format pivot table reports in HTML. We can send them via email them, straight from SQL Server, all in their correct formatting, or put the reports on an intranet and update them daily or hourly. With the contents of this workbench we show you how it is done, and how easy it is to do, all without a .NET programmer in sight!

However, first things first, we must first show you how to do a crosstab, or pivot table in Transact SQL

Crosstabs and Pivot tables

The basic code to do the report is pretty simple.

SELECT 
[No. Sales per year]=CASE WHEN row IS NULL THEN 'Sum' 
ELSE CONVERT(VARCHAR(80),[row]) END ,
[1996] =SUM( CASE col WHEN '1996' THEN data ELSE 0 END ),
[1997] =SUM( CASE col WHEN '1997' THEN data ELSE 0 END ),
[1998] =SUM( CASE col WHEN '1998' THEN data ELSE 0 END ),
[Total]= SUM( data )
FROM 
(SELECT [row]=firstname+' '+lastname, 
[col]=YEAR(OrderDate), 
[data]=COUNT(*)
FROM Employees INNER JOIN Orders 
ON (Employees.EmployeeID=Orders.EmployeeID) 
GROUP BY firstname+' '+lastname, YEAR(OrderDate)
    )f
GROUP BY row WITH ROLLUP
ORDER BY GROUPING(row),total DESC

You'll notice that the years are hard-coded into the column headings, which are a time bomb waiting to happen. You'll also realize that the all-important formatting is missing. The structure of the query seems slightly more complicated than necessary, but you'll see why soon. The PIVOT operator in SQL Server 2005 makes it rather easier but we wanted to make this relevant to the SQL Server 2000 users too

For any sort of portable solution that will work on SQL Server 2000, dynamic SQL is the traditional solution. Basically, the stored procedure generates the tedious code and then executes it. Why, one wonders. This is because there are a number of tweaks that have to be made, such as the order of the columns, and rows. After all, wouldn't someone want the report ordered by the number of sales of the salesman rather than just alphabetic order? The same basic query may generate a lot of different aggregations. Pretty soon, some sort of automation will be required.

Keith Fletcher contributed to Simple-Talk the ingenious but complex stored procedure that did cross-tabs in his excellent article Creating cross tab queries and pivot tables in SQL. Because we were awed by its grandeur, we didn't initially want to add our own contribution. However, we had two another objectives, firstly to show how easy the technique can be, and also because we wanted to do more, encouraging you to try things out, and secondly, because we wanted to show how one might mark up the presentation of the crosstab.

Here is a stored procedure that does the trick, along with some examples using NorthWind. (if you are stuck with SQL Server 2000, make the Varchar(MAX)s into Varchar(8000) and don't be too ambitious with the complexity of your crosstabs!)

-------------------------------------------------------------------------
CREATE PROCEDURE spDynamicCrossTab
@RowValue VARCHAR(255),         --what is the SQL for the row title 
@ColValue VARCHAR(255),         --what is the SQL for the column title
@Aggregate VARCHAR(255),        --the aggregation value to go in the cells
@FromExpression VARCHAR(8000),              --the FROM, ON and WHERE clause
@colOrderValue VARCHAR (255)=NULL,            --how the columns are ordered
@Title VARCHAR(80)='_',    --the title to put in the first col of first row
@SortBy VARCHAR(255)='row asc', --what you sort the rows by (column heading)
@RowSort VARCHAR(80)=NULL,
@ReturnTheDDL INT=0,--return the SQL code rather than execute it
@Debugging INT=0    --debugging mode
/*
e.g.
Execute spDynamicCrossTab
    @RowValue='firstname+'' ''+lastname',
    @ColValue='Year(OrderDate)',
    @Aggregate= 'count(*)',
    @FromExpression='FROM Employees INNER JOIN Orders 
    ON (Employees.EmployeeID=Orders.EmployeeID)',
    @ColOrderValue='Year(OrderDate)',
   @Title ='No. Sales per year',
   @SortBy ='total desc' --what you sort the rows by (column heading)
Execute spDynamicCrossTab
    @RowValue='firstname+'' ''+lastname',
    @ColValue='DATENAME(month,orderDate)',
    @Aggregate= 'sum(subtotal)',
    @FromExpression='FROM Orders 
   INNER JOIN "Order Subtotals" 
       ON Orders.OrderID = "Order Subtotals".OrderID
   inner join employees on employees.EmployeeID =orders.EmployeeID',
    @ColOrderValue='datepart(month,orderDate)',
   @Title ='Customers orders per month '
EXECUTE spDynamicCrossTab 
    @RowValue='country',
    @ColValue='datename(quarter,orderdate)
     +case datepart(quarter,orderdate) 
         when 1 then ''st'' 
         when 2 then ''nd'' 
         when 3 then ''rd'' 
         when 4 then ''th'' end',
    @Aggregate= 'sum(subtotal)',
    @FromExpression='FROM Orders 
   INNER JOIN "Order Subtotals" 
       ON Orders.OrderID = "Order Subtotals".OrderID
  inner join customers on customers.customerID =orders.customerID',
    @ColOrderValue='datepart(quarter,orderDate)',
   @sortby='total desc',
   @Title ='value of orders per quarter'
*/
AS
SET nocount ON
DECLARE @Command NVARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)
--make sure we have sensible defaults for orders
SELECT @ColOrderValue=COALESCE(@ColOrderValue, @ColValue),
@Sortby=COALESCE(@SortBy,@RowValue),
@rowsort=COALESCE(@RowSort,@RowValue)
--first construct tha SQL which is used to calculate the columns in a 
--string
SELECT @Command='select @SQL=coalesce(@SQL,''SELECT 
  ['+@Title+']=case when row is null then ''''Sum'''' 
else convert(Varchar(80),[row]) end ,
'')+
  ''[''+convert(varchar(100),'
+@ColValue+')+''] =sum( CASE col WHEN ''''''+convert(varchar(100),'
+@ColValue+')+'''''' THEN data else 0 END ),
'' '+@FromExpression+'
GROUP BY '+@ColValue+'
order by max('+@ColorderValue+')'
--Now we execute the string to obtain the SQL that we will use for the
--crosstab query
EXECUTE sp_ExecuteSQL @command,N'@SQL VARCHAR(MAX) OUTPUT',@SQL OUTPUT
IF @@error > 0 --display the string if there is an error
BEGIN
      RAISERROR ( 'offending code was ...%s', 0, 1, @command )
RETURN 1
END
IF @debugging <>0 SELECT @Command
--we now add the rest of the SQL into the string
SELECT @SQL=@SQL+'  [Total]= sum( data )
from 
   (select [row]='+@RowValue+', 
           [col]='+@ColValue+', 
           [data]='+@Aggregate+',
           [sort]=max('+@rowsort+')
 '+@FromExpression+' 
    GROUP BY '+@RowValue+', '+@ColValue+'
)f
group by row with rollup
order by grouping(row),'+@Sortby
--and execute it
IF @ReturnTheDDL<>0 SELECT @SQL ELSE EXECUTE (@SQL)
IF @@error > 0 
BEGIN
      RAISERROR ( 'offending code was ...%s', 0, 1, @sql )
RETURN 1
END

You'll see that this is a developer's tool. It is easy to crash the procedure by putting in bad SQL. SQL Injectors would love it. No sir, this is a back-office report-generating tool. Let's try it out

EXECUTE spDynamicCrossTab
@RowValue='ProductName',
@ColValue='Year(OrderDate)',
@Aggregate= 'ROUND(SUM(CONVERT(decimal(14, 2), OD.Quantity 
* ( 1 - OD.Discount ) * OD.UnitPrice)),0)',
@FromExpression='FROM    [Order Details] OD,
        Orders O,
        Products P,
        Categories C
where   OD.OrderID = O.OrderID 
AND OD.ProductID = P.ProductID 
AND P.CategoryID = C.CategoryID',
@Title ='Customers total orders per year'
-- change the line...     @RowValue='ProductName', 
--             ...to     @RowValue='CategoryName',
-- and see what happens!
/*-------------------------------------------------------------------

*/
--
-- add the row ...
-- @SortBy ='total desc', --what you sort the rows by (column heading)
-- before the @Title ='Customers total orders per year'-- Neat Huh?
--now change 
--    @RowValue='CategoryName',
--    @ColValue='Year(OrderDate)',
--to
--    @ColValue='CategoryName',
--    @RowValue='Year(OrderDate)',
--Instant Rotation!
--Now try this, and notice how we get the columns and rows in the right order
EXECUTE spDynamicCrossTab
@colValue='DATENAME(year,orderDate)',
@rowValue='DATENAME(month,orderDate)',
@Aggregate= 'sum(subtotal)',
@Rowsort='DATEpart(month,orderDate)',
@FromExpression='FROM Orders 
   INNER JOIN "Order Subtotals" 
       ON Orders.OrderID = "Order Subtotals".OrderID
   inner join employees on employees.EmployeeID =orders.EmployeeID',
@ColOrderValue='datepart(year,orderDate)',
@Title ='Customers orders per month ',
@sortby='max(sort) asc'
/*-------------------------------------------------------------------

HTML Crosstabs

Why bother with this when the Internet abounds with such Cross tab or pivot-table procedures? This is because we are going to take it one stage further so that, instead of just producing a resultset, we want to show how to produce HTML to produce the chart like the one at the beginning of the article.

To reproduce this...

value of orders per quarter

1st
2nd
3rd
4th
Total

USA
$81364.94
$50525.40
$58047.56
$55646.73
$245584.63

Germany
$66823.20
$64681.22
$42550.91
$56229.29
$230284.62

Austria
$32357.82
$37346.79
$17383.60
$40915.63
$128003.84

Brazil
$47027.15
$12127.25
$22537.77
$25233.60
$106925.77

France
$31085.27
$11225.97
$14407.11
$24639.96
$81358.31

UK
$21302.05
$17061.85
$2292.70
$18314.72
$58971.32

Venezuela
$21186.40
$11991.60
$12098.75
$11533.89
$56810.64

Sweden
$13627.62
$18234.86
$8718.31
$13914.35
$54495.14

Canada
$22746.94
$7094.48
$9225.70
$11129.18
$50196.30

Ireland
$20500.54
$8291.50
$11376.50
$9811.36
$49979.90

Belgium
$19410.67
$3728.48
$7740.70
$2945.00
$33824.85

Denmark
$17049.90
$2007.79
$2127.25
$11476.08
$32661.02

Switzerland
$4454.02
$10928.70
$7714.06
$8595.88
$31692.66

Mexico
$3938.00
$12432.71
$4616.17
$2595.20
$23582.08

Finland
$5532.80
$5791.20
$2884.41
$4601.64
$18810.05

Spain
$7329.30
$1875.80
$4633.25
$4144.85
$17983.20

Italy
$7082.09
$2836.10
$2484.37
$3367.60
$15770.16

Portugal
$3335.79
$4311.20
$1519.24
$2306.14
$11472.37

Argentina
$6684.10
$716.50
$0.00
$718.50
$8119.10

Norway
$3354.40
$822.35
$500.00
$1058.40
$5735.15

Poland
$587.50
$1277.60
$808.00
$858.85
$3531.95

Sum
$436780.50
$285309.35
$233666.36
$310036.85
$1265793.06

...We need code like this (the first example in the body of the code was used to generate this Pivot-table)…

-------------------------------------------------------------------------
CREATE PROCEDURE spDynamicHTMLCrossTab
@RowValue VARCHAR(255), --what is the row header
@ColValue VARCHAR(255), --what is the column header
@Aggregate VARCHAR(255), --the aggregation value
@FromExpression VARCHAR(8000), --the FROM, ON and WHERE clause
@colOrderValue VARCHAR (255)=NULL, --how the columns are ordered
@Title VARCHAR(80)='_', --the title to put in the first col of first row
@RowSort VARCHAR(80)=NULL,--any special way the rows should be sorted
@SortBy VARCHAR(255)='row asc', --what you sort the rows by (column heading)
@UnitBefore VARCHAR(10)='',--the unit that each value has before (e.g. £ or $)
@UnitAfter VARCHAR(10)='',--The unit that each value has after e.g. %
@ReturnTheDDL INT=0,--we return just the DLL
@Debugging INT=0,--we look at the intermediate code
@output VARCHAR(MAX) ='none' output,
@style VARCHAR(MAX)='<style type="text/css">
/*<![CDATA[*/
<!--
#MyCrosstab {
font-family: Arial, Helvetica, sans-serif; font-size:small;
}
#MyCrosstab td{font-size:small; padding: 3px 10px 2px 10px; }
#MyCrosstab td.number{ text-align: right; }
#MyCrosstab td.rowhead{ border-right: 1px dotted #828282; font-weight: bold;}
#MyCrosstab th{ font-size:small; border-bottom: 1px dotted #828282; text-align: center; }
#MyCrosstab .sum{ border-top: 2px solid #828282; }
#MyCrosstab .sumrow{ text-align: right }
#MyCrosstab .total{ border-left: 1px solid #828282; }
-->
/*]]>*/
</style>
'
/*
Declare @HTMLString varchar(max) 
EXECUTE spDynamicHTMLCrossTab 
    @RowValue='CompanyName',
    @ColValue='datename(quarter,orderdate)
     +case datepart(quarter,orderdate) 
         when 1 then ''st'' 
         when 2 then ''nd'' 
         when 3 then ''rd'' 
         when 4 then ''th'' end',
    @Aggregate= 'sum(subtotal)',
    @FromExpression='FROM Orders 
   INNER JOIN "Order Subtotals" 
       ON Orders.OrderID = "Order Subtotals".OrderID
  inner join customers on customers.customerID =orders.customerID',
    @ColOrderValue='datepart(quarter,orderDate)',
   @Unitbefore='$',
   @sortby='total desc',
   @Title ='value of orders per quarter',
   @Output=@HTMLString output
Select @HTMLString
Execute spDynamicHTMLCrossTab 
    @RowValue='firstname+'' ''+lastname', 
    @ColValue='DATENAME(year,orderDate)', 
    @Aggregate= 'sum(subtotal)', 
    @FromExpression='FROM Orders  
   INNER JOIN "Order Subtotals"  
       ON Orders.OrderID = "Order Subtotals".OrderID 
   inner join employees on employees.EmployeeID =orders.EmployeeID', 
    @ColOrderValue='datepart(year,orderDate)', 
   @Unitbefore='$',
   @sortby='total desc',
   @Title ='Revenue per salesman per year '
Execute spDynamicHTMLCrossTab
    @RowValue='firstname+'' ''+lastname',
    @ColValue='Year(OrderDate)',
    @Aggregate= 'count(*)',
    @FromExpression='FROM Employees INNER JOIN Orders 
    ON (Employees.EmployeeID=Orders.EmployeeID)',
    @ColOrderValue='Year(OrderDate)',
   @Title ='No. Sales per year',
   @SortBy ='total desc', --what you sort the rows by (column heading)
    @ReturnTheDDL =0,
    @debugging=0
*/
AS
SET nocount ON
DECLARE @Command NVARCHAR(MAX)
DECLARE @DataRows VARCHAR(MAX)
DECLARE @HeadingLines VARCHAR(8000)
--make sure we have sensible defaults for orders
SELECT @ColOrderValue=COALESCE(@ColOrderValue, @ColValue),
@rowsort=COALESCE(@RowSort,@RowValue),
@Sortby=COALESCE(@SortBy,@RowValue)
--first construct tha SQL which is used to calculate the columns in a 
--string
DECLARE @StringTable TABLE
(
MyID INT IDENTITY(1, 1),
string VARCHAR(8000),
waste numeric(19,8)
  )
SELECT 
@Command='Select 
 @Headinglines=coalesce(@headinglines,''<div id="MyCrosstab">
<h3>'+@title+'</h3>
<table cellpadding="0" cellspacing="0">
<thead>
<tr class="header"><th> </th>'')+''<th>''
+convert(varchar(100),'
+@ColValue+') +''</th>'',
 @DataRows=coalesce(@DataRows,
''SELECT 
[string]=''''<tr>
  <td class="rowhead''''
+ case when grouping(row)<>0 then'''' sumrow'''' else '''''''' end
+''''">''''+convert(varchar(100),case when row is null 
then ''''Sum'''' else [row] end)+''''</td>
'')
 +''<td class="''''
+ case when grouping(row)<>0 then''''sum'''' else '''''''' end
+'''' number">''''+'''''+@unitBefore+'''''+convert(varchar(100),sum( CASE col WHEN ''''''
 +convert(varchar(100),'
+@ColValue+')
 +'''''' THEN data else 0 END ))++'''''+@unitAfter+'''''+''''</td>
''  '+@FromExpression+'
GROUP BY '+@ColValue+'
order by max('+@ColorderValue+')'
--Now we execute the string to obtain the SQL that we will use for the
--crosstab query
EXECUTE sp_ExecuteSQL @command,N'@DataRows VARCHAR(MAX) OUTPUT,
  @Headinglines VARCHAR(MAX) OUTPUT', @DataRows output,@Headinglines OUTPUT
IF @@error > 0 --display the string if there is an error
BEGIN
      RAISERROR ( 'offending first-phase code was ...%s', 0, 1, @command )
RETURN 1
END
IF @Debugging <>0 SELECT @Command
INSERT INTO  @StringTable(string) SELECT @Style
INSERT INTO  @StringTable(string) SELECT @Headinglines+'<th>Total</th></tr>
   </thead>
   <tbody>'
SELECT @DataRows=
@DataRows+'<td class="''
  + case when grouping(row)<>0 then''sum'' else '''' end+'' number total">''
  +'''+@unitBefore+'''+convert(varchar(100),sum( data ))+'''+@unitAfter
+'''+''</td></tr>'', [total]=convert(numeric(19,8),sum( data ))
from 
   (select [row]='+@RowValue+', 
           [col]='+@ColValue+', 
           [data]='+@Aggregate+',
           [sort]=max('+@rowsort+')
 '+@FromExpression+' 
    GROUP BY '+@RowValue+', '+@ColValue+'
)f
group by row with rollup
order by grouping(row),'+@Sortby
--and execute it
IF @ReturnTheDDL<>0 SELECT @DataRows ELSE
   INSERT INTO  @StringTable(string,waste)
EXECUTE (@DataRows)
IF @@error > 0 
BEGIN
      RAISERROR ( 'offending second-phase code was ...%s', 0, 1, @DataRows )
RETURN 1
END
INSERT INTO  @StringTable(string) SELECT '</tbody></table></div>'
IF @Output='none' 
SELECT string FROM @StringTable ORDER BY MyID
ELSE 
    SELECT @Output=COALESCE(@Output,'')+ string 
FROM @StringTable 
ORDER BY MyID

(n.b this is the SQL Server 2005 version. The SQL Server 2000 version is included with the files you can download in the speech bubble at the top of the article)

You will have noticed a few things here.

  • The inputs are the same as the first stored procedure, spDynamicCrossTab. This means that you can try out your parameters in SSMS until you have things the way you want them and then you can just move to spDynamicHTMLCrossTab to concentrate on getting the presentation aspects as you want.
  • We have separated the style from the code. All presentation is in an inline style block. This means you can change the way the crosstab looks to your heart's content.
  • with both stored procedures, you can specify the order of both the columns and rows precisely (it is always nice to have the months, or the days of the week in the correct order!
  • you can specify the units, either before(e.g. '£' or '$') or after (e.g '%') the aggregate values
  • We provide you with an optional output variable so you can take the results and save it easily to a file, using the technique Phil described in his Blog 'Using BCP to export the contents of MAX datatypes to a file'

You can change the appearance of the crosstab simply by changing the inline style. For example, this ...

EXECUTE spDynamicHTMLCrossTab
@RowValue='firstname+'' ''+lastname',
@ColValue='Year(OrderDate)',
@Aggregate= 'count(*)',
@FromExpression='FROM Employees INNER JOIN Orders 
    ON (Employees.EmployeeID=Orders.EmployeeID)',
@ColOrderValue='Year(OrderDate)',
@Title ='No. Sales per year',
@SortBy ='total desc', --what you sort the rows by (column heading)
@Style='<style type="text/css">
/*<![CDATA[*/
<!--
#MyCrosstab {
font-family: "Times New Roman", Times, serif; font-size:small;
}
#MyCrosstab td{font-size:small; padding: 3px 10px 2px 10px; }
#MyCrosstab td.number{ text-align: right; }
#MyCrosstab td.rowhead{ background-color: #C5DC9C; font-weight: bold;}
#MyCrosstab th{ background-color: #C5DC9C; font-size: small;  border-bottom: text-align: center; }
#MyCrosstab .sum{ border-top: 3px double #828282; }
#MyCrosstab .sumrow{ border-top: 1px solid #828282; text-align: right }
#MyCrosstab .total{ border-left: 1px solid #828282; }
-->
/*]]>*/
</style>' 

... will give you this

No. Sales per year

1996
1997
1998
Total

Margaret Peacock
31
81
44
156

Janet Leverling
18
71
38
127

Nancy Davolio
26
55
42
123

Laura Callahan
19
54
31
104

Andrew Fuller
16
41
39
96

Robert King
11
36
25
72

Michael Suyama
15
33
19
67

Anne Dodsworth
5
19
19
43

Steven Buchanan
11
18
13
42

Sum
152
408
270
830

Lastly, although there is a lot more one can say about these procedures and the tricks one can use, especially with the CSS, here is an illustration of the way one might save the results of your crosstab to an HTML file for your management reporting intranet site. This uses Phil's technique taken from his blog entry Using BCP to export the contents of MAX datatypes to a file (Phil: Thanks for the plug, Robyn!)


DECLARE @HTMLString VARCHAR(MAX) 
EXECUTE spDynamicHTMLCrossTab
@colValue='DATENAME(year,orderDate)',
@rowValue='DATENAME(month,orderDate)',
@Aggregate= 'sum(subtotal)',
@Rowsort='DATEpart(month,orderDate)',
@FromExpression='FROM Orders 
   INNER JOIN "Order Subtotals" 
       ON Orders.OrderID = "Order Subtotals".OrderID
   inner join employees on employees.EmployeeID =orders.EmployeeID',
@ColOrderValue='datepart(year,orderDate)',
@Title ='Customers orders per month ',
@sortby='max(sort) asc',
@Output=@HTMLString output
SELECT @HTMLString=
'<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
                           "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head><title>Customers orders per month</title></head>
<body>'+@HTMLString+'</body>'
EXECUTE spSaveTextToFile @HTMLString, 'C:\MyHTMLReport.html'

So that's it. We've enjoyed ourselves trying things out, and we've been surprised how far we can take the dynamic creation of pivot tables. We suggest you take the workshop and explore the ideas. If you discover anything interesting, we'd love to hear your comments! We'd particularly like to hear of interesting CSS layouts, though displaying them on Simple-Talk will be very difficult.

© Simple-Talk.com

Top 10 Firefox features that don't require extensions

Today, rather than pointing you toward a handful of extensions designed to boost your productivity, we're highlighting the top 10 productivity-boositng Firefox features and that don't require an extension.

read more | digg story