Skip to main content
Post Made Community Wiki by Paul White
Source Link

Notes on the solution originally left in the question body by the author:

The fix ended up being simple: replacing the LEFT JOIN to Map with a NOT EXISTS clause. This causes only one tiny difference in the query plan, joining to the TransactionCustomers table (a remote query) after joining to the Map table instead of before. This may mean that it's requesting only needed records from the remote server, which would cut down the volume transmitted ~100-fold.

Ordinarily I'm the first to cheer for NOT EXISTS; it's often faster than a LEFT JOIN...WHERE ID IS NULL construct, and slightly more compact. In this case, it's awkward because the problem query is built on an existing view, and while the field needed for the anti-join is exposed by the base view, it's first cast from integer to text. So for decent performance, I have to drop the two-layer pattern and instead have two nearly-identical views, with the second including the NOT EXISTS clause.

Thank you all for your help in troubleshooting this problem! It may be too specific to my circumstances to be of help to anyone else, but hopefully not. If nothing else, it's an example of NOT EXISTS being more than marginally faster than LEFT JOIN...WHERE ID IS NULL. But the real lesson is probably to ensure that remote queries are being joined as efficiently as possible; the query plan claims that it represents 2% of the cost, but it does not always estimate accurately.