Entirely a poor article.  Wordy, unclear, and pretentious.  You have some good ideas.  Unfortunately, you fail to express them clearly.

Even worse, you tell us how wrong the "MUCK" practice is...

Ok I guess we're getting somewhere...  It's not the UDF or the SP that's causing the problem. 

When I use constants for @StartTime/@StopTime (either nulls, or dates like '1/1/1960' and...

Changing the clustered key is not a good option for us, but I will play with indexes some more.

And pardon if this is being a newb, but I don't understand...

Now it's getting weirder...  I moved the VehicleStartDate and VehicleStartMileage to [Common].dbo.tblVehicle so now the join to tblVehicleStart has been eliminated.  Now the straight query (no UDF or SP) runs in...

Perhaps not.  But 135K has to be doable in under 30 seconds of CPU time.    If online stores can inventory millions of items...

It will primarily be used as the record source for an Access report (where the default timeout is 30 seconds, plus people will get fidgety if it takes much longer than...

dbcc dropcleanbuffers

go

select * from fn_vehicledistances('6/1/2005','6/15/2005')

------------

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 0 ms.

DBCC execution completed. If DBCC printed error messages, contact your...

Actually... here is the plan from the query (not inside the UDF):

Table 'tblVehicleStart'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.

Table 'Worktable'. Scan count 30238, logical...

set statistics io on

set statistics time on

dbcc dropcleanbuffers

go

select * from fn_vehicledistances(null,null)

dbcc dropcleanbuffers

go

select * from fn_vehicledistances('1/1/1960','1/1/2010')

set statistics io off

set statistics time off

go

---------------

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed...

42 rows each.  They both return exactly the same results.  This is why I don't understand how one can possibly be twice as fast since it's the same query.

Alright, here's what you asked for. 

Method #1, 14 seconds: Here is the entire UDF.  What I did to get 14 seconds is pull...

Is there a good reason why the the UDF takes 30 seconds to run when pass in @StartTime=null and @Stoptime=null, but it only takes 14 seconds to run when I copy the code...

Here is, I believe, all that pertains to this query:

CREATE TABLE [tblMerged] (

 [ID] [int] IDENTITY (1, 1) NOT NULL ,

 [VIN] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

 [StartTime] [datetime] NULL ,

 [StopTime] [datetime] NULL...

Pardon the newbness but you'll have to define "ddl," sorry.  And yeah I probably could change the clustered index as long as it doesn't destroy anyone else's query efficiency.

Certainly.  This is the workplan of the original query I gave a couple posts back (without the changes we talked about).

  |--Compute Scalar(DEFINE: ([Expr1009]=[Expr1007]-If ([tblVehicleStart].[StartMileage]<>NULL) then [tblVehicleStart].[StartMileage] else [Expr1008]))

       |--Parallelism(Gather...