AnsweredAssumed Answered

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

Question asked by Mike_Mitchell Expert on Apr 26, 2016
Latest reply on Apr 28, 2016 by okramis

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

Outcomes