We recently upgraded to SQL Server 2017 from SQL Server 2012. This includes a change from SSRS 2012 to Power BI Reporting Services 2017 (Sept 2019) and we are now having a weird parameter problem. I am not sure if it is SSRS related or Power BI RS related or database related, and not sure how to troubleshoot which it is.
Some reports are now taking 30 seconds or more to run and they used to take about 1 second. They also take 1 second when I run the queries with the parameters in my SQL developer tool (TOAD). According to the statistics, the time is all spent in the data retrieval portion, not in report processing or rendering.
I can fix the performance problem by converting the datatype of the parameter in the SQL used in the dataset. For instance, I have a parameter that has integer values, but is set to be the default of Text datatype. In the SQL I say WHERE @Parm = 1. The query works but takes forever (30 seconds). So I change the datatype to integer on the parameter in the report. Still takes forever. It takes 1 second if I change the SQL to WHERE CONVERT(int, @Parm) = 1. This same behavior occurs when I am using text values in a text parameter. I have to CONVERT(varchar,@Parm) in order to use the parameter.
Any ideas why this is happening or how to narrow the possibilities?