Very few people have thought about this performance killer. It is all about report parameter of text type.
The initial situation
There was a table with about 45 million rows. Some columns in the table were VARCHAR and should have been filtered. As a result of filtering by 10 different parameters the total number of rows could have been significantly reduced. SQL Server was going for table scan in this case. Also there was a number of indexes defined on the table.
The where-clause based on a particular parameter was used:
WHERE
Analysis
In case you, for example, run the statement above and look into Activity Monitor, you will see what query SSRS is really running:
WHERE
At this moment the ranking by data types come into arena. As you can see data type with the lower precedence is implicitly converted up to the data type with the higher precedence.. In this case, there were also varchar (in the table) and nvarchar (as a parameter). Now the list is presented inconveniently, because the smallest number means the highest data type. Here we should note that varchar fields are converted to nvarchar.
It would have been better to declare some of the parameters as varchar, but this improvement hasn’t been implemented in Reporting Services yet.
Solution
There are several ways to change the way of this conversion. Here I will describe the method that suits me most.
Dynamic dataset query
We build a dataset query at run time by ourselves, which means that we have to define the fields entirely by hand.
Thus, the dataset begins with
How to build a command for a comma-separated list, where there is no capital N standing before a string and the values are not of nvarchar type.
The command looks as follows:
WHERE
In case we have other parameters, for example numeric data, the appropriate command needs to be written and it is not difficult:
With this dynamic dataset now we can run correct queries in terms of performance and perform Index Seek instead of Index Scan.
The conversion of the data type into nvarchar in the table was out of the question, since the space used increases and fewer records fit one page, but data compression can improve this.
A good alternative would be to replace the strings with numeric values and run the queries only with numeric parameters. But for this purpose you must create a small data warehouse with the corresponding dimension tables and rebuild the data.