Tengo un caso en el que necesito traducir (buscar) varios valores de la misma tabla. La primera forma en que lo escribí fue usando subconsultas:

SELECT
    (SELECT id FROM user WHERE user_pk = created_by) AS creator,
    (SELECT id FROM user WHERE user_pk = updated_by) AS updater,
    (SELECT id FROM user WHERE user_pk = owned_by) AS owner,
    [name]
FROM asset

Como uso mucho esta subconsulta (es decir, tengo alrededor de 50 tablas con estos campos), y es posible que deba agregar más código a la subconsulta (por ejemplo, "Y activo = 1") pensé que los pondría en una función definida por el usuario UDF y usar eso. Pero el rendimiento con ese UDF fue abismal.

CREATE FUNCTION dbo.get_user ( @user_pk INT )
RETURNS INT
AS BEGIN 
    RETURN ( SELECT id
             FROM   ice.dbo.[user]
             WHERE  user_pk = @user_pk )
END

SELECT dbo.get_user(created_by) as creator, [name]
FROM asset

El rendimiento del # 1 es menos de 1 segundo. El rendimiento del # 2 es de aproximadamente 30 segundos ...

¿Por qué, o lo que es más importante, hay alguna forma de codificar en SQL Server 2008, para no tener que usar tantas subconsultas?

Editar

Solo un poco más de explicación de cuándo esto es útil. Esta simple consulta (es decir, obtener ID de usuario) se vuelve mucho más compleja cuando quiero tener un texto para un usuario, ya que tengo que unirme con el perfil para obtener el idioma, con una empresa para ver si el idioma debe ser obtenido ' ed desde allí en su lugar, y con la tabla de traducción para obtener el texto traducido. Y para la mayoría de estas consultas, el rendimiento es un problema secundario para la legibilidad y la mantenibilidad.