title: SQL Server perfomance tuning - Union tables having 1.5 million rows of data
body: <p>I have a stored procedure which unions two tables each with 1.5M rows of data. Created a fulltext index for the default search field (<code>VARCHAR(255)</code>) and a non-clustered index covering column names in select/where clause.</p>
  
  <p>Also pagination is applied and fetching only the necessary columns from both 
  tables.</p>
  
  <p><code>UNION</code> is used as I want distinct records after combining the table.</p>
  
  <p>The response time of the query varies from 3 seconds to more than a minute. When the filter text contains just two characters it is too slow, still fetches values faster when repeating the execution.</p>
  
  <pre><code>DECLARE @start INT, @length INT, @searchQuery VARCHAR(100) --Param
  DECLARE @sortQuery VARCHAR(100)
  SET @sortQuery = @searchQuery;
  SET @searchQuery = ('"' + @searchQuery + '*"');
  
  SELECT temp2.*
                  , t3.Name AS CountryName
                  , t5.Name AS CityName
                  , t4.Name AS StateProvinceName
                  , t4.Code AS StateProvinceCode      
           FROM
                  (SELECT temp1.* FROM
                      (SELECT ID
                              , Name
                              , ISNULL(Address1, '') AS AddressLine1
                              , ISNULL(Address2, '') AS AddressLine2
                              , ISNULL(CityID, 0) AS CityID
                              , ISNULL(CountryID, 0) AS CountryID
                              , ISNULL(StateProvinceID, 0) AS StateProvinceID
                              , ISNULL(PostalCode, '') AS PostalCode
                      FROM Table1
                      WHERE Deleted= 0 AND CONTAINS(Name, @searchQuery)
  
                      UNION
  
                      SELECT t2.EmployerID AS ID
                              , t2.DivisionName AS Name
                              , t2.Address1 AS AddressLine1
                              , t2.Address2 AS AddressLine2
                              , t2.CityID AS CityID
                              , t2.CountryID AS CountryID
                              , t2.StateProvinceID AS StateProvinceID
                              , t2.PostalCode AS PostalCode
                      FROM Table2 t2
                      JOIN Employer t1 ON t2.EmployerID = t1.ID
                      WHERE t2.Deleted= 0 AND t2.DivisionName IS NOT NULL AND t2.DivisionName &lt;&gt; '' 
                              AND t1.Deleted = 0 AND t2.CountryID IS NOT NULL AND t2.Address1 IS NOT NULL AND t2.Address2 IS NOT NULL  
                              AND CONTAINS(t2.DivisionName, @searchQuery)
                      )   temp1
              ORDER BY (CASE WHEN temp1.Name LIKE @sortQuery +'%' THEN 2 ELSE 1 END) DESC, temp1.Name                                 
              OFFSET @start ROWS
              FETCH NEXT @length ROWS ONLY
              )   temp2
  
           JOIN Table3 t3 ON C.ID = temp2.CountryID
           JOIN Table4 t4 ON S.ID = temp2.StateProvinceID
           JOIN Table5 t5 ON Ct.ID = temp2.CityID
          ORDER BY temp2.Name
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/90q8z.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/90q8z.png" alt="enter image description here"></a></p>