Reported By: Andrew H. Grilk Reported On: 6/25/2004 For: Version 4.08 Build 1
# 1759DISTINCT SQL NOT IN Sub-Queries Are Very Slow select statment using a not in clause with distinct VERY slow.
This took 876 seconds (about 14.5 mins) on the 2 tables cited below with record counts of
4939 in ItemDetails and
3644 in ItemHeaders.
delete from itemheaders
where reference not in(select distinct header
from itemdetails)
Workaround:
delete from itemheaders
where reference not in(select distinct header
from itemdetails order by 1)
Comments and WorkaroundsThe workaround is to add an ORDER BY on the sub-query. This also applies to IN sub-queries in addition to NOT IN sub-queries.
ResolutionFixed Problem on 7/10/2004 in version 4.08 build 1