Try this TVF. Make sure there are indexes on loaddate in both order and product, with covering columns for ordernuber and product columns.

CREATE FUNCTION tvf_loadDate

(

-- Add the parameters for the function here

@Start Datetime,

@End Datetime

)

RETURNS TABLE

AS

RETURN

(

SELECT

a.ordernuber,

b.product,

a.loaddate

from [order] a

inner join [product] b

on a.itemid = b.itemid

where loaddate between @Start and @END and a.loadate >= b.loaddate

union all

SELECT

a.ordernuber,

b.product,

b.loaddate

from [order] a

inner join [product] b

on a.itemid = b.itemid

where loaddate between @Start and @END and a.loadate < b.loaddate

)

GO