Awesome.  I created local variables to hold the values passed to the parameters.  Then used the local variables inside the function rather the parameters.  Now it runs just as fast...

Okay.  I've got the execution plans and I'm going to go through them in detail.  Something I'm noticing off the bat is that the batch uses Hash Joins where as...

I looked at that.  The problem is when you look at the execution plan for a function it doesn't go into the internal details of the funtion.  There are 2 steps. ...

How do you recompile a function?

I've tried dropping and recreating it but still no change.

Also I've tried DBCC Freeproccache to remove the plan out of memory but still no improvement.

Here's the function it's a little convoluted.

ALTER         FUNCTION DDOverrideComp

 (@From smalldatetime,

  @To smalldatetime, @Region varchar(5))

RETURNS @table_var TABLE (tableid numeric(18,0) IDENTITY(1,1) primary key clustered,

 [Personalid] varchar(40),

 [Name] varchar(100),

 [mga] varchar(40),

 [Phone#] varchar(50),

 [BizCode] varchar(5),

 [policy]...