Stumbled upon decision to use either cross tab or pivot in SQL Server

Here is what it is..

Create a set of views which uses pivot using SQL Server 2008 express. All goes fine if the record count is in 1000's .... Testing with 20,000 found no issues what so ever with performance.

Now dealing with a million records, struggling with performance of 4 min's for simple query run.

Googling about this, have found a nice article

Cross Tabs and Pivots

As the article states using cross tab would give better performance than pivot, happy to accept that but this too was taking 2 to 3 min's.

So now changed the design, to remove pivot and an intermediate table to have the results.

I wish life had an Undo function. ~Author