Today I come across something interresting in SQL Server.

I was optimizing a report query and in the SSMS windows runned in 10 seconds for 3000 rows.

Put it to a stored procedure took me 5 minutes for getting 100 rows.

I was like WTF?

After some research I found out that the problem was that I was using the Stored Procedure parameters in the query.

Created local variables for the parameters and poof... 10 seconds again.

So if you are creating Stored Procedures in SQL Server DO NOT USE THE PARAMETERS FROM THE PROCEDURE. CREATE LOCAL VARIABLES.