Friday, August 28, 2015

Recompile the store procedure to improve the performance

One of our report runs under 2min in TEST environment, but over 10 min when deployed in production.  After days of research, it turns out all we need to do is to recompile the stored procedure.

Below is from MSDN:

When a procedure is compiled for the first time or recompiled, the procedure’s query plan is optimized for the current state of the database and its objects. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure’s query plan for those changes. This can improve the procedure’s processing performance.

example:
USE AdventureWorks2012;
GO
EXEC sp_recompile N'Sales.Customer';
GO

https://msdn.microsoft.com/en-us/library/ms190439.aspx

No comments: