none
Reporting Services Unicode Parameters Cause Performance Issues RRS feed

  • Question

  • When I create a report using string parameters,  reporting services sends the SQL to SQL Server with an N prefix on the string parameters.  This is the behavior even when the underlying data table has no unicode datatypes.  This causes SQL Server to do a scan instead of a seek on these queries.  Can this behavior be modified to send the parameters as non-unicode text?
    Monday, May 1, 2006 1:31 PM

All replies

  • Work around to overcome SSRS report performance due to UNICODE conversion issue:

    I have used a new parameter (of type Internal) which collects/duplicates the original parameter values as comma separated in string.
    In the report Dataset query, parse the comma separated string into  a list into a vairable table using XML trick.
    Use the variable table in WHERE IN clause

    Steps:
    Create a new Internal parameter (call it InternalParameter1)
    Under Default Values -> Specify values : Add Exp : =join( Parameters!OrigParameter1.Value,",")
    Pass/Use the InternalParameter1 in your dataset query.

    
    /*
    Example code  
    DECLARE @InternalParameter1 NVARCHAR(MAX)
    SET @InternalParameter1 = '100167600,
    100167601,
    4302853605,
    4030753556,
    4026938411
    '
    */
    
    --- Load comma separated string to a temp variable table  ---
    SET ARITHABORT ON
    DECLARE @T1  AS TABLE (PARALIST VARCHAR(100))
    INSERT @T1 SELECT Split.a.value('.', 'VARCHAR(100)') AS CVS  FROM  
    ( SELECT CAST ('<M>' + REPLACE(@InternalParameter1, ',', '</M><M>') + '</M>' AS XML) AS CVS ) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)
    
    --- Report Dataset query  ---
    SELECT CONTRACT_NO, report fields  FROM mytable
    WHERE  CONTRACT_NO IN (SELECT PARALIST FROM @T1)   -- Use temp variable table in where clause
    


    Mahesh

    Friday, June 27, 2014 7:20 PM
  • THIS WORKED! Thank you.

    Leroy G. Brown

    Tuesday, April 4, 2017 4:01 PM