the import take a long time since each time the number of imported rows is about 10000 and the table have many indexes and I have to check the previous rate on each insert ( I can't use the bulk insert).

I have a scenario that I can get the rate without using EndDate

in this case I can remove the EndDate from the table

Do you recommend this way ?

ALTER FUNCTION [dbo].[GetRate] (@prefix varchar(20),@Date datetime,@Tariff_id int)

RETURNS decimal(18,4)

AS

BEGIN

declare @rate decimal(18,4)

select @rate=rate from Tariffs t1 where

prefix=@prefix and

Tariff_id=@Tariff_id and

startDate =

(select max(startDate) from Tariffs t2

where t1.Tariff_id =t2.Tariff_id and t1.prefix=t2.prefix and t2.StartDate <= @Date )

-- code removed -- @Date >= startDate and @Date < dateadd(dd,1,endDate)

order by prefix desc

return @rate

END