We have a report stored procedure that runs in about 1 minute in production; however, when executed from the Sharepoint it populates, it runs for over 20 minutes, with exactly the same parameters. We've done everything to avoid parameter sniffing problems: used WITH RECOMPILE on the procedure declaration; added OPTION OPTIMIZE FOR UNKNOWN on the parameters within queries; even created local variables and assigned the parameter values to them and used them throughout the procedure. None of these has had any effect. I know that the 1-base minute run time is a big red flag, and we're working on pre-aggregating this data into our BI platform, but that's months down the liine and this is needed for month-end close every month.
Other relevant information:
Sharepoint 2013 on SQL Server 2012
Data Source is a SQL Server 2008 R2 database
Report definition created in Visual Studio 2008
No comments:
Post a Comment