(http://support.microsoft.com/default.aspx?scid=kb;en-us;2000524&sd=rss&spid=13165).
A Microsoft consultant told me about this in mid-May. Parameters are coerced into Unicode and applied in the SQL query to the SQL Server.
The problem is query optimization. As suggested in the notice:
This may result in performance issues on the backend SQL server. For example SQL server may perform a table scan instead of a seek.
The interim workarounds are:
- Use an expression based command text with Join().
- Use stored procedures with parameters passed in e.g. as comma-separated string.
- Implement custom data extensions with your own implementation of multi-value query parameter rewrite.
- Change database schema column definition to NVarchar
None of them are particularly appealing if your shop has invested in designing hundreds of reports with multi-parameter drop down options against thousands of tables in the Clarity database, many of which are so large that access using a table scan is of concern.
The first two are the most feasible and are being explored. However, now the issue is public and some clever approaches will likely be forthcoming.
Stay tuned on this issue.
There is an even larger issue of parameters in RS and the optimization of queries. That will be covered in a subsequent posting.
Edit Update: One of the MS field engineers provides more detail on the remedies at http://blogs.msdn.com/sql_pfe_blog/archive/2009/06/17/possible-performance-implications-when-using-string-parameters-in-reporting-services.aspx
I'll be applying these next week to some of our problem queries issued by RS and see if index scans can be turned into index seeks.
Posts
