title: Slow Query - uses views
body: <p>Backend on the cloud using SQL Server Express 2008 R2
  Frontend on Access 2013</p>
  <p>I had a query that I recently had to add a subquery to to restrict the results.  The query took about 1 second to run before, but now takes about 70+.  I think the problem may be that I am using views in the query that are not indexed.  I'm pretty new to this and don't use Access/SQL much, so apologies if i am missing something obvious here.</p>
  <p>This is my query code:</p>
  <pre>SELECT DISTINCT vuSearch.PAID
      ,vuSearch.PDID
      ,ConcatAddress(Nz([Building_Name]), Nz([Building_No]), Nz([Street]), Nz([IndEst]), Nz([District]), Nz([Town]), Nz([Postcode])) AS Address
      ,vuSearch.Deal_Date
      ,vuSearch.Lease_End
      ,vuSearch.Break_Date
      ,vuSearch.Review_Date
      ,vuSearch.PropertyType
      ,vuSearch.Acting_For
      ,vuSearch.Landlord_Seller
      ,vuSearch.Tenant_Purchaser
      ,IIf(IsNull([vuSearch.GIA]), [vuSearch.NIA], [vuSearch.GIA]) AS MainArea
      ,vuDesc.Comments_Incentives
      ,tldDealSearch.Include
      ,vuSearch.Incomplete
  FROM (
      vuSearch RIGHT JOIN tldDealSearch ON vuSearch.PDID = tldDealSearch.PDID
      )
  LEFT JOIN vuDesc ON tldDealSearch.PDID = vuDesc.PDID
  WHERE (
          (
              (vuSearch.PDID) IN (
                  (
                      SELECT Max(v2.PDID)
                      FROM vuSearch AS v2
                      GROUP BY v2.PAID
                      )
                  )
              )
          AND ((vuSearch.Incomplete) = False)
          );</pre>
  <p>I added indexes to the tldDealSearch table for the PDID and Include fields (I think I've done that right).  When I looked at the views on the backend however I could not add indexes as the views are no schema bound.</p>
  <p>Is there anything I can do or should be looking at to speed this up?  I'm quite worried as right now there are only 300 records on the database - 70+ seconds?</p>
  <p>I have checked everything on the 'Analyze performance' tool but not sure what to do next.</p>
  <p>EDIT:
  Thanks for the fast responses guys.</p>
  <p>NZ - This is the Access NullToZero function i believe, or what replaced it.
  <a href="http://www.techonthenet.com/access/functions/advanced/nz.php" rel="nofollow noreferrer">http://www.techonthenet.com/access/functions/advanced/nz.php</a></p>
  <p>ConcatAddress - This is a function I use to put all elements of an address together in a readable format for inclusion in a report.</p>
  <pre>Public Function ConcatAddress(strBuildingName As String, strBuildingNo As String, strStreet As String, _
  strIndEstate As String, strDistrict As String, strTown As String, strPostcode As String) As String
      On Error GoTo ErrRoutine
      Dim strSQL As String
      If Len(strBuildingName) > 0 Then
          strSQL = strBuildingName
      End If
      If Len(strBuildingNo) > 0 Then
          If Len(strSQL) > 0 Then
              strSQL = strSQL & " " & strBuildingNo
          Else
              strSQL = strBuildingNo
          End If
      End If
      If Len(strStreet) > 0 Then
          If Len(strSQL) > 0 Then
              strSQL = strSQL & " " & strStreet & ","
          Else
              strSQL = strStreet
          End If
      End If
      If Len(strIndEstate) > 0 Then
          If Len(strSQL) > 0 Then
              strSQL = strSQL & " " & strIndEstate & ","
          Else
              strSQL = strIndEstate
          End If
      End If
      If Len(strDistrict) > 0 Then
          If Len(strSQL) > 0 Then
              strSQL = strSQL & " " & strDistrict & ","
          Else
              strSQL = strDistrict
          End If
      End If
      If Len(strTown) > 0 Then
          If Len(strSQL) > 0 Then
              strSQL = strSQL & " " & strTown
          Else
              strSQL = strTown
          End If
      End If
      If Len(strPostcode) > 0 Then
          If Len(strSQL) > 0 Then
              strSQL = strSQL & " " & strPostcode
          Else
              strSQL = strPostcode
          End If
      End If
      If Len(strSQL) > 0 Then
          ConcatAddress = strSQL
      Else
          ConcatAddress = ""
      End If
  ErrExit:
      Exit Function
  ErrRoutine:
      ConcatAddress = Empty
      Select Case Err
          Case 94
              'MsgBox "Postcode not found."
              Resume ErrExit
          Case Else
              MsgBox "The following error has occurred " & Err & " " & Err.Description
              Resume ErrExit
      End Select
  End Function</pre>
  <h1>'#'#'#'#'#'#'</h1>
  <p>EDIT</p>
  <p>I was asked to post the execution plan for the query, but that is not available in access.  I got a hack working that I think is close to what an execution plan is (couldn't get it to work with Access 2013 though).</p>
  <p>[For Info]
  Add a key and string to the registry - HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines<br />
  Add key 'Debug' and add string 'JETSHOWPLAN' - Set the value to ON to record
  (results should be in my documents or in location of database)</p>
  <pre>- Inputs to Query -
  ODBC table 'vuSearch'
  ODBC table 'vuSearch'
  Table 'tldDealSearch'
  ODBC table 'vuDesc'
  - End inputs to Query -
  
  01) Sort table 'vuDesc'
  02) Outer Join table 'tldDealSearch' to result of '01)'
        using temporary index
        join expression "tldDealSearch.PDID=vuDesc.PDID"
  1614631268) Remote SQL
  03) Sort result of '02)'
  04) Inner Join result of '02)' to result of '03)'
        using temporary index
        join expression "tldDealSearch.PDID=vuSearch.PDID"
        store result in temporary table </pre>
  <p>Seems to be alot of sorting going on so that must slow it down some.  Hope this is what you were looking for.</p>
  <p><strong>EDIT</strong><br />
  The Access JETSHOWPLAN does not give much detail so I transferred the tldSearchData table to the backend and ran the query there.  I removed the concatAdderss (as it uses Access function) and also the IIF formula to determine which area to use.  The resultant execution plans are below.</p>
  <p>Actual: <a href="https://drive.google.com/file/d/0B5o8fYhuyQ0ZODZCWHNIaS1KZ1k/view?usp=sharing" rel="nofollow noreferrer">https://drive.google.com/file/d/0B5o8fYhuyQ0ZODZCWHNIaS1KZ1k/view?usp=sharing</a>
  Estimated: <a href="https://drive.google.com/file/d/0B5o8fYhuyQ0ZU0lnRUhvaXVkc1k/view?usp=sharing" rel="nofollow noreferrer">https://drive.google.com/file/d/0B5o8fYhuyQ0ZU0lnRUhvaXVkc1k/view?usp=sharing</a><br />
  The query took 19 seconds to run directly from SQL (from the cloud server)</p>