title: SQL view for searching is slow
body: <p>We are trying to build a view that provides data in a flattened manner that backs a simple search.</p>
  
  <p>The challenge has been that while we are displaying fields like the name, we are needing to search across fields like address which we don't have displayed, but can still be returned. The address is stored in an address table, and there can be many addresses to each client record.</p>
  
  <p>So I initially thought to do a view that put together the main client record with the various addresses. This resulted in duplicate rows because of the join.</p>
  
  <p>Next, I wrote a query that rolls up these child tables and concatenates them together. This makes it easy to search against and doesn't end up with any duplicates. I put it into a view, but it's pretty slow. I'm preferring to keep it a view because of the nature of the component that is accessing the data, however, I'm not completely opposed to it.</p>
  
  <p>Can this be sped up or am I going about this the wrong way?</p>
  
  <p>Here's the SQL (T-SQL, actually):</p>
  
  <pre><code>WITH ClientBase
  AS
  (
      SELECT DISTINCT Client.ClientID, ClientNames.FirstName, ClientNames.LastName, ClientNames.MiddleName, 
          AddressHistory.Address1 + ' ' + AddressHistory.Address2 AS StreetAddress, 
          AddressHistory.City, ContactInfo.Contact AS Phone
      FROM Client
          INNER JOIN ClientNames ON ClientNames.ClientID = Client.ClientID
          INNER JOIN AddressHistory ON AddressHistory.ClientID = Client.ClientID
          INNER JOIN ContactInfo ON ContactInfo.ClientID = Client.ClientID
  )
  SELECT DISTINCT vcs.ClientID, vcs.FirstName, vcs.LastName, vcs.MiddleName,
                  (SELECT STUFF ((SELECT DISTINCT TOP (100) PERCENT ';' + ClientBase.StreetAddress AS [text()]
                                  FROM ClientBase
                                  WHERE ClientBase.ClientID = vcs.ClientID
                                  FOR XML PATH('')), 1, 1, '') AS StreetAddress) AS StreetAddress,
                  (SELECT STUFF ((SELECT DISTINCT TOP (100) PERCENT ';' + ClientBase.City AS [text()]
                                  FROM ClientBase
                                  WHERE ClientBase.ClientID = vcs.ClientID
                                  FOR XML PATH('')), 1, 1, '') AS City) AS City,
                  (SELECT STUFF ((SELECT DISTINCT TOP (100) PERCENT ';' + ClientBase.Phone AS [text()]
                                  FROM ClientBase
                                  WHERE ClientBase.ClientID = vcs.ClientID
                                  FOR XML PATH('')), 1, 1, '') AS Phone) AS Phone
  FROM ClientBase AS vcs
  </code></pre>