How can I optimize this horrendously inefficient User Defined Function in SQL Server 08

Is there anyway to optimize this horrible inefficient UDF in SQL Server 08. I am fairly new to UDF's and especially looking at them for optimizations.

UPDATE: Should I be sending a column to a function like this if I wanted to perform it on each row and each column in a query? Is there a better way to go about this?

Thank You

** @value(float) and @fieldname(varchar(40)) are input parameters **

BEGIN
  DECLARE @UT integer, @FRM integer, @TO integer, @FACTOR float

  select @UT =  [UF_UT_ID] FROM dbo.UNIT_FIELDS where [UF_FIELD]=@fieldName
  select @FRM = [UT_UN_ID_INTERNAL_UNITS] from dbo.UNIT_TYPES where [UT_ID]=@UT
  select @TO = [UT_UN_ID_DISPLAY_UNITS] from dbo.UNIT_TYPES where [UT_ID]=@UT
  select @FACTOR = [UC_SLOPE] from dbo.UNIT_CONVERSIONS where [UC_UN_ID_UNIT_FROM]=@FRM and [UC_UN_ID_UNIT_TO]=@TO

  -- Return the result of the function dbo.
  RETURN @FACTOR*@value
END

From stackoverflow

• Step 1 would be to run each select individually and see where the bottleneck is.

• If you can join the three tables based on PK/FK relationships you could get the query down to a single select. If not, the only immediately obvious optimization is to assign @FRM and @TO in a single select statement:

  select @FRM = [UT_UN_ID_INTERNAL_UNITS], @TO = [UT_UN_ID_DISPLAY_UNITS] from dbo.UNIT_TYPES where [UT_ID]=@UT
  

• A classical candidate for a inline table function...

  Something like:

  ALTER FUNCTION fnName(@value float, @fieldName VARCHAR(100))
  RETURNS TABLE 
  AS
  RETURN 
  (
      SELECT @value * 
       (SELECT conv.[UC_SLOPE] from dbo.UNIT_CONVERSIONS conv
       JOIN dbo.UNIT_TYPES UT_UN_ID_INTERNAL_UNITS ON where [UC_UN_ID_UNIT_FROM]=UT_UN_ID_INTERNAL_UNITS.[UT_ID]
       JOIN dbo.UNIT_TYPES UT_UN_ID_DISPLAY_UNITS ON where [UC_UN_ID_UNIT_TO]=UT_UN_ID_DISPLAY_UNITS.[UT_ID]
       JOIN dbo.UNIT_FIELDS fields ON (UT_UN_ID_INTERNAL_UNITS.[UT_ID] = fields.[UF_UT_ID]) AND (UT_UN_ID_DISPLAY_UNITS.[UT_ID] = fields.[UF_UT_ID])
       WHERE ([UF_FIELD]=@fieldName)
       )
  )
  

• One small thing you can do:

   select @FRM = [UT_UN_ID_INTERNAL_UNITS] from dbo.UNIT_TYPES where [UT_ID]=@UT  
   select @TO = [UT_UN_ID_DISPLAY_UNITS] from dbo.UNIT_TYPES where [UT_ID]=@UT
  

  If I read the correctly this would be the same as:

   select @TO = [UT_UN_ID_DISPLAY_UNITS],
          @FRM = [UT_UN_ID_INTERNAL_UNITS] 
  
   from dbo.UNIT_TYPES where [UT_ID]=@UT
  

  Why run a select twice on the same record?

• This needs to rewritten using JOIN's:

  SELECT  c.UC_SLOPE * @value
  FROM    unit_fields f
  JOIN    unit_types t
  ON      t.UT_ID = f.UF_UT_ID
  JOIN    unit_conversions c
  ON      c.UC_UN_ID_UNIT_FROM = t.UT_UN_ID_INTERNAL_UNITS
          AND c.UC_UN_ID_UNIT_TO = t.UT_UN_ID_DISPLAY_UNITS
  WHERE   f.UF_FIELD = @field_name
  

• BEGIN
    DECLARE @FACTOR float
  
      select @factor = UC.UC_SLOPE
        from dbo.UNIT_CONVERSIONS UC,
             dbo.UNIT_TYPES       UT,
             dbo.UNIT_FIELDS      UF
        where UF.UF_FIELD=@fieldName
          and UT.UT_ID = UF.UF_UT_ID
          and UC.UC_UN_ID_UNIT_FROM = UT.UT_UN_ID_INTERNAL_UNITS
          and UC.UC_UN_ID_UNIT_TO = UT.UT_UN_ID_DISPLAY_UNITS
  
  
    -- Return the result of the function dbo.
    RETURN @FACTOR*@value
  END
  

• You can try this as all one query:

  --Decalre Factor var
  DECLARE @FACTOR float
  
  SELECT @FACTOR = [UC_SLOPE] 
  FROM dbo.UNIT_CONVERSIONS uc
  --Join the Unit Types table to Unit Conversions on the old From & To types
  INNER JOIN dbo.UNIT_TYPES ut ON
   ut.[UT_UN_ID_DISPLAY_UNITS] = uc.[UC_UN_ID_UNIT_TO]
   AND ut.[UT_UN_ID_INTERNAL_UNITS] = uc.[UC_UN_ID_UNIT_FROM]
  --Join the Unit Files on the Unit Types
  INNER JOIN dbo.UNIT_FIELDS uf ON
   uf.[UF_UT_ID] = ut.[UT_ID]
  WHERE uf.[UF_FIELD]=@fieldName
  
  -- Return the result of the function dbo.
  RETURN @FACTOR*@value