Hi,
I had to alter the code, the union all as it was giving duplicates, I also removed the DISTINCTS as per sergey's suggestion this resulted in a significant difference.
Rushmore optimization was NONE on all
Our code 209 recs 2.12 seconds
Your code 209 recs 1.13 (after removing UNION ALL)
Your code 209 recs 0.75 (after removing the DISTINCTS) not bad improvement
The final code is as follows:-
select table1.f1 from table1 left join table3 on table1.f1 = table3.f1 ;
where ISNULL(table3.f1) ;
union ;
select table2.f1 from table2 left join table3 on table2.f1 = table3.f1 ;
where ISNULL(table3.f1) ;
order by 1 into cursor curOrphans nofilter
>>Thanks, it was the cursor name of "a", when I changed it to nomaster the problem stopped.
>>I will try the other things later.
>>
>>Have a good day.
>>
>>>1. There's no need to include DISTINCT because UNION does implicit DISTINCT on the result set
>>>2. For EXISTS type subqueries there's no reason to use anything but '*' in the field list because they don't return any record set
>>>3. The 'a' is not a good name for a cursor because it conflicts with the default name for the first workarea.
>>>
>>>SELECT table1.f1 FROM table1 ;
>>> WHERE NOT exist ;
>>> (SELECT * FROM table3 WHERE table3.f1 = table1.f1) ;
>>>UNION ;
>>>SELECT table2.f1 FROM table2 ;
>>> WHERE NOT exist ;
>>> (SELECT * FROM table3 WHERE table3.f1 = table2.f1) ;
>>>INTO CURSOR crsResult NOFILTER
>>>
>
>Compare speed difference for two select variations and let us know your results.
Regards N Mc Donald