Contents
Is the SSRs query running fast or slow?
The query runs fast (in less than 5 secs) if I hardcode the parameter in the SSRS query but if it’s left as a dynamically chosen parameter the query takes over 5 minutes to render. I have read a little about “Parameter Sniffing” but I am not sure if that applies to my case since I am only using a TSQL query and not a SP.
How long does it take SSRs to render a query?
I am just retrieving some data from a table and I am using a parameter in the Where clause of the query. The query runs fast (in less than 5 secs) if I hardcode the parameter in the SSRS query but if it’s left as a dynamically chosen parameter the query takes over 5 minutes to render.
Why is SQL Server executing from SSRS report?
SQL Server was producing a convoluted execution plan when executed from the SSRS report due to ‘parameter sniffing’. The workaround was to declare variables inside of the stored procedure and assign the incoming parameters to the variables.
When to use parameter sniffing in SSMS query?
“Parameter sniffing” applies to any parameterized query, not just stored procedures. But before you go down that path, ensure that the data type of @Para1 exactly matches that of the underlying Col2 column. Otherwise an implicit conversion may be needed, which can mitigate usefulness of indexes on the column.
Can a 10 second query cause performance degradation?
The slow queries definition might differ in different cases since there are certain occasions that even a 10 second query is acceptable and still not slow. However, if your application is an OLTP, it’s very common that a 10 second or even a 5 second query is an issue or causes performance degradation to your database.
Why does the report spend all its time on the query?
According to profiler and the RS ExecutionLogStorage table, the report was spending all it’s time on the query. Brian S.’s comment led me to the solution..I simply added WITH RECOMPILE before the AS statement in the SP, and now the report time pretty much matches the SP execution time.
What does it mean when a query is slow?
The slow query logs will show you a list of queries that have been identified as slow, based on the given values in the system variables as mentioned earlier. The slow queries definition might differ in different cases since there are certain occasions that even a 10 second query is acceptable and still not slow.