title: Alternative for joining two tables multiple times
body: <p>I have a situation where I have to join a table multiple times. Most of them need to be left joins, since some of the values are not available. How to overcome the query poor performance when joining multiple times?</p>
  
  <h1>The Scenario</h1>
  
  <h3>Tables</h3>
  
  <p>[Project]: ProjectId Guid, Name VARCHAR(MAX).<br/>
  [UDF]: EntityId Guid, EntityType Char(1), UDFCode Guid, UDFName varchar(20)<br/>
  [UDFDetail]: UDFCode Guid, Description VARCHAR(MAX)</p>
  
  <p>Relationship:<br/>
  [Project].ProjectId - [UDF].EntityId<br/>
  [UDFDetail].UDFCode - [UDF].UDFCode</p>
  
  <p>The UDF table holds custom fields for projects, based on the UDFName column. The value for these fields, however, is stored on the UDFDetail, in the column Description.
  I have lots of custom columns for Project, and they are stored in the UDF table. </p>
  
  <p>So for example, to get two fields for the project I do the following select: </p>
  
  <pre><code>SELECT 
      p.Name ProjectName,
      ud1.Description Field1, 
      ud1.UDFCode Field1Id,
      ud2.Description Field2,
      ud2.UDFCode Field2Id
  FROM
      Project p
      LEFT JOIN UDF u1 ON
          u1.EntityId = p.ProjectId AND u1.ItemName='Field1'
      LEFT JOIN UDFDetail ud1 ON
          ud1.UDFCode = u1.UDFCode
      LEFT JOIN UDF u2 ON
          u2.EntityId = p.ProjectId AND u2.ItemName='Field2'
      LEFT JOIN UDFDetail ud2 ON
          ud2.UDFCode = u2.UDFCode
  </code></pre>
  
  <h1>The Problem</h1>
  
  <p>Imagine the above select but joining with like 15 fields.  In my query I have around 10 fields already and the performance is not very good. It is taking about 20 seconds to run. I have good indexes for these tables, so looking at the execution plan, it is doing only index seeks without any lookups. Regarding the joins, it needs to be left join, because Field 1 might not exist for that specific project.</p>
  
  <h1>The Question</h1>
  
  <p>Is there a more performatic way to retrieve the data?
  How would you do the query to retrieve 10 different fields for one project in a schema like this?</p>