title: Last Name Search SQL performance improvement
body: <p>Looking for inputs on the following query that utilises a table variable retrieving last name results with the matching CustomerNumberID's.</p>
  
  <p>1.</p>
  
  <pre><code>INSERT INTO #MatchingIndividuals (CustomerNumberID)
      SELECT DISTINCT(c.CustomerNumberID)
      FROM CustomerNumber c
      WHERE c.CustomerTypeID = 1
          AND EXISTS (
              SELECT 1 
              FROM CustomerIndividualNameSearch cn
                  INNER JOIN CustomerIndividualName cin
                      ON cn.CustomerIndividualNameID = cin.CustomerIndividualNameID
                  CROSS JOIN #NameCriteria cnc
              WHERE cin.CustomerNumberID = c.CustomerNumberID
                  AND cn.LastName LIKE cnc.LastName ESCAPE @escape)
      ORDER BY c.CustomerNumberID
      OFFSET @SKIP ROWS FETCH NEXT @ROWS ROWS ONLY
  </code></pre>
  
  <p>2.
  #NameCriteria is declared as </p>
  
  <pre><code>SELECT LastName, FirstName, MiddleName
          FROM dbo.CustomerIndividualNameCriteriaExpansion(@LastName, @FirstName, @MiddleName, @ExactSearch, @LastNameThreshold, @escape)'
  
              SET @where = @where + '
          AND EXISTS (
              SELECT 1 
              FROM #NameCriteria cnc
                  INNER JOIN CustomerIndividualNameSearch cn
                      ON ' + @namewhere + '
                  INNER JOIN CustomerIndividualName cin
                      ON cn.CustomerIndividualNameID = cin.CustomerIndividualNameID
              WHERE cin.CustomerNumberID = c.CustomerNumberID)'
  </code></pre>
  
  <p>3.CustomerIndividualNameCriteriaExpansion is an inline table valued function that expands first, middle and last name for wildcard name searches.</p>
  
  <p>Query is taking 30 sec or more to return. Suggestions?</p>