View Incident Report

Minor

Reported By: Andrew H. Grilk
Reported On: 6/25/2004
For: Version 4.08 Build 1

# 1759 DISTINCT 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 Workarounds

The 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.

Resolution

Fixed Problem on 7/10/2004 in version 4.08 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap