December 29, 2010 at 8:23 am
I have a simple report using a Stored Procedure as the data source. When executing the SP from SQL Manager, it returns data within a parameter range in less than 1 second. When executing the SP from the designer using the same parameters, it takes over 1 minute. When trying to view the report, either through the designer or after posting to the server, it comes up after a long wait, with an unexpected end of line error.
Why does the SP take so long to execute in through the Report and what can I do about it?
December 29, 2010 at 8:41 am
We found our issue. I was using the parameters directly in the body of the SP definition. Apparently there is a known issue with how paramaters are accessed and processed. I modified the stored procedure by declaring new variables in the body of the SP and setting them to them value of the passed parameter. Reduced execution time from within SQL Manager and provided the same response time from within the report.
🙂
December 29, 2010 at 10:13 am
It is called Parameter Sniffing. You took the proper corrective action.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply