Why are there performance differences when a SQL function is called from .Net app vs when the same call is made in Management Studio

Answer 1

This is usually because you are getting a different execution plan in your SSMS connection. Often related to parameter sniffing issues where when the plan gets generated with a specific value that is sub optimal for other values of the parameters. This also explains why recompiling would resolve the issue. This thread seems to have a good explanation http://stackoverflow.com/questions/211355/parameter-sniffing-or-spoofing-in-sql-server

Answer 2

I had similar problem with stored procedures, and for me it turned out to be 'parameter sniffing'. Google that and see if it solves your problem, for me it was dramatic speed-up once I fixed it.

In my case, I fixed it by declaring a local variable for each parameters that was passed in, and then assigned the local variable to that parameter value, and the rest of the proc used the local variables for processing...for whatever reason, this defeated the parameter sniffing.

Answer 3

A likely cause is out of date statistics and/or parameter sniffing causing a cached query plan to be re-used that is sub-optimal.

SSMS emits pre-amble statements that you don't see, that cause the submitted query to be re-compiled each time, thus eliminating the possibility of using an incorrect cached plan.

This will update all statistics and refresh views and stored procs (but be careful about running on a Production machine):

EXEC sp_updatestats

EXEC sp_refreshview 

EXEC sp_msForEachTable 'EXEC sp_recompile ''?'''