CREATE FUNCTION dbo.Currency (@UserID int, @Currency varchar(3), @CurDate datetime)
RETURNS decimal(19, 10)
AS

BEGIN
DECLARE 
  @Rate decimal(19, 10)

  SET @Rate = (SELECT b.rate/a.Rate
  FROM currency a WITH (NOLOCK), currency b WITH (NOLOCK), user WITH (NOLOCK)
  WHERE user.UserID = @UserID
  AND user.Currency = a.Currency
  AND b.Currency = @Currency
  AND a.Updated = 
    (SELECT MAX(Updated) 
    FROM currency 
    WHERE Currency = user.Currency AND Updated <= @CurDate)
  AND b.Updated = 
    (SELECT MAX(Updated) 
    FROM currency 
    WHERE Currency = @Currency AND Updated <= @CurDate))

RETURN @Rate
END

ALTER FUNCTION dbo.Currency_2 (@UserID int, @Currency varchar(3), @CurDate datetime)
RETURNS decimal(19, 10)
AS

BEGIN
DECLARE 
  @Rate decimal(19, 10),
  @UserCurrency varchar(3)
  
  SET @UserCurrency = (SELECT Currency FROM bob_user WITH (NOLOCK) WHERE UserID = @UserID)
  
  IF @UserCurrency = @Currency 
    SET @Rate = 1
  ELSE
    SET @Rate = 
      (SELECT TOP 1 rate 
      FROM bob_currency WITH (NOLOCK)
      WHERE bob_currency.Currency = @Currency 
        AND Updated <= @CurDate 
      ORDER BY Updated DESC)
      /
      (SELECT TOP 1 Rate FROM bob_currency WITH (NOLOCK) WHERE Currency = @UserCurrency
        AND Updated <= @CurDate ORDER BY Updated DESC)

RETURN @Rate
END

quote:

---

when I ran my initial query with the original udf it took some 20 seconds I belive, second time I ran it it took only 5 seconds...why is this? Is there some caching or something involved here?

---