Performance Improvement on Recursive CTE

One big finding for today is that when using recursive CTE, the leaner the recursive member table is the better. This basically means getting only the fields that are necessary for the recursive CTE. Any other fields such as Tile, Description are to be pulled after the recursive CTE. It also means doing the recursive as early as possible in the big overall query.

This practise brought a SQL query running time from 30 seconds to 1 second. Huge, huge difference!

Convert UTC Time to Local Time

To convert a UTC time to local time, we first find out the difference between the UTC and local time by getting the current UTC time and local time.

DECLARE @UTCtoLocalTime AS INT
Set @UTCtoLocalTime = DATEDIFF(hour, GETUTCDATE(), GETDATE())

Then we just need to add the difference back to the UTC time to get the local time.
DateAdd(hour, @UTCtoLocalTime, [UTC TIME])

The benifit of this approach is that the US daylight saving is considered.

Multi-Selection Parameter Reset to Blank

I encountered a situation where a multi-selection parameter is reset to blank sometimes by the Reporting Services.

Here is the reason that the Feature parameter was reset to blank or giving out error message:

Somehow the Reporting Service re-evaluates and invalidates the parameter values when a parameter is dynamically generated based on other parameter. In our case, Feature list is dynamically generated based on Primary Workstream selection. The work around is to use a table variable instead of straight SQL query to generate the dataset that populates the parameter values. For some reason the Reporting Services doesn’t re-evaluate the values if a table variable is used.

After some digging, I found a work around:
http://stackoverflow.com/questions/684250/ssrs-asp-net-reportviewer-parameters-reset-to-default-when-clicking-view-report

The following query is used:

DECLARE @FeatureList TABLE (Feature NVARCHAR(256))
INSERT @FeatureList
SELECT DISTINCT Cobalt_Common_Feature AS Feature FROM dbo.[Work Item]
WHERE Cobalt_Common_Feature IS NOT NULL
AND (@PrimaryWorkStream='_All Workstreams' OR Cobalt_Common_PrimaryWorkstream=@PrimaryWorkStream)
UNION SELECT 'ZZZ Unknown' AS Feature

SELECT * FROM @FeatureList ORDER BY Feature

How to create new SQL Server Instance

1. Run the full setup.exe. Ignore the version warning message is Service Pack is installed.
2. Add a new DB instance there
3. Upgrade to the current SP version

T-SQL Pivot / UnPivot Command

This is a very useful and efficient command for pivoting data.

E.g.

select * from dbo.tbl_BuildInformationField
pivot (
min(FieldValue)
for
FieldName in ([Name],[Message],StartTime,FinishTime)
) as p
where name = 'FirstTarget'

More information at:
http://www.unboxedsolutions.com/sean/archive/2004/08/30/302.aspx
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData

Also UNPIVOT is equally handy in certain situations:
http://msdn.microsoft.com/en-us/library/ms177410.aspx

Here is how to unpivot multiple columns:
http://pratchev.blogspot.com/2009/02/unpivoting-multiple-columns.html

Take Advantage of the Calcuated Field

Get most of the detail level calculation done using the Calcuated Field helps simplify the Layout design.

Fixing The Custom MDX Lost in Report Designer Problem

Recently I encountered a problem with the SSRS 2005 Report Designer. When I opened a report with custom MDX code and go the data tab. The dataset view screen automatically went to the design view and wiped out the custom MDX code. The problem started occuring when I uninstalled SQL 2005 Express edition and installed the Enterprise edition. I found a post on the Internet that talks about similar situation. (http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/fca63464-cffb-4208-99e4-c0a53c3686a0/)

Because of the issue mentioned in the post, the Report Designer couldn't parse the MDX code. It automatically went back to the design mode when that happends because that's the "Previouse State" of the data set.

Installing SQL Server 2005 SP2 fixed this problem.