SSRS 2008 slow preview in Visual Studio 2008 when using expressions in groups

Question

When creating a matrix report in Visual Studio 2008 that uses an expression like the one below, the preview takes much longer than it should do.

=Fields(Parameters!ColumnGroupParam.Value).Value

If the same report is either deployed to the report server and viewed within the report manager or the report is designed using Report Builder rather than Visual Studio, there are no issues and the report generates quickly.

When looking at the results from SQL Server Profiler the query is taking much longer to return the result set when it is queried from Visual Studio.

Report Manager: Duration = 1885

Visual Studio: Duration = 327946 (173x longer!)

In general, query execution appears to be slower when done from Visual Studio even without the expressions used for groups. I had the same behaviour when trying this out in SQL Server 2008 R2 and SQL Server 2012.

Has anyone else come across this behaviour before and found a suitable fix?

Steps to reproduce:

Using AdventureWorksDW2008 database create a matrix report within Visual Studio 2008 using the following query as the dataset:

SELECT
PC.EnglishProductCategoryName AS Category
,PSC.EnglishProductSubcategoryName AS Subcategory
,P.EnglishProductName AS ProductName
,G.EnglishCountryRegionName AS Country
,G.StateProvinceName AS State
,FIS.SalesAmount
FROM        dbo.FactInternetSales FIS
INNER JOIN dbo.DimProduct P
ON FIS.ProductKey = P.ProductKey
INNER JOIN dbo.DimProductSubcategory PSC
ON P.ProductSubcategoryKey = PSC.ProductSubcategoryKey
INNER JOIN dbo.DimProductCategory PC
ON PSC.ProductCategoryKey = PC.ProductCategoryKey
INNER JOIN dbo.DimCustomer C
ON FIS.CustomerKey = C.CustomerKey
INNER JOIN dbo.DimGeography G
ON C.GeographyKey = G.GeographyKey

Create 2 parameters RowGroupParam and ColumnGroupParam both with available values Category, Subcategory, ProductName.

Create a row group using the expression =Fields(Parameters!RowGroupParam.Value).Value and column group using the expression =Fields(Parameters!ColumnGroupParam.Value).Value. Use the same expressions for the textbox values for the row and column group.

Use SalesAmount for the detail textbox.

Run the report selecting Category for the row group parameter and Subcategory for the column group category. Wait 30 seconds for the report to generate.

Deploy the same report to the report server and run the report with the same parameters. The report will generate in a few seconds.

YvesR YvesR · Accepted Answer · 2012-09-10T08:40:48

I can't give you a 100% answer, just my expirience. As the report service builds its own internal record set it fetches all the data and create a new virtual recordset (table). This works, as you mentioned, slower then executing from a sql query directly. So what I do to prevent this: Always build queries and store them inside ms sql to perform all the work there. In the report itself I access the results of the query only.

E.g. SELECT * FROM qry_verycomplicatedquery which is stored in ms sql as query.

Maybe someone else knows a better way, but this way is imho easier to handle later anyway to administratote lots of reports using the same data source.

SSRS 2008 slow preview in Visual Studio 2008 when using expressions in groups

1 Answers