title: A faster SQL query using temp tables?
body: <p>I am pretty new with SQL, but I need to work out how to make a whole bunch of existing queries I have inherited faster.</p>
  
  <p>Here is there WHERE clause for one of them:</p>
  
  <pre><code> @SearchFor nvarchar(200)        
   ,@SearchIn nvarchar(1024)      
   ,@SearchActivity int   
  
      -- stuff here I've left out ---
  
  WHERE
      ( -- filter for all queries --
              (FT_TBL.IsActive = 1)
          AND (FT_TBL.IsPro = 1)
          AND (
              (FT_TBL.DatePaidUpTo &gt;= @CurrentTime)
              OR (
                  (FT_TBL.IsFromPaidBusinessDB = 1) 
                  AND (FT_TBL.DatePaidUpTo IS NULL)
              )
          )
          AND (aspnet_Membership.IsApproved = 1)
      )       
      AND ( -- filter if user fills in 'searchfor' box --
             (@SearchFor IS NULL)
          OR (FT_TBL.CompanyName like '%' + @SearchFor + '%') 
          OR (aspnet_Users.UserName like '%' + @SearchFor + '%')
          OR (Activities.Activity like '%' + @SearchFor + '%')
      )
      AND ( -- filter if user fills in 'searchIn' box --
             (@SearchIn IS NULL)
          OR (a1.City LIKE '%' + @SearchIn + '%') 
          OR (a1.Region LIKE '%' + @SearchIn + '%') 
          OR (a1.Postcode LIKE '%' + @SearchIn + '%') 
          OR (Country.Name LIKE '%' + @SearchIn + '%')
      )
      AND ( -- filter by activity --
             (@SearchActivity IS NULL)
          OR (@SearchActivity = Activities.ActivityID)
      )
  
      AND NOT EXISTS (Select a2.AddressId, a2.UserId
              from Addresses a2
              where a2.userid = a1.UserId
              and a2.addressid &lt; a1.addressid
      )
  </code></pre>
  
  <p><code>SearchIn</code>, <code>SearchFor</code>, and <code>SearchActivity</code> are three fields that that can passed through to filter search results. The idea is that if 'null' is passed through for each of these, the only constraints on the search results come from the first block in the WHERE clause. If the any of these 3 fields are not null, the results are further constrained based on location, name,or category for that row. The final block is a little trick - users can have several addresses attached to them, but there should only be one row returned for each user. So this block just picks the address with the lowest ID.</p>
  
  <p>At the moment this query runs very slowly - partly because our hardware is underpar, but also, I suspect, because this proc isn't efficient enough. I am trying to work out ways to make it better - difficult as I am trying to learn how SQL works at the same time!</p>
  
  <p>One of the ideas I had was to try doing the search in two stages - For instance, to first do a query with only the first block of the <code>WHERE</code> clause, and then do a second query on the resultant table with the rest of the blocks in the <code>WHERE</code> clause. As the initial block filters out a lot of the rows in the table,  I thought this could help.</p>
  
  <p>Can someone suggest a good way to go about improving this query? Also, what tools are best used to test the efficiency of a query? sometimes the execution time can vary wildly, even for the same inputs.</p>