>>>>Or Just turn buffering on for table1 and do a tablerevert() within a begin/end transaction so that no changes get saved back to the table.
>>>
>>>I'm not sure TABLEREVERT() would be any faster than recall all.
>>
>>It should be much faster, since it simply involves throwing out the local buffers; no changes have actually been written to the table. If the deletions are really written to the table, then you have to actually rewrite the records to remove the deletion mark.
>
>Okay, here's the results of some testing in a fairly simple situation:
>
>1) The simple SELECT...NOT IN takes about 220 seconds
>
>2) The DELETE FROM... by itself takes about 210 seconds, though optimized.
>
>3) The additional SELECT to use with the DELETE FROM takes about 30 seconds.
>

And what about the RECALL ALL vs optimistic table buffering and TABLEREVERT()? That's where I expected to see a significant difference in performance.

>So, to compare, we have the fastest so far by just doing the unoptimized NOT IN method. Whatever way the SQL handles it internally, it is pretty fast for the difficult job it has to do. Maybe I won't complain so much now.
>
>The reason, I assume, that the DELETE doesn't help much is that these are unique keys, and every record must be checked and potentially marked, and even with an index to help, it's not of enough benefit. Probably there's a fair overhead on the marking of DELETEDs, also.