Possible performance implications when using string parameters in Reporting Services

  • Article

In Reporting Services String Parameters are of type Unicode. This could have unwanted side effects if comparing with a varchar ( or non-unicode data type) on the data source due to conversions that could occur from the non-Unicode column to the Unicode column as the resulting query plans may use scan’s instead of seeks. This is applicable to both SQL 2005 and SQL 2008.

 

Let us consider the following table which the report is based on

 

Create table TempProducts

(

  ProductID int Primary Key,

  [Name] varchar(100),

  ProductNum varchar(20),

  StockLevel int,

  ListPrice float,

)

Go

Create index IndProductName on TempProducts([Name])

go

 

Let’s consider a Dataset with a query below, where @Products is a String Multi-value parameter in Reporting Services.

 

select Name from TempProducts

where Name in (@Products)

 

When the report is run, and say we select 2 values, these are passed as Nvarchar back to the Engine as Reporting Services treats the string parameters are Unicode. If you run a profiler on the backend, you will see the query below passing Nvarchar values in the in clause.

 

select Name from TempProducts

where Name in (N'Half-Finger Gloves, M',N'Full-Finger Gloves, M')

 

If you look at the plan, it is doing an Index Scan though we have an index on the Name column

 

StmtText

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  |--Index Scan(OBJECT:([AdventureWorks].[dbo].[TempProducts].[IndProductName]), WHERE:(CONVERT_IMPLICIT(nvarchar(100),[AdventureWorks].[dbo].[TempProducts].[Name],0)=N'Full-Finger Gloves, M' OR CONVERT_IMPLICIT(nvarchar(100),[AdventureWorks].[dbo].[TempP

 

The scan is chosen due to the Data-type precedence (https://msdn.microsoft.com/en-us/library/ms190309.aspx ) , when doing the comparison between a varchar and an Nvarchar data-type, the varchar has to be converted into Nvarchar ( data type lower on the precedence has to be converted into the data type which is higher on the precedence scale). Due to this conversion, we could get a plan that has a “Scan” rather than seek

 

This is not necessarily a problem for small tables or datasets, but could pose a larger problem for large tables where we opt for a scan instead of a seek.

 

You have several approaches to work-around this if you deem the time taken by the dataset or query to be your bottleneck. The key is here to ensure before making a change that time to retrieve the data is your problem.

 

a. Use a dynamic Dataset

 

Basically what you are doing here in the dataset in reporting services is converting the values to literal values before passing it to the Database and building the query at runtime as an expression.

="select b.Prodname,a.* from ProductOrders a inner join testproduct b on a.ProductID = b.id and b.Prodname in ("

& "'" & Join(Parameters!Products.Value,"','") & "'

If you run a profiler, this now gets passed as:

               select Name from TempProducts where Name in ('Half-Finger Gloves, M','Full-Finger Gloves, M')

The resultant plan is:

 

StmtText

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

|--Index Seek(OBJECT:([AdventureWorks].[dbo].[TempProducts].[IndProductName]), SEEK:([AdventureWorks].[dbo].[TempProducts].[Name]='Full-Finger Gloves, M' OR [AdventureWorks].[dbo].[TempProducts].[Name]='Half-Finger Gloves, M') ORDERED FORWARD)

 

b. Use a Stored procedure

 

In case of a Single value Parameter, you can create a procedure that takes as input a varchar parameter instead of an nvarchar and hence the conversion is done prior to actually running the select statement. In the case of a multi-value parameter you can have a proc take a comma delimited string and then construct the where clause.

 

c. Convert the underlying data-type:  

 

Of course you won’t convert the underlying column’s data-type just because a single report possibly runs into this problem, rather would convert it to Unicode if that column would merit such a conversion based on the application in question.

 

-Denzil Ribeiro, SQL Dedicated Premier Field Engineer