Reporting Services - SQL query takes 10 times as long, 100% cpu, when in a stored procedure vs inline SQL

Question

I have a SQL query - fairly complex, but not too bad.

When I run the SQL query in Management Studio, the query runs in about 10 seconds or less.

When I put the SQL query directly into a reporting services report, the query runs in about 10 seconds or less.

When I put the exact same SQL query into a stored procedure, and call the procedure in the reporting services report, the SQL query takes 10 times as long, or times out completely.

Is there anything specific about stored procedure settings?

Answer 1

Have a look at parameter sniffing?

That's the most likely culprit.

Also look at your statistics.

Answer 2

Yeah it probably is parameter realted optimisation problems. Can you post the code fro the SP and the code straight SQL you are running in SSRS and we can have a guess at wyhere the problem might lie.