1 2 3 Previous Next 34 Replies Latest reply on Apr 28, 2016 3:49 PM by okramis

    SQL JOIN with multiple predicates too slow to use. Options?

    Mike_Mitchell

      Good day, all. I have a need to perform a query that joins two tables together. Here's the basic query:

       

      Let ( [

      $field = "Contact_Local.ID" ;

      $table = "Contact_Local" ;

      sqlQuery =

      "SELECT Contact_Local.ID " &

      "FROM Contact_Local LEFT OUTER JOIN syncHistory " &

      "ON Contact_Local.ID = syncHistory.keyValue AND syncHistory.lastSyncPersistentID = ? " &

      "WHERE syncHistory.keyValue IS NULL"

      ] ;

       

      ExecuteSQL ( sqlQuery ; "|" ; "¶" ; Get ( PersistentID ))

       

      )

       

      The objective is to return all records in Contact_Local where there is no matching record in syncHistory. The join criteria are the the two fields syncHistory.keyValue and syncHistory.lastSyncPersistentID. Basically, I want to see all the records in the local file that don't have a match in a hosted file.

       

      This query works, but it's way too slow to use. So I need another way to get the same result. We are, again, hit with the "I need an AntiFilterValues function" conundrum.

       

      If I remove the second join predicate and just use:

       

      Let ( [

      $field = "Contact_Local.ID" ;

      $table = "Contact_Local" ;

      sqlQuery =

      "SELECT Contact_Local.ID " &

      "FROM Contact_Local LEFT OUTER JOIN syncHistory " &

      "ON Contact_Local.ID = syncHistory.keyValue " &

      "WHERE syncHistory.keyValue IS NULL"

      ] ;

       

      ExecuteSQL ( sqlQuery ; "|" ; "¶" )

       

      )

       

      it returns quite quickly. But, that excludes the records where there's a device ID from a different device - which isn't what I need.

       

      Any ideas on how I might restructure this so (a) it performs acceptably, and (b) returns the correct set?

       

      TIA

       

      Mike

        1 2 3 Previous Next