This is just a quick tip/workaround for performance issues with stored procedures in SQL Server.
I had a fairly complex SP, actually just a SELECT with calculations, joins and outer applies, that had several parameters of different types (uniqueidentifier, varchar, datetime) that were used in the WHERE clause and passed to functions. This SP took more than 1 minute to complete but when I extracted the SELECT statement form the SP and ran it in SQL Server Management Studio it took just 3 seconds. This is quite a big difference to do the exact same thing, no?
Some time ago I read something about SQL Server doing some kind of parameter checking in SP's and this could cause a performance issue. So I tried the workaround that was mentioned there: declare new variables inside your SP, asign the values of the parameters to those variables and then use those variables in the rest of the SP. Lo and behold, my SP was finished in 3 seconds.
I don't exactly understand what is going on here, if anybody knows the details don't hesitate to tell me.
