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

Question

We are having a problem in our test and dev environments with a function that runs quite slowly at times when called from an .Net Application. When we call this function directly from management studio it works fine.

Here are the differences when they are profiled: From the Application:
CPU: 906
Reads: 61853
Writes: 0
Duration: 926

From SSMS:
CPU: 15
Reads: 11243
Writes: 0
Duration: 31

Now we have determined that when we recompile the function the performance returns to what we are expecting and the performance profile when run from the application matches that of what we get when we run it from SSMS. It will start slowing down again at what appear to random intervals.

We have not seen this in prod but they may be in part because everything is recompiled there on a weekly basis.

So what might cause this sort of behavior?

Edit -
We finally were able to tackle this and restructuring the varables to deal with parameter sniffing appears to have done the trick...a snippet of what we did here: Thanks for your help.

        -- create set of local variables for input parameters - this is to help performance - vis a vis "parameter sniffing"
    declare @dtDate_Local                  datetime
           ,@vcPriceType_Local             varchar(10)
           ,@iTradingStrategyID_Local      int
           ,@iAccountID_Local              int
           ,@vcSymbol_Local                varchar(10)
           ,@vcTradeSymbol_Local           varchar(10)
           ,@iDerivativeSymbolID_Local     int
           ,@bExcludeZeroPriceTrades_Local bit

   declare @dtMaxAggregatedDate     smalldatetime
          ,@iSymbolID               int
          ,@iDerivativePriceTypeID  int

   select @dtDate_Local                  = @dtDate
          ,@vcPriceType_Local             = @vcPriceType
          ,@iTradingStrategyID_Local      = @iTradingStrategyID
          ,@iAccountID_Local              = @iAccountID
          ,@vcSymbol_Local                = @vcSymbol
          ,@vcTradeSymbol_Local           = @vcTradeSymbol
          ,@iDerivativeSymbolID_Local     = @iDerivativeSymbolID
          ,@bExcludeZeroPriceTrades_Local = @bExcludeZeroPriceTrades

Answer 1

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 2

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 Parameter Sniffing (or Spoofing) in SQL Server

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 ''?'''