Report Optimization/Tuning

I have been assigned a task to speed up a slow report. This report actually has 6 different datasets and each calls a different store procedure. My job is to tune it to speed it up. The first step I did is to find out how long it may take to execute the report and associated store procedure to produce the results.

Goal for 2011

Currently, the following ideas are floating in my mind:

1. Write at least one quality blog each month

2. Certification on .NET language

3. Own projects need to kick off

4. Contribute more to the Sql Server user Forum

to be added…

Dynamic connection manager in SSIS package, Use DelayValidation = TRUE on pkg level.

Since the connection manager is using the expression to make it flexible, the dataflow will always show the error icon. The tip is to turn the DelayedValidation to TRUE on the package level. In this way, the validation package won’t kick in until the run time.

unicode and non-unicode data type in sql server 2008

nchar, nvarchar

char, varchar

 

Basically unicode data type requires a bit more space to store than non-unicode datatype. however, it could save you tons of time when it comes to the data type conversion. From the perspective of SSIS ETL processes, lots of source data are in unicode format (excel , dbf) and it would get into the unicode datatype columns a lot easiler than the non-unicode ones.

Can not open a report in the BIDS designer 2008

I am trying to open a report in the BIDS designer. However, it failed to open it and the error xml message is as follows

... Deserialization failed: The 'DataType' attribute is not declared. Line 3102, position 20.....

I’ve posted it to the SSRS social group and also googled around. It turns out as a bug inside VS2008 and the workaround is to manually edit the report xml and strip off all DataType="Integer” inside the <parameterValues> tag.

 

Cheers