Optimizing Negative Comparison in SQL
It's hard to say much given your need to keep your code and data confidential, but sometimes you can get better performance just by trying equivalent rewrites. The following query should return the same results but it's very likely to have a different query plan:

SELECT 
A.REFERENCE, 
A.MEMBERID, 
B.MEMBERID AS MEMBERID_B 
FROM 
TRANS A INNER JOIN 
TRANS B ON A.REFERENCE = B.REFERENCE AND A.MEMBERID &gt; B.MEMBERID 

UNION ALL 

SELECT 
A.REFERENCE, 
A.MEMBERID, 
B.MEMBERID AS MEMBERID_B 
FROM 
TRANS A INNER JOIN 
TRANS B ON A.REFERENCE = B.REFERENCE AND A.MEMBERID &lt; B.MEMBERID


As you said in chat, this rewrite finishes in 6 seconds which is a decent improvement over the original 20 seconds you were seeing with &lt;&gt;.