Sample Header Ad - 728x90

Stored Procedure Performance Issue in Sharepoint Report

1 vote
1 answer
203 views
We have a report stored procedure that runs in about 1 minute in production; however, when executed from the Sharepoint it populates, it rus 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
Asked by KRISTOPHER COOK (109 rep)
Aug 28, 2014, 02:56 PM
Last activity: Dec 30, 2022, 11:05 AM