Reporting Services - String parameter problems

We have noticed that SQL Reporting Services uses the 'nvarchar' data type for
all report parameters of type 'string'. If the actual datatype of the column
is 'varchar' this causes (serious) performance problems as SQL Server must
convert the 'varchar' column to match the 'nvarchar' parameter.
Is there any way to control the data type that is chosen for 'string'
parameters? And why does SQL Reporting Services choose 'nvarchar' when the
column is of type 'varchar'?
For example, if I have a table defined as:
CREATE TABLE [TestTable] (
[TheKey] [int] NOT NULL ,
[Name] [varchar] (50) NULL ,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[TheKey]
)
)
... and I write a simple report to display this data with a query of :
SELECT Name
FROM TestTable
WHERE (Name = @.NameParam)
.. I notice that the query sent to SQL Server (from Profiler) is:
exec sp_executesql N'SELECT Name
FROM TestTable
WHERE (Name = @.NameParam)', N'@.NameParam nvarchar(4000)', @.NameParam = N'Name0'
... notice that the parameter type is 'nvarchar(4000)' but the actual column
data type is 'varchar'. If you create an execution plan for this query you
can see that SQL Server must perform alot more work than if the parameter was
defined as 'varchar(4000)'.
Thanks,
Scott.Scott
Have you tried CONVERT or CAST on both sides of the expression, i.e
WHERE CONVERT(Varchar(4000), Name) = CONVERT(VarChar(4000), @.NameParam)
I know it's clunky but it is a way of explicitly data typing the fields!
Chris
"Scott Simms" wrote:
> We have noticed that SQL Reporting Services uses the 'nvarchar' data type for
> all report parameters of type 'string'. If the actual datatype of the column
> is 'varchar' this causes (serious) performance problems as SQL Server must
> convert the 'varchar' column to match the 'nvarchar' parameter.
> Is there any way to control the data type that is chosen for 'string'
> parameters? And why does SQL Reporting Services choose 'nvarchar' when the
> column is of type 'varchar'?
> For example, if I have a table defined as:
> CREATE TABLE [TestTable] (
> [TheKey] [int] NOT NULL ,
> [Name] [varchar] (50) NULL ,
> CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
> (
> [TheKey]
> )
> )
> ... and I write a simple report to display this data with a query of :
> SELECT Name
> FROM TestTable
> WHERE (Name = @.NameParam)
> .. I notice that the query sent to SQL Server (from Profiler) is:
> exec sp_executesql N'SELECT Name
> FROM TestTable
> WHERE (Name = @.NameParam)', N'@.NameParam nvarchar(4000)', @.NameParam => N'Name0'
> ... notice that the parameter type is 'nvarchar(4000)' but the actual column
> data type is 'varchar'. If you create an execution plan for this query you
> can see that SQL Server must perform alot more work than if the parameter was
> defined as 'varchar(4000)'.
> Thanks,
> Scott.
>
>|||Hi Chris,
Yep. If we cast the parameter to varchar the query executes properly. I was
hoping not to update all of my reports to fix this problem!
If you can think of anyway to change the behaviour within Reporting Services
I would appreciate it. If not, can you check if this is being addressed in a
service pack or next version?
Thanks,
Scott.
"Chris McGuigan" wrote:
> Scott
> Have you tried CONVERT or CAST on both sides of the expression, i.e
> WHERE CONVERT(Varchar(4000), Name) = CONVERT(VarChar(4000), @.NameParam)
> I know it's clunky but it is a way of explicitly data typing the fields!
> Chris
> "Scott Simms" wrote:
> > We have noticed that SQL Reporting Services uses the 'nvarchar' data type for
> > all report parameters of type 'string'. If the actual datatype of the column
> > is 'varchar' this causes (serious) performance problems as SQL Server must
> > convert the 'varchar' column to match the 'nvarchar' parameter.
> >
> > Is there any way to control the data type that is chosen for 'string'
> > parameters? And why does SQL Reporting Services choose 'nvarchar' when the
> > column is of type 'varchar'?
> >
> > For example, if I have a table defined as:
> >
> > CREATE TABLE [TestTable] (
> > [TheKey] [int] NOT NULL ,
> > [Name] [varchar] (50) NULL ,
> > CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
> > (
> > [TheKey]
> > )
> > )
> >
> > ... and I write a simple report to display this data with a query of :
> >
> > SELECT Name
> > FROM TestTable
> > WHERE (Name = @.NameParam)
> >
> > .. I notice that the query sent to SQL Server (from Profiler) is:
> >
> > exec sp_executesql N'SELECT Name
> > FROM TestTable
> > WHERE (Name = @.NameParam)', N'@.NameParam nvarchar(4000)', @.NameParam => > N'Name0'
> >
> > ... notice that the parameter type is 'nvarchar(4000)' but the actual column
> > data type is 'varchar'. If you create an execution plan for this query you
> > can see that SQL Server must perform alot more work than if the parameter was
> > defined as 'varchar(4000)'.
> >
> > Thanks,
> >
> > Scott.
> >
> >
> >|||Hi Scott,
I appreciate what your saying.
I reckon it's "Data Type Precedence" that's causing your problem, see;
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_2js5.asp
for an explanation.
It's typing to the highest common denominator which must be the parameter in
your case.
Is this parameters values derived from a query?
If not, then it's Reporting Services itself setting it to nvarchar!
If it is a query, maybe you could look at the query to force the type - I
don't really know.
But going back to my original suggestion, casting the parameter alone should
cure your problem, so it's just half as clunky! :)
Regards
Chris
"Scott Simms" wrote:
> Hi Chris,
> Yep. If we cast the parameter to varchar the query executes properly. I was
> hoping not to update all of my reports to fix this problem!
> If you can think of anyway to change the behaviour within Reporting Services
> I would appreciate it. If not, can you check if this is being addressed in a
> service pack or next version?
> Thanks,
> Scott.
>
> "Chris McGuigan" wrote:
> > Scott
> >
> > Have you tried CONVERT or CAST on both sides of the expression, i.e
> > WHERE CONVERT(Varchar(4000), Name) = CONVERT(VarChar(4000), @.NameParam)
> > I know it's clunky but it is a way of explicitly data typing the fields!
> >
> > Chris
> >
> > "Scott Simms" wrote:
> >
> > > We have noticed that SQL Reporting Services uses the 'nvarchar' data type for
> > > all report parameters of type 'string'. If the actual datatype of the column
> > > is 'varchar' this causes (serious) performance problems as SQL Server must
> > > convert the 'varchar' column to match the 'nvarchar' parameter.
> > >
> > > Is there any way to control the data type that is chosen for 'string'
> > > parameters? And why does SQL Reporting Services choose 'nvarchar' when the
> > > column is of type 'varchar'?
> > >
> > > For example, if I have a table defined as:
> > >
> > > CREATE TABLE [TestTable] (
> > > [TheKey] [int] NOT NULL ,
> > > [Name] [varchar] (50) NULL ,
> > > CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
> > > (
> > > [TheKey]
> > > )
> > > )
> > >
> > > ... and I write a simple report to display this data with a query of :
> > >
> > > SELECT Name
> > > FROM TestTable
> > > WHERE (Name = @.NameParam)
> > >
> > > .. I notice that the query sent to SQL Server (from Profiler) is:
> > >
> > > exec sp_executesql N'SELECT Name
> > > FROM TestTable
> > > WHERE (Name = @.NameParam)', N'@.NameParam nvarchar(4000)', @.NameParam => > > N'Name0'
> > >
> > > ... notice that the parameter type is 'nvarchar(4000)' but the actual column
> > > data type is 'varchar'. If you create an execution plan for this query you
> > > can see that SQL Server must perform alot more work than if the parameter was
> > > defined as 'varchar(4000)'.
> > >
> > > Thanks,
> > >
> > > Scott.
> > >
> > >
> > >