SQL: Why Stored Procedure execution slower from normal select query?

on

Questions:

I've stored procedure (SP) that takes a long time to execute. When i try to debug why the SP executing is very slow, first I tried running the select query in that particular stored procedure. To my surprise, it's very fast! The question is, why running the select query itself is fast but when executing it via SP with the same select query it's very slow? How to solve this issue?

CREATE PROCEDURE GetActiveEmployeesByCompanyId (@CompanyId INT)
AS
BEGIN
    SELECT * FROM Employee WHERE IsActive=1 AND CompanyId=@CompanyId
END

Answers:

This happen due to "Parameter Sniffing". It can happen when you use the user parameter defined in SP within your select script as condition.

To resolve it, you can introduce new local variable/parameter inside the SP itself. Please refer to below example. Happy querying! 

CREATE PROCEDURE GetActiveEmployeesByCompanyId (@CompanyId INT)
AS
BEGIN
    DECLARE @LocCompanyId INT;
    SET @LocCompanyId=@CompanyId;
    SELECT * FROM Employee WHERE IsActive=1 AND CompanyId=@LocCompanyId
END