次の方法で共有

Possible performance implications when using string parameters in Reporting Services

  • [アーティクル]

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

Comments

  • Anonymous
    June 20, 2009
    This is one of the reasons I prefer to create any Data Mart using only Unicode types. The other one is very similar and explained in this old post: http://sqlblog.com/blogs/marco_russo/archive/2006/10/21/unicode-varchar-nvarchar-and-index-usage-in-sql-server.aspx

  • Anonymous
    June 29, 2009
    While using a Data Mart is a good practice indeed, it is not always possible due to the size of the implementation at a customer location. So while ensuring the data-types match is a good practice in SQL Server, the workarounds could help in scenarios where that is not feasible/possible. This behavior is not new and has been the case since Reporting Services 2000 but folks still often run into this. Good to know it was blogged about in your blog :)